topic Re: Proc Freq Macro Variable List in SAS Programming

Proc Freq Macro Variable List

cc1986 — Wed, 20 Jun 2018 20:46:34 GMT

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!

Re: Proc Freq Macro Variable List

Reeza — Wed, 20 Jun 2018 20:50:21 GMT

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!

Re: Proc Freq Macro Variable List

Astounding — Wed, 20 Jun 2018 21:02:03 GMT

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?

Re: Proc Freq Macro Variable List

ballardw — Thu, 21 Jun 2018 16:28:31 GMT

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

Re: Proc Freq Macro Variable List

avellani — Mon, 15 Apr 2019 22:18:29 GMT

If I understand your question correctly, try using an out=work.xxx after the '/' to output all the rows to a SAS dataset.

change xxx to a sas ds name of your chosing. If ou want to catalogue the output dataset, change work. to a permanent library name.

Then I wrote my Proc Freq code:

proc freq data=vars;

tables (&var1.)*(&var2.) /norow nocol nopercent missing out=work.xxx;

run;

good luck!

Re: Proc Freq Macro Variable List

Tom — Mon, 15 Apr 2019 23:11:18 GMT

First note that AGE CATEGORY is not a valid variable name unless you have set the option VALIDVARNAME to ANY. And even then it can only be used in code by making a name literal out of it.

If you only want to take the combinations that exist in your dataset then you could perhaps just get by with creating a simple macro variable with the X*Y combinations. You can then use the macro variable in your TABLES statement.

proc sql noprint;
select catx('*',nliteral(variable1),nliteral(variable2))
  into :table_list separated by ' '
from ALL
;
quit;
proc freq data=VARS;
  tables &table_list ;
run;

Re: Proc Freq Macro Variable List

avellani — Tue, 16 Apr 2019 01:55:18 GMT

Thanks so much. I'll try it tomorrow and let you know how it goes.