Proc Freq Macro Variable List

Reply
Occasional Contributor
Posts: 6

Proc Freq Macro Variable List

Hi,

 

First, sorry for posting if this is the wrong place! I'm trying to learn macros better, well macros at all, and I was just given this project that would probably be made simpler by using macros. I need to do a bunch of cross tabs of variables that are in the same dataset. I have a dataset that contains the list of variables to cross tab. For example:

 

Variable 1Variable 2
stateyear
age categoryown pets

 

I wrote code, which I think pulled in the two sets variable names:

 

data _null_;

set all;

call symput("var1", variable_1);

call symput("var2", variable_2);

run;

 

Then I wrote my Proc Freq code:

 

proc freq data=vars;

tables (&var1.)*(&var2.) /norow nocol nopercent missing;

run;

 

The code runs, except that it only outputs the last crosstab. In this case (using fake variable names), age category by own pets. I would like all of the crosstabs to be generated by row, not just the last one. What am I doing wrong? Or would an array work better?

 

Any thoughts/suggestions would be much appreciated!

 

Thanks in advance for your help!

Super User
Posts: 23,776

Re: Proc Freq Macro Variable List

data _null_;

set all;

call symput("var1", variable_1); <- gets reset at each line;

call symput("var2", variable_2);

run;

 

You're macro variables VAR1 and Var2 are getting created only at the last line because you're reusing the same macro variables so it's overwritten each time. You don't go into enough detail, but I would probably create my PROC FREQ as a macro and pass the parameters to the macro call instead using CALL EXECUTE.

 

 

%macro myFreq(var1, var2);

proc freq data=vars;

tables (&var1.)*(&var2.) /norow nocol nopercent missing;

run;

%mend;

Then for each line pass the values.

 

data _null_;
set all;

str = catt('%myFreq(', var1, ', ', var2, ');');

call execute(str);
run;

@cc1986 wrote:

Hi,

 

First, sorry for posting if this is the wrong place! I'm trying to learn macros better, well macros at all, and I was just given this project that would probably be made simpler by using macros. I need to do a bunch of cross tabs of variables that are in the same dataset. I have a dataset that contains the list of variables to cross tab. For example:

 

Variable 1 Variable 2
state year
age category own pets

 

I wrote code, which I think pulled in the two sets variable names:

 

data _null_;

set all;

call symput("var1", variable_1);

call symput("var2", variable_2);

run;

 

Then I wrote my Proc Freq code:

 

proc freq data=vars;

tables (&var1.)*(&var2.) /norow nocol nopercent missing;

run;

 

The code runs, except that it only outputs the last crosstab. In this case (using fake variable names), age category by own pets. I would like all of the crosstabs to be generated by row, not just the last one. What am I doing wrong? Or would an array work better?

 

Any thoughts/suggestions would be much appreciated!

 

Thanks in advance for your help!


 

Super User
Posts: 13,583

Re: Proc Freq Macro Variable List

[ Edited ]

Or perhaps to avoid reloading the data set for each table:

 

data _null_;
   set all end=last;
   if _n_=1 then call execute("proc freq data=vars;");
   call execute (catx(' ','tables ',variable_1,'*',variable_2,'/norow nocol nopercent missing;'));
   if last then call execute('run;');
run;

This assumes the data set all contains the pairings you want in variable_1 and variable_2

 

Super User
Posts: 6,785

Re: Proc Freq Macro Variable List

Two questions to start.

 

First, are the values of VARIABLE_1 and VARIABLE_2 always correctly spelled variable names?  AGE_CATEGORY would be correct name, but AGE CATEGORY would (under normal circumstances) be invalid.

 

Second, which cross tabs do you want?  The code you are posting, if it had worked, would generate 4 cross-tabs:

 

state * year

state * own_pets

age_category * year

age_category * own_pets

 

Are you looking for all four, or just the first and last ones?

Ask a Question
Discussion stats
  • 3 replies
  • 88 views
  • 2 likes
  • 4 in conversation