Is it possible to loop over SAS datasets?

Question

I have 60 sas datasets that contain data on consumers individual characteristics such as id, gender, age, amountSpent, .... Each dataset shows data only for one time period (data1 is Jan, data2 is Feb...). I cannot merge them because of the size and some other issues.

How can I write a multiple loop to go through each of the datasets, do some manipulations and save the estimated values to a temporary file.

SAS does not have a for loop. How can I use do?

Answer 1

But sas does have a do while macro loop. So basically you need 3 things: 1. In some way, a listing of your datasets. 2. A macro that loops over this listing. 3. The stuff you want to do.

E.g., let us presume you have a dataset WORK.DATASET_LIST that contains a variable library (libname) and a variable member (dataset name) for every dataset you want to loop across.

Then you could do:

%macro loopOverDatasets();
    /*imho good practice to declare macro variables of a macro locally*/
    %local datasetCount iter inLibref inMember;

    /*get number of datasets*/
    proc sql noprint;
        select count(*)
         into :datasetCount
        from WORK.DATASET_LIST;
    quit;

    /*initiate loop*/
    %let iter=1;
    %do %while (&iter.<= &datasetCount.);
        /*get libref and dataset name for dataset you will work on during this iteration*/
        data _NULL_;
            set WORK.DATASET_LIST (firstobs=&iter. obs=&iter.); *only read 1 record;
            *write the libname and dataset name to the macro variables;
            call symput("inLibref",strip(libname));
            call symput("inMember",strip(member));
            *NOTE: i am always mortified by the chance of trailing blanks torpedoing my code, hence the strip function;
        run;

        /*now you can apply your logic to the dataset*/
        data &inLibref..&inMember.; *assuming you want to apply the changes to the dataset itself;
            set &inLibref..&inMember.;
            /*** INSERT YOUR LOGIC HERE ***/
        run;

        /*** ANY OTHER PROCS/DATA STEPS ***/
        /*just remember to use &inLibref..&inMember. to refer to the current dataset*/

        /*increment the iterator of the loop*/
        %let iter=%eval(&iter.+1);
    %end;
%mend;

/*call the macro*/
%loopOverDatasets()

That is the idea. Maybe you want to gather the list of your datasets in a different way. e.g., a macro variable containing them all. In that case you'll have to use the %scan function in the loop to pick a dataset. Or maybe there is logic in the naming, e.g., dataset1, dataset2, dataset3..., in which case you could simply make use of the &iter. macro variable.