DATA Step, Macro, Functions and more

macro looping on diffrent tables in diffrent libs

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

macro looping on diffrent tables in diffrent libs

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


Accepted Solutions
Solution
‎03-26-2018 08:41 AM
Frequent Contributor
Posts: 76

Re: macro looping on diffrent tables in diffrent libs

Posted in reply to PaigeMiller

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


All Replies
Respected Advisor
Posts: 2,836

Re: macro looping on diffrent tables in diffrent libs

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
Solution
‎03-26-2018 08:41 AM
Frequent Contributor
Posts: 76

Re: macro looping on diffrent tables in diffrent libs

Posted in reply to PaigeMiller

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

Respected Advisor
Posts: 2,836

Re: macro looping on diffrent tables in diffrent libs

Thanks  Miller,

I have different approach , please can you review .

 

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

--
Paige Miller
Frequent Contributor
Posts: 76

Re: macro looping on diffrent tables in diffrent libs

Posted in reply to PaigeMiller
yes,It is working and I accepted it.
Thanks,
SS
Super User
Posts: 10,698

Re: macro looping on diffrent tables in diffrent libs

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

Need further help from the community? Please ask a new question.

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