BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sathya66
Barite | Level 11

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

1 ACCEPTED SOLUTION

Accepted Solutions
sathya66
Barite | Level 11

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

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26

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)
--
Paige Miller
sathya66
Barite | Level 11

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

PaigeMiller
Diamond | Level 26

Thanks  Miller,

I have different approach , please can you review .

 

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

--
Paige Miller
sathya66
Barite | Level 11
yes,It is working and I accepted it.
Thanks,
SS
Ksharp
Super User
%macro testall(dsn);
 %put Table= &dsn ;
%mend;
data _null_;
 set sashelp.vmember(where=(memtype='DATA'));
 call execute(cats('%testall(',libname,'.',memname,')'));
run;

SAS Innovate 2025: Register Now

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!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1143 views
  • 1 like
  • 3 in conversation