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

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

Zeus_Olympous — Fri, 07 Jul 2017 09:30:13 GMT

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

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

Astounding — Fri, 07 Jul 2017 12:43:03 GMT

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)

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

RW9 — Fri, 07 Jul 2017 10:32:48 GMT

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.

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

Zeus_Olympous — Fri, 07 Jul 2017 12:01:09 GMT

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

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

RW9 — Fri, 07 Jul 2017 12:26:13 GMT

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

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;

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

Zeus_Olympous — Fri, 07 Jul 2017 13:23:08 GMT

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

Thanks again.

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

RW9 — Fri, 07 Jul 2017 13:31:58 GMT

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

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

Zeus_Olympous — Sun, 09 Jul 2017 13:23:11 GMT

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;

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

Zeus_Olympous — Sun, 09 Jul 2017 13:43:15 GMT

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"....

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

Tom — Sun, 09 Jul 2017 13:50:44 GMT

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;

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

Zeus_Olympous — Sun, 09 Jul 2017 14:04:07 GMT

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;

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;

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

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

Zeus_Olympous — Sun, 09 Jul 2017 14:07:56 GMT

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

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

Tom — Sun, 09 Jul 2017 17:55:47 GMT

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;
...

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

Zeus_Olympous — Sun, 09 Jul 2017 18:59:44 GMT

Dear Tom,

Thank you a million!