Accessing big data using SAS PC

[dr2014]

@Tom I decided to use a macro to see how it works. Referring to the different librefs below is my code.I am not sure if it fits in memory, but it would be worth trying.

%macro allfiles(varlist=);

 libname2.orig1&varlist

 libname2.orig2&varlist

 libname3.orig3&varlist

 libname4.orig4&varlist

 libname5.orig5&varlist

 libname5.orig6&varlist

%mend;

　

data out_all;

set %allfiles(varlist=%str((keep=id var1 var2 type); 

run;

[Tom]

Macro code is not going to change the SAS code that is run or how SAS runs it. It just makes it easier for you type it.  For just 5 or 6 datasets if is just is probably easier to just use copy/paste to replicate the code rather than complicate your program with macro code.

Most SAS jobs are I/O bound and it sounds like that is your problem. Run PROC CONTENTS on your input datasets and check some things. Make sure they were made with the same operating system and version of SAS.  If SAS has to use CEDA to read the files it will slow it down a lot.  Check if they are already sorted by the ID variable(or at least if SAS thinks so).  Eliminating the need to sort is a hugh deal.  Check if they are using SAS compression.  Many SAS datasets can get 60-80 reduction in disk size just by using the COMPRESS=BINARY option when creating them.

Try using the SPDE engine to store your new subsets.  That can really reduce the disk space needed to store the data.

The main thing is subset as early as possible.   

If your list of ID values is small then store them in a macro variable.

proc sql noprint;
 select distinct id into :idlist separated by ' '
 from relation
 ;
quit;

%let varlist=id var1 vara varb varc ;
proc sort data=libname1.orig1(keep=&varlist) out=subset1 (compress=binary);
  by id;
  where id in (&idlist);
run;
...
data combine/view=combine;
  set subset1 subset2 subset3 .... ;
  by id;
run;

data final (compress=binary);
  merge relation combine ;
  by id;
run;    

[dr2014 · Accepted solution]

@Tom Thanks for all your suggestions. I will definitely be implementing them. I have about 20000 ids so it will be hard to put them in a macro. It would have certainly helped to subset to those ids in the first step itself.