DATA Step, Macro, Functions and more

Loop through (with a macro) over multiple user defined %LET=

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

Loop through (with a macro) over multiple user defined %LET=

Dear all,

 

My question might be extremely trivial.

 

I have to create a series of macro lists of variable names from a dataset i.e.:

 

%LET varlst1 = a b;

%LET varlst2 = a b c;

%LET varlst2 = a b c d;

%LET varlst2 = a b c d e;

%LET varlst2 = a b c d e f;

etc.

 

and then use a macro to apply the above %lLET=  lists to a series of data steps i.e.

 

%macro repeat (var_list);

 

proc sort data=dsn_test;

by &var_list.;

run;

 

...more code...

 

%mend;

%var_list(&varlst1 );

%var_list(&varlst2 );

%var_list(&varlst3 );

%var_list(&varlst4 );

etc.

 

I would greatly appreciate hints/tips how to automate this process.

 

Thanking you in advance.

 

Best regards


Accepted Solutions
Solution
‎07-09-2017 03:00 PM
Super User
Super User
Posts: 6,502

Re: Loop through (with a macro) over multiple user defined %LET=

[ Edited ]

SAS will assign the type and length for a variable based on how it is first used. In this case it will be set to $3 to match 'sex'.

 It just looked like it was using SEX on the second iteration because there was no room to store the rest of the value.

Just define a length for the variable.

data _null_;
  length i 8 vars $200;
  do vars="sex","sex age";
    i+1;
...

 

View solution in original post


All Replies
Super User
Posts: 5,093

Re: Loop through (with a macro) over multiple user defined %LET=

[ Edited ]

Assuming that %REPEAT works properly, don't change it.  Instead, write a second macro to loop through the existing sets of variable lists:

 

%macro loop (n_loops=);

   %local i;

   %do i=1 %to &n_loops;

      %repeat (&&varlist&i)

   %end;

%mend loop;

 

%loop (n_loops=4)

Super User
Super User
Posts: 7,413

Re: Loop through (with a macro) over multiple user defined %LET=

Could I ask to what end.  I have found it is almost never the simplest solution to start creating macros lists and macro loops, and in all those cases a simple change to the data or method of processing can vastly simplfy the code.  If you can show what your doing further on in the process and what you want out further code can be provided.

Contributor
Posts: 40

Re: Loop through (with a macro) over multiple user defined %LET=

Hi RW9,

 

Actually I want to run PROC TABULATE based on each and every %LET combination and my goal is to combine all output datasets from PROC TABULATE, run within the macro, in one single dataset so I will be able to compare the various results.

 

Thanks

Super User
Super User
Posts: 7,413

Re: Loop through (with a macro) over multiple user defined %LET=

Well, its hard to say without seeing data/code, but you should be able to do all that in one step:

data have;
  a=1; b=2; c=3; d=34;
run;

proc tabulate data=have out=want;
  class a b c d;
  table a * b * c * d;
run;

Alternatively, you can get exactly the same results doing a datastep, i.e. 

data want;
  set have;
  array vars{4} a b c d;
  array results{4} 8.;
  do i=...;
...
run;

Or a few other procedures.  You could also normalise your data from:

a b c d

1 2 3 4

 

to

var  res

a     1

b     2

c     3

...

then its simply a matter of where clausing.

However if you insist on code generation:

data _null_;
  do i="a b","a b c","a b c d";
    call execute('proc tabulate date=have;  class'||strip(i)||'; run;');
  end;
run;
Contributor
Posts: 40

Re: Loop through (with a macro) over multiple user defined %LET=

RW9 thank you but the last call execute is not working because the "TABLE" statement is missing.

 

Thanks again.

 

Super User
Super User
Posts: 7,413

Re: Loop through (with a macro) over multiple user defined %LET=

Yep, you would need to update it with your code, which I cannot see.

Contributor
Posts: 40

Re: Loop through (with a macro) over multiple user defined %LET=

Dear RW9 ,

 

Thank you for your help..I followed your instructions but I fail to produce for each call execute run different names in the output sets of PROC tabulate

 

for example when i="sex" then

I need out=pret1

when i="sex age" 

then

I need out=pret2

etc. 

 

I have tried to utilize %sysfunc(countw(&i%str( ))) but I cannoot make it work.

 

Any hint would be more than welcome.

 

Thank you in advance.

 

please see sample code and code

 

data test;
input ID $ 1-4 Sex $ 6 Age 8-9 Height 11-12 Weight 14-16
Pulse 18-20 FastGluc 22-24 PostGluc 26-28;
datalines;
2304 F 16 61 102 100 568 625
1128 M 43 71 218 76 156 208
4425 F 48 66 162 80 244 322
1387 F 57 64 142 70 177 206
9012 F 39 63 157 68 257 318
6312 M 52 72 240 77 362 413
5438 F 42 62 168 83 247 304
3788 M 38 73 234 71 486 544
9125 F 56 64 159 70 166 215
3438 M 15 66 140 67 492 547
1274 F 50 65 153 70 193 271
3347 M 53 70 193 78 271 313
2486 F 63 65 157 70 152 224
1129 F 48 61 137 69 267 319
9723 M 52 75 219 65 348 403
8653 M 49 68 185 79 259 311
4451 M 54 71 196 81 373 431
3279 M 40 70 213 82 447 504
4759 F 60 68 164 71 155 215
6488 F 59 64 154 75 362 409
;
run;

data _null_;
do i="sex","sex age";
call execute('proc sort data=test; by '||strip(i)||'; run;');
call execute('  proc tabulate data=test out=pret&c.; class '||strip(i)||'; var pulse; table '||strip(i)||' ALL = "Grand Total" , pulse = "Group I" * (N="Count" COLPCTN="%");
Run; run;');
end;
run;

Super User
Super User
Posts: 6,502

Re: Loop through (with a macro) over multiple user defined %LET=

[ Edited ]

If you generating the sequence using a data step then the counter must be in the data step also.  Change the name of the variable with that has the list of variable names and add another variable that is the counter.  Then you can generate the numbered dataset name with code like: cats('pret',i).

 

So you overall data step might look like:

 

data _null_;
  do vars="sex","sex age";
    i+1;
    call execute(catx(' '
      ,'proc sort data=test; by',vars,'; run;'
    ));
    call execute(catx(' '
      ,'proc tabulate data=test out=',cats('pret',i),';'
      ,'class',vars,';'
      ,'var pulse;'
      ,'table',vars,'ALL="Grand Total"'
      ,', pulse="Group',i,'" * (N="Count" COLPCTN="%");'
      ,'run;'
    ));
  end;
run;

 

Contributor
Posts: 40

Re: Loop through (with a macro) over multiple user defined %LET=

Dear Tom,

 

Thank you very much. Your code does create different output sets on each DO LOOP iteration but the problem is that does not update the list of variables from iteration to iteration.

It should be for i=1  

table sex

for 

i=2 

table sex age

etc.

 

but it iterates only for the first value of i="sex"

 

Any help?

 

Thanking you in advance.

 

please see attached the log

 

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
61
62 data test;
63 input ID $ 1-4 Sex $ 6 Age 8-9 Height 11-12 Weight 14-16
64 Pulse 18-20 FastGluc 22-24 PostGluc 26-28;
65 datalines;
 
NOTE: The data set WORK.TEST has 20 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
 
86 ;
 
87 run;
88
89 data _null_;
90 do vars="sex","sex age";
91 i+1;
92 call execute(catx(' '
93 ,'proc sort data=test; by',vars,'; run;'
94 ));
95 call execute(catx(' '
96 ,'proc tabulate data=test out=',cats('pret',i),';'
97 ,'class',vars,';'
98 ,'var pulse;'
99 ,'table',vars,'ALL="Grand Total"'
100 ,', pulse="Group',i,'" * (N="Count" COLPCTN="%");'
101 ,'run;'
102 ));
103 end;
104 run;
 
NOTE: DATA statement used (Total process time):
real time 0.06 seconds
cpu time 0.00 seconds
 
 
NOTE: CALL EXECUTE generated line.
1 + proc sort data=test; by sex ; run;
 
NOTE: There were 20 observations read from the data set WORK.TEST.
NOTE: The data set WORK.TEST has 20 observations and 8 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
 
 
2 + proc tabulate data=test out= pret1 ; class sex ; var pulse; table sex ALL="Grand Total" , pulse="Group 1 " * (N="Count"
COLPCTN="%"); run;
 
NOTE: There were 20 observations read from the data set WORK.TEST.
NOTE: The data set WORK.PRET1 has 3 observations and 6 variables.
NOTE: PROCEDURE TABULATE used (Total process time):
real time 0.09 seconds
cpu time 0.09 seconds
 
 
3 + proc sort data=test; by sex ; run;
 
NOTE: Input data set is already sorted, no sorting done.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
 
 
4 + proc tabulate data=test out= pret2 ; class sex ; var pulse; table sex ALL="Grand Total" , pulse="Group 2 " * (N="Count"
COLPCTN="%"); run;
 
NOTE: There were 20 observations read from the data set WORK.TEST.
NOTE: The data set WORK.PRET2 has 3 observations and 6 variables.
NOTE: PROCEDURE TABULATE used (Total process time):
real time 0.04 seconds
cpu time 0.05 seconds
 
 
105
106
107 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
120
 
Solution
‎07-09-2017 03:00 PM
Super User
Super User
Posts: 6,502

Re: Loop through (with a macro) over multiple user defined %LET=

[ Edited ]

SAS will assign the type and length for a variable based on how it is first used. In this case it will be set to $3 to match 'sex'.

 It just looked like it was using SEX on the second iteration because there was no room to store the rest of the value.

Just define a length for the variable.

data _null_;
  length i 8 vars $200;
  do vars="sex","sex age";
    i+1;
...

 

Contributor
Posts: 40

Re: Loop through (with a macro) over multiple user defined %LET=

Dear Tom,

 

Thank you a million!

Contributor
Posts: 40

Re: Loop through (with a macro) over multiple user defined %LET=

the same happens for proc sort BY variables ..in all iterations BY runs only for variable "sex"

Contributor
Posts: 40

Re: Loop through (with a macro) over multiple user defined %LET=

and unfortunately CALL EXECUTE does not iterate over i values "sex" , "sex age"....

 

the second DO LOOP iteration uses again the value of i="sex" instead of i="sex age"....

☑ This topic is solved.

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

Discussion stats
  • 13 replies
  • 203 views
  • 3 likes
  • 4 in conversation