topic Re: How to make a loop over a column of a SAS table in New SAS User

How to make a loop over a column of a SAS table

Al_senior — Wed, 30 Jun 2021 17:20:28 GMT

I know this is a basic question, but the answer to the question can help me understand how loops work in SAS.

I have 5 different datasets named "dt1", "dt2",..., "dt5"

data products;
input product_name $ ;
datalines;
dt1
dt2
dt3
dt4
dt5
;
proc print data= products;
run;

I have faced many occasions that I needed to go through rows of a table and apply a function on them. Let's say I want to do a very simple step with these datasets for instance I want do proc contents.

The code I wrote is as following:

%macro first_loop;
 %do iterate = 1 %to 5;
   data _null_;
   set products ;
   call symput ("prd", trim(left(product_name);
 %put &prd;  
   proc contents data = &prd ;
   run;

%end; 
%mend;

%first_loop

Obviously this code doesn't provide the result and log shows that &prd is not well defined, but I don't know how to pass the column name. Any help would be appreciated.

Re: How to make a loop over a column of a SAS table

Reeza — Wed, 30 Jun 2021 17:22:22 GMT

CALL EXECUTE()/DOSUBL() or Macros

data products;
input product_name $ ;
str = catt('proc contents data=', product_name, '; run;');
call execute(str);
datalines;
dt1
dt2
dt3
dt4
dt5
;

UCLA introductory tutorial on macro variables and macros
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

@Al_senior wrote:

I know this is a basic question, but the answer to the question can help me understand how loops work in SAS.

I have 5 different datasets named "dt1", "dt2",..., "dt5"
data products;
input product_name $ ;
datalines;
dt1
dt2
dt3
dt4
dt5
;
proc print data= products;
run;
I have faced many occasions that I needed to go through rows of a table and apply a function on them. Let's say I want to do a very simple step with these datasets for instance I want do proc contents.

The code I wrote is as following:
%macro first_loop;
 %do iterate = 1 %to 5;
   data _null_;
   set products ;
   call symput ("prd", trim(left(product_name);
 %put &prd;  
   proc contents data = &prd ;
   run;

%end; 
%mend;
Obviously this code doesn't provide the result and log shows that &prd is not well defined, but I don't know how to pass the column name. Any help would be appreciated.

Re: How to make a loop over a column of a SAS table

Tom — Wed, 30 Jun 2021 18:37:27 GMT

PROC CONTENTS in not a "function". It is a procedure.

If you want to use data to generate code there are many ways.

You can use CALL EXECUTE(). Basically generate a string (or series of strings) and use CALL EXECUTE() to have SAS stack them up to run when your current data step ends.

data _null_;
  set products;
  call execute(cats('proc contents data=',product_name,';run;'));
run;

You can use use normal SAS data statements to write a file that has the code you want to run and then use %INCLUDE statement to include that new file as source code. This is much easier to debug as you can review the file and make sure the code is being generate with the right syntax before running the %include. Plus you have access to all of the power of the PUT statement to generate the code.

filename code temp;
data _null_;
  set products;
  file code;
  put 'proc contents data=' product_name ';run;';
run;
%include code / source2;

There is a newer (and in may ways still experimental) DOSUBL() function that works a lot like CALL EXECUTE() but it actually allows the generated code to run immediately in a sub-process. But usually this is not worth the effort.

If you have a really complicated series of steps you want to execute for every observation in your data then encapsulate them into a macro and in your code generation step simply generate a call to the macro as the code you are generating.

%macro mymacro(product_name);
proc contents data=&product_name;
run;
%mend mymacro;

filename code temp;
data _null_;
  set products;
  file code;
  put '%mymacro(' product_name= ')';
run;
%include code / source2;

Resulting generated code:

%mymacro(product_name=dt1 )
%mymacro(product_name=dt2 )
%mymacro(product_name=dt3 )
%mymacro(product_name=dt4 )
%mymacro(product_name=dt5 )