BookmarkSubscribeRSS Feed
cc1986
Calcite | Level 5

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!

6 REPLIES 6
Reeza
Super User

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!


 

ballardw
Super User

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

 

avellani
Fluorite | Level 6

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!

Astounding
PROC Star

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?

Tom
Super User Tom
Super User

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;

 

avellani
Fluorite | Level 6
Thanks so much. I'll try it tomorrow and let you know how it goes.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 6 replies
  • 4765 views
  • 2 likes
  • 6 in conversation