BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Zeus_Olympous
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

13 REPLIES 13
Astounding
PROC Star

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)

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Zeus_Olympous
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Zeus_Olympous
Obsidian | Level 7

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

 

Thanks again.

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Zeus_Olympous
Obsidian | Level 7

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;

Tom
Super User Tom
Super User

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;

 

Zeus_Olympous
Obsidian | Level 7

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
 
Tom
Super User Tom
Super User

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

 

Zeus_Olympous
Obsidian | Level 7

Dear Tom,

 

Thank you a million!

Zeus_Olympous
Obsidian | Level 7

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

Zeus_Olympous
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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