topic macro looping on diffrent tables in diffrent libs in SAS Programming

macro looping on diffrent tables in diffrent libs

sathya66 — Mon, 26 Mar 2018 11:50:51 GMT

Hi All,

I have 100+ sas datasets in each library that contain data on consumers individual characteristics such as id, gender, age,etc.

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

　my code;

data test.table;
	set test.table
	%testall(test.table);

run;

　I want to apply %testall macro for all the tables in all libraries.

Thanks,

Re: macro looping on diffrent tables in diffrent libs

PaigeMiller — Mon, 26 Mar 2018 12:21:33 GMT

You need to create a macro variable that has the name of the 100 tables (probably using PROC SQL), let's say it is &list_of_table_names

Then

%macro do_all(table_names=);
%do i=1 %to %sysfunc(countw(&table_names));
    %let this_table_name=%scan(&table_names,&i,%str( ));
    data a;
         set &this_table_name;
         ... your calculations go here ...
     run;
     proc append base=all new=a;
     run;
%end;
%mend;
%do_all(table_names=&list_of_table_names)

Re: macro looping on diffrent tables in diffrent libs

Ksharp — Mon, 26 Mar 2018 12:26:34 GMT

%macro testall(dsn);
 %put Table= &dsn ;
%mend;
data _null_;
 set sashelp.vmember(where=(memtype='DATA'));
 call execute(cats('%testall(',libname,'.',memname,')'));
run;

Re: macro looping on diffrent tables in diffrent libs

sathya66 — Mon, 26 Mar 2018 12:27:26 GMT

Thanks Miller,

I have different approach , please can you review .

%macro loopOverDatasets(inLibref);  
    ods output Members=Members;
 proc datasets library=&inLibref memtype=data;
 run;
 quit;
    %local datasetCount iter inLibref inMember;
    /*get number of datasets*/
    proc sql noprint;
        select count(*)
         into :datasetCount
        from WORK.Members;
    quit;

    /*initiate loop*/
    %let iter=1;
    %do %while (&iter.<= &datasetCount.);
        
        data _NULL_;
            set WORK.Members (firstobs=&iter. obs=&iter.); *only read 1 record;
         
            call symput("inMember",strip(Name));
           
        run;

        /* applying my logic to the dataset*/
        data &inLibref..&inMember.; 
            set &inLibref..&inMember.;
          %testall(&inLibref..&inMember.);
        run;



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

/*call the macro*/
%loopOverDatasets(work);

Thanks,

Re: macro looping on diffrent tables in diffrent libs

PaigeMiller — Mon, 26 Mar 2018 12:44:33 GMT

Thanks Miller,

I have different approach , please can you review .

Why don't you execute the code and see if it works?

Re: macro looping on diffrent tables in diffrent libs

sathya66 — Mon, 26 Mar 2018 12:48:45 GMT

yes,It is working and I accepted it.
Thanks,
SS