Combine multiple sas datasets efficiently

Question

I have more than 200 thousand small data sets with the same variables (n <1000 and usually n <100) that I want to combine into a main data set. I tried using a macro that uses a data step to simply iterate over all new data sets and combine with the wizard using "set master new:", but it takes a lot of time. In addition, if I try to start at the same time, then at the stage of the call, it will be indicated that I lost memory on a huge server. For reference, all small datasets together make up just over 5 gigs. Any suggestions would be appreciated. Here is what I still have:

%macro catDat(name, nbr) ; /*call in new dataset */ data new ; set libin.&name ; run ; /* reorder names */ proc sql noprint; create table new as select var1, var2, var3 from new; quit; %if &nbr = 1 %then %do ; data master; set new; run; %end; %if &nbr > 1 %then %do ; data master; set master new ; run; %end ; %mend; /* concatenate datasets */ data runthis ; set datasetNames ; call execute('%catdat('||datasetname||','||_n_||')'); run; 

Allowed: see Bob's comments below.

Answer 1

Try using PROC APPEND instead of your “new” dataset; it will be much, much faster:

 %macro DOIT; proc sql noprint; select count(*) into : num_recs from datasetNames; quit; %do i=1 %to &num_recs; data _null_; i = &i; set datasetNames point=i; call symput('ds_name',datasetname); stop; run; /* UPDATE: added this line */ %if &i = 1 %then %do; /* Initialize MASTER with variables in the order you wish */ data master(keep=var1 var2 var3); retain var1 var2 var3; set libin.&ds_name; stop; run; %end; proc append base=master data=libin.&ds_name(keep=var1 var2 var3); run; %end; %mend DOIT; 

PROC APPEND will add each dataset to your new “master” without having to rebuild it every time you do it now. It also avoids the use of CALL EXECUTE by removing this memory problem that you encountered (caused by the creation of a lot of code on the execution stack).