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;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.