Range of values : IN operator

Beginning in SAS 9, a range can be used with the IN operator. For
example:

if x in(1:3,5) then y=2;

is equivalent to:

if x in(1,2,3,5) then y=2;

Usage of Macro variable SQLOBS

Macro Variable:
sqlobs

Short Description:
Automatic macro variable. Returns the number of rows processed by an SQL statement.

Scenario Description:
Many a times after creation of a table, we need to take its count and store it in a macro variable.

 One approach is to take “select count * from table”.

But this is approach is time consuming.
Alternatively, we can use the automatic macro variable “sqlobs”.

 The automatic macro variable SQLOBS is assigned a value after the SQL SELECT statement executes.

We can directly refer the value of this macro variable to store the count.

Example:

data Marks_sub;
input Id Marks;
datalines;
100 50
101 70
102 30
106 40
;
proc sql;
create table Pass as
select
*
From Marks_sub
where Marks > 40;
quit;

%let count=&sqlobs;
%put Number of rows = &count;

Here two rows qualify the criteria and thus are processed by the sql statement.
In log the message comes as mentioned below:

%put Number of rows = &count;
Number of rows = 2

2 logics (Where also and Subsetting IF)

1. if City =: 'B';

It selects the observation if the data values of CITY variable starts with 'B' character (i.e,'B' at the first position in the City variable value).
Egg: City= "Bangalore", "Bahrain" etc., but not "bombay"

2. Where also Salary > 10000;

In general we cant use 2 where statements in a single data step. Means, if you executed a datastep which contains 2 where statements, then only the 2nd where statement will work. In this case you need to use where also statement instead of 2nd where statement.

Before:
data b;
set a;
where gender='M';
where salary > 10000;
run;

After:
data b;
set a;
where gender='M';
where also salary > 10000;
run;

SORT Var Names in a Dataset

This code is used to sort variable names in a dataset. Means placing variables in ascending order in a dataset according to their names.

For more information, see the comments in code.

Here just you need to provide the library name and dataset name in which you want to see the variable names in their ascending order.

SAS Code:

/* options to print resolved macro code in log file */
options mlogic mprint symbolgen;

/* Macro code to sort variable names in a dataset */
%macro var_sort(library=,dataset=);

proc sql noprint;
select strip(name) into :vars separated by ' '
from dictionary.columns
where libname=upcase("&library") and memname=upcase("&dataset")
order by 1;
quit;

data &library..&dataset;
retain &vars;
set &library..&dataset;
run;

%mend var_sort;
/* end of the macro code */

/* Here just you need to provide the library name and dataset name in which you want to see the variable
names in their ascending order */
%var_sort(library=work,dataset=sample);


/*###########################################################################*/

/* Sample data to test this macro code */

data work.sample;
xy=1;
zx=2;
dc=3;
ab=4;
aa=1;
a=2;
cd='A';
run;

/* end of the sample data */

DateTime_Manip : a small SAS utility Tool.

This code is used to generate date and datetime values between a given range with a constant gap.

Here the constant gap may be day,week,month,year etc.,
For more details, see the comments in code.

Here you need to provide DATASET name in which you want to store created dates.
DT_SLOT: type of interval ex: DAY,WEEK,TENDAY,SEMIMONTH etc.,
START_VALUE: sas date, time, or datetime value that identifies a starting point.
END_VALUE: sas date, time, or datetime value that identifies a end point.
GAP: This is the number of intervals to shift the value of START_VALUE.
FORMA: Format which is used to display the value, easy to read.

SAS Code:


/* options to print resolved macro code in log file */
options mlogic mprint symbolgen;

/* use TIME8. format for time, DATE9. format for date and NLDATM21. format for datetime values */
/* use "01:00:00"t like values for Time, "1jan2001"d like values for date and
"1jan2001:23:59:59"dt like values for Datetime */
/* You can increment SECOND,MINUTE,HOUR(as 1st argument) for both Time and Datetime values,
DAY,WEEK,TENDAY,SEMIMONTH,MONTH,QTR,SEMIYEAR,YEAR etc., for Date values.
Just add DT for first argument, if you want to operate Date related things on Datetime values */

/* Macro code to generate date and datetime values with in the specified range, with specific gap. */
%macro dt_generate(dataset=,dt_slot=,start_value=,end_value=,gap=,forma=);

data &dataset;
dt_var=&start_value;
do while (dt_var<&end_value);
dt_var=intnx("&dt_slot",dt_var,&gap);
format dt_var &forma.;
output;
end;
run;

%mend dt_generate;
/* end of the macro code */

/* Here you need to provide DATASET name in which you want to store created dates/times.
DT_SLOT: type of interval ex: DAY,WEEK,TENDAY,SEMIMONTH etc.,
START_VALUE: sas date, time, or datetime value that identifies a starting point.
END_VALUE: sas date, time, or datetime value that identifies a end point.
GAP: This is the number of intervals to shift the value of START_VALUE.
FORMA: Format which is used to display the value, easy to read.
*/

%dt_generate(dataset=xxx,dt_slot=dtmonth,start_value="1jan2001:00:00:00"dt,end_value="1jan2002:00:00:00"dt,gap=1,forma=NLDATM21.);

--------------- END -----------------------------------------------------------