Generate SAS Code Iteratively with Simple Loops and SYMPUTX Routine

Iterative Processing 1

Execute macro language statements iteratively. Generate SAS code iteratively. 2

Simple Loops Macro applications might require iterativeprocessing. The iterative %DO statement can execute macro language statements and generate SAS code. %DOindex-variable=start %TO stop <%BY increment>; text %END; 3

Simple Loops %DO and %END statements are valid only inside a macro definition. index-variable is a macro variable. index-variable is created in the local symbol table if it does not already exist in another symbol table. start, stop, and increment values can be any valid macro expressions that resolve to integers. The %BY clause is optional. (The default increment is 1.) 4

Simple Loops text can be any of: constant text macro variables or expressions macro statements macro calls 5

The SYMPUTX Routine (Review) Example: Create a numbered series of macro variables. data data _null_; set orion.country end=no_more; call symputx('Country'||left(_n_),country_name); if no_more then call symputx('numrows',_n_); run run; proc proc sql sql; select name from dictionary.macros where scope="GLOBAL"; quit quit; 6

data data _null_; set orion.country end=no_more; call symputx('Country'||left(_n_),country_name); if no_more then call symputx('numrows',_n_); run run; proc proc sql sql; select name, value from dictionary.macros where scope="GLOBAL" and upcase(name) contains "COUNTRY"; quit quit; proc proc sql describe table dictionary.macros; quit quit; sql;

Simple Loops %macro %macro putloop putloop; %do i=1 1 %to &numrows; %put Country&i is &&country&i; %end; %mend %mend putloop; %putloop putloop 8

Example: Iteratively generate complete SAS steps.

%let path=c:\users\dlm1\dropbox\sas\sasdata; %macro %macro readraw(first=2003 2003,last=2007 %do year=&first %to &last; data year&year; infile "&path\mac1\orders&year..dat"; input order_ID order_type order_date : date9.; run; %end; %mend %mend readraw; 2007); options mlogic mprint; %readraw readraw(first=2004 options nomlogic nomprint; 2004,last=2006 2006) 10

Generating Data-Dependent Code Example: Create a separate data set for each value of a selected variable in a selected data set. %split(data=orion.customer, var=country) 11

Step 1: Store unique data values into macro variables. %macro %macro split (data=, var=); proc sort data=&data(keep=&var) out=values nodupkey; by &var; run; data _null_; set values end=last; call symputx('site'||left(_n_),&var); if last then call symputx('count',_n_); run; %put _local_; %mend %mend split; %split split(data=orion.customer, var=country) 12

Generating Data-Dependent Code Step 2: Use loops to generate the DATA step. %macro %macro split (data=, var=); proc sort data=&data(keep=&var) out=values nodupkey; by &var; run; data _null_; set values end=last; call symputx('site'||left(_n_),&var); if last then call symputx('count',_n_); run; data %do i=1 1 %to &count; &&site&i %end; ; set &data; select(&var); %do i=1 1 %to &count; when("&&site&i") output &&site&i; %end; otherwise; end; run; %mend %mend split; %split split(data=orion.customer, var=country) 13

Example: Print all data sets in a SAS data library. Step 1: Store data set names into macro variables. %macro %macro printlib(lib=WORK); %let lib=%upcase(&lib); data _null_; set sashelp.vstabvw end=final; where libname="&lib"; call symputx('dsn'||left(_n_),memname); if final then call symputx('totaldsn',_n_); run; %put _local_; %mend %mend printlib; %printlib printlib(lib=orion) 14

Generating Data-Dependent Code Step 2: Use a macro loop to print every data set in the library. %macro %macro printlib(lib=WORK,obs=5 5); %let lib=%upcase(&lib); data _null_; set sashelp.vstabvw end=final; where libname="&lib"; call symputx('dsn'||left(_n_),memname); if final then call symputx('totaldsn',_n_); run; %do i=1 1 %to &totaldsn; proc print data=&lib.. .&&dsn&i(obs=&obs); title "&lib..&&dsn&i Data Set"; run; %end; %mend %mend printlib; %printlib printlib(lib=orion) 15

Conditional Iteration You can perform conditional iteration in macros with %DO %WHILE and %DO %UNTIL statements. General form of the %DO %WHILE statement: %DO %WHILE(expression); text %END; A %DO %WHILE loop: evaluates expression at the top of the loop before the loop executes executes repetitively while expression is true 16

Conditional Iteration General form of the %DO %UNTIL statement: %DO %UNTIL(expression); text %END; expression can be any valid macro expression. A %DO %UNTIL loop does the following: evaluates expression at the bottom of the loop after the loop executes executes repetitively until expression is true executes at least once 17

Conditional Iteration Example: Generate a conditional number of program steps, based on a parameter value. %macro %macro stats(datasets); %let i=1; %let dsn=%scan(&datasets,1); %do %while(&dsn ne ); title "ORION.%upcase(&dsn)"; proc means data=orion.&dsn n min mean max; run; %let i=%eval(&i+1); %let dsn=%scan(&datasets,&i); %end; title; %mend %mend stats; %stats stats(staff specialsals country) 18

Example: Modify the previous example to validate data set names. %macro %macro stats(datasets); %let i=1; %do %until(&dsn= ); %let dsn=%scan(&datasets,&i); %if &dsn= %then %put NOTE: Processing completed.; %else %if %sysfunc(exist(orion.&dsn)) %then %do; title "ORION.%upcase(&dsn)"; proc means data=orion.&dsn n min mean max; run; %end; %else %put ERROR: No &dsn dataset in ORION library.; %let i=%eval(&i+1); %end; %mend %mend stats; %stats stats(discount music orders) 19

Make multiple copies of a dataset data data tmp; do i=1 1 to 5 5; output; end; run run; data data tmp1; set tmp tmp; proc proc print print data=tmp1 noobs; run run;

%macro %macro mkcopies(reps,newdata,copydata); data &newdata; set %do i=1 1 %to &reps; &copydata %end; ; run; %mend %mend mkcopies; options mprint; %mkcopies mkcopies(2 2,tmp2,tmp); proc proc print print data=tmp2; run run; options nomprint;