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,
SS
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,
SS
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)
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,
SS
Thanks Miller,
I have different approach , please can you review .
Why don't you execute the code and see if it works?
%macro testall(dsn);
%put Table= &dsn ;
%mend;
data _null_;
set sashelp.vmember(where=(memtype='DATA'));
call execute(cats('%testall(',libname,'.',memname,')'));
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.