Order of precedence

The below merge is more like a set statement. But here there is a possibility of the same store occurring in both new_stores and mature_stores table. Hence flagging with ‘if a’ and ‘if b’ could be problem, as one store could fall in both the groups. Care should be taken while making use of this logic. In the below example if a store is found in mature_stores table then it no more belongs to new store category, so the order of precedence is given to mature stores using ‘if a’.


/* Dataset for MATURE & NEW STORE*/
data all_stores;
merge mature_stores(in=a) new_stores(in=b);
by str_i;
if a then FLAG="Mature";
if b then FLAG="New";
run;

Datalines within a macro will throw error

*Datalines outside a macro;
data new1;
input x 8.;
datalines;
1
2
3
;
run;

%macro test2;
*Datalines within a macro will throw error;
data new2;
input x 8.;
datalines;
1
2
3
;
run;
%mend test2;
%test2;

*ERROR: The macro TEST2 generated CARDS (data lines) for the DATA step, which could cause incorrect results. The DATA step and the macro will stop executing.;

%macro test3;
*Make use of the below method if you want to create records;
data new3;
length x 8.;
x=1; output;
x=2; output;
x=3; output;
run;
%mend test3;
%test3;

Else one can make use of proc sql insert into

Execute macro with data _null_

options mprint mlogic symbolgen;


*Create a dataset with no records;
data test_store;
attrib store length=8 ;
attrib go_d length=8 format=date9.;
stop;
run;


*This macro gets executed by call execute to populate test_store - with no observation;
%macro store(str_i);
proc sql noprint;
insert into test_store
select &str_i, go_d from def.str
where str_i = &str_i;
quit;
%mend store;


*Pick random sample of stores for which the test_store dataset should get populated;
data random;
set abc.score_11;
x=ranuni(1234);
run;


*Randomly sort the data set;
proc sort data=random;
by x;
run;


*Keep the first n observations. Since the data points are randomly sorted, these observations constitute a simple random sample;
data sample(drop=x);
set random (obs=10);
run;


*This creates a dataset named test_store WITH ONLY THOSE STORES THAT ARE PRESENT IN SAMPLE;
data _null_;
set sample;
call execute('%store('str_i')');
run;


More: http://www2.sas.com/proceedings/sugi22/CODERS/PAPER86.PDF

Random Sample Selection

PROC SURVEYSELECT or RANUNI function can be used for random sample selection.

More: http://cba.ua.edu/~mhardin/SAMPLE%20Simple%20Random%20Sample%20Without%20Replacement.htm

Extracting data from relational databases

/*example 1: SAS ACCESS/LIBNAME statement*/

libname udblib db2 uid=testuser pass=testpass dsn=strtd101 schema=STR;
libname oralib oracle user=testuser password=testpass path=crotd101 schema=CRO;


/*example 2: SAS ACESS/SQL pass through facility*/


PROC SQL;
connect to db2 as db2tbl (user=testuser password=testpass datasrc=sample in=userspace1);
create table SASLOCAL as
select * from connection to db2tbl
(select * from RUN_PARM);
disconnect from db2tbl;
quit;

proc sql;
connect to oracle as oratbl (user=testuser password=testpass path=crotd101);
create table AE as
select * from connection to oratbl
(select * from AE_ORACLE_TABLE);
disconnect from oratbl;
quit;
 
More: http://support.sas.com/documentation/cdl/en/connref/61908/HTML/default/viewer.htm#a000269041.htm