Calcite | Level 5

## Macro for Categorical variables using proc freq

Hi Friends,

I need all your help . I need a macro which can run all possible combinations for the N number of variables inside a proc freq.

For eg.

I have 5 variables. Say A B C D and E  all are binary . I need a 2 way classifcation of variables using proc freq. like it should generate

frequencies for AB AC AD AE BC BD BE CD and CE.

Now how i am doing is passing the values for each combination.

%macro freqproc freq data=chk;

table &var1*&var2;

run;

%mend freq;

%freq(var1=a , var2=b);

%freq(var1=a, var2=c);

.................................

................

What i need an help on is that instead of pasing the values like this. I need an macro which can in one shot give me frequency for all possible combination. Please help.

1 ACCEPTED SOLUTION

Accepted Solutions
Quartz | Level 8

## Re: Macro for Categorical variables using proc freq

I think the fastest way is to go with reply of EJ. Anyway a macro could like this

/*just for quick test*/

data test;

A=1;B=2;C=3;D=4;E=5;

run;

/*macro definition*/

%macro freqs(list_of_vars=,dataset=);

%local num_of_all test_number i;

%let num_of_all=%sysfunc(countw(&list_of_vars));

%let test_number=%sysfunc(comb(&num_of_all.,2));

proc plan;

factors test_number=&test_number ordered

class= 2 of &num_of_all. comb ;

ods output plan=combinations;

run;

quit;

data _null_;

set combinations;

call symputx('first'||strip(put(_N_,best12.)),class1);

call symputx('second'||strip(put(_N_,best12.)),class2);

run;

%do i = 1 %to &test_number.;

proc freq data=&dataset.;

table %scan(&list_of_vars.,&&first&i)*%scan(&list_of_vars.,&&second&i)/out=freq&i.;

run;

%end;

%mend freqs;

/*macrocall*/

%freqs(list_of_vars=A B C D E,dataset=test);

Hope it helps

Jakub

17 REPLIES 17
Quartz | Level 8

## Re: Macro for Categorical variables using proc freq

Is there a problem doing Table (A B C D E)*(A B C D E)?  There will be 5 extra tables (AA BB CC DD EE) but those could be deleted out using ODS document features.

Just a thought,

EJ

Calcite | Level 5

## Re: Macro for Categorical variables using proc freq

Hi,

Thank you for the reply and help. Can we also extend this to more than A B C D E  what i mean is  for 'N' number of variables?

thanks and regards,

## Re: Macro for Categorical variables using proc freq

I think has the best idea.  No macro involved is always at the top of my list.

Another possibility would be to use the WAYS statement in PROC SUMMARY.

...

CLASS A B C D E;

WAYS 2;

...

Quartz | Level 8

## Re: Macro for Categorical variables using proc freq

I think the fastest way is to go with reply of EJ. Anyway a macro could like this

/*just for quick test*/

data test;

A=1;B=2;C=3;D=4;E=5;

run;

/*macro definition*/

%macro freqs(list_of_vars=,dataset=);

%local num_of_all test_number i;

%let num_of_all=%sysfunc(countw(&list_of_vars));

%let test_number=%sysfunc(comb(&num_of_all.,2));

proc plan;

factors test_number=&test_number ordered

class= 2 of &num_of_all. comb ;

ods output plan=combinations;

run;

quit;

data _null_;

set combinations;

call symputx('first'||strip(put(_N_,best12.)),class1);

call symputx('second'||strip(put(_N_,best12.)),class2);

run;

%do i = 1 %to &test_number.;

proc freq data=&dataset.;

table %scan(&list_of_vars.,&&first&i)*%scan(&list_of_vars.,&&second&i)/out=freq&i.;

run;

%end;

%mend freqs;

/*macrocall*/

%freqs(list_of_vars=A B C D E,dataset=test);

Hope it helps

Jakub

## Re: Macro for Categorical variables using proc freq

I wonder how you would have to change your macro to allow LIST_OF_VARS to be a "SAS Variable List"?

%freqs(list_of_vars=A--E);

%freqs(list_of_vars=_CHAR_);

Calcite | Level 5

## Re: Macro for Categorical variables using proc freq

Hi,

Thank you so much for the reply. It worked perfectly. Just would like to know since here we are having only 5 variables. say from A to E . so its easy to assign numbers to them from 1-5. Suppose we have 1000 variables will that be feasible to assign 1000 numbers to all the variables?

Thank you.

Super User

## Re: Macro for Categorical variables using proc freq

%macro twoway(varlist);

%local i j;

%do i=1 %to %sysfunc(countw(&varlist,%str( )))-1;

%do j=&i+1 %to %sysfunc(countw(&varlist,%str( )));

%scan(&varlist,&i)*%scan(&varlist,&j)

%end;

%end;

%mend;

...

tables %twoway(A B C D E) ;

...

Calcite | Level 5

## Re: Macro for Categorical variables using proc freq

Hi Tom,

Thank you .It worked perfectly .I also need one help suppose we have 100 variables then how can we list then inside the macros . its not possible to give all the 100 variables inside the proc freq. In this example we have just 5 variables so we could list them . Can we have generalized code to include all the 100 variables.

Can we extend this to a Three way or Four way classifcation?

Thanks and Regards,

Message was edited by: pradeep sridharan Can we

Super User

## Re: Macro for Categorical variables using proc freq

You can extend it as much as you want. To generate two way combinations I use two %DO loops.  To generate three way you would want to code three %DO loops. etc.

There are many ways to ask SAS to tell you what variables are in a dataset.

If you really want the flexibility in specifying variables and combinations of variables then use PROC SUMMARY instead of PROC FREQ.

Calcite | Level 5

## Re: Macro for Categorical variables using proc freq

Hi Tom,

Thank you. As of now its creating all the combinations. i need one more favour.

All the 5 variables are binary. for eg. A has value 0 and 1, B has a value 0 and 1 and C....

So what i need an help on is . 0 of A with 1 of B,

1 of A with 0 of B,

1 of A with 1 of B,

0 of A with 0 of B

the macro i am looking out for should do for all the A to E variables.

Please let me know the thoughts,

Thanks and Regards,

Super User

## Re: Macro for Categorical variables using proc freq

I don't think it makes any difference whether the variable has two levels or twenty when requesting a frequency table.

Calcite | Level 5

## Re: Macro for Categorical variables using proc freq

Hi Tom,

Thank you for the tips. Please find below the code which i am trying.. but unable to get the desire results.

I have four variables - 1) b5_own has two values ( 0 ,1)

2) AA1 has two values (0 ,1)

3) brand ( has 20 brands)

4) market ( has 10 markets)

%macro twoway(varlist);

%local i j k l;

%do i=1 %to %sysfunc(countw(&varlist,%str( )))-1;

%do j=&i+1 %to %sysfunc(countw(&varlist,%str( )));

%do k=1 %to %sysfunc(countw(&varlist,%str( )));

%do l=1 %to %sysfunc(countw(&varlist,%str( )));

%scan(&varlist,&i)*%scan(&varlist,&j)*%scan(&varlist,&k)*%scan(&varlist,&l)

%end;

%end;

%end;

%end;

%mend;

proc freq data=chks;

tables %twoway(b5_own aa1 brand market) ;

weight weight;

run;

Can you please guide where the mistake is because i couldnt match the numbers..

Thanks and Regards,

Super User

## Re: Macro for Categorical variables using proc freq

If you have four variables and you want them all crossed in a TABLES statement then just separate them with * instead of spaces.

If you want to modify the little pair generating macro to generate four-way crossing then you will need to adjust the upper and lower bounds of the do loops.

%let n=%sysfunc(countw(&varlist));

%do i=1 %to &N-3;

%do j=&i+1 %to &N-2;

%do k=&j+1 %to &N-1 ;

%do l=&k+1 %to &N ;

Why not just use PROC SUMMARY?

Compare the output dataset you get with this PROC FREQ call

proc freq;

tables b5_own*aa1*brand*market /noprint out=freq ;

weight weight ;

run;

With the output dataset you get with this PROC SUMMARY call.

proc summary data = sample ;

var weight ;

class b5_own aa1 brand market ;

ways 4;

output out=summary sum=;

run;

Calcite | Level 5

## Re: Macro for Categorical variables using proc freq

Hi Tom,

Thank you for the guidance. As you suggested Proc summary is the best choice. I tried running both proc freq and proc summary.

1. Proc freq - i got the percent frequency count and percentage of total frequency in the output dataset - so if i remove the noprint option and run i am getting the exact number which i am looking out for.

2. proc summary - here i got the type , freq and weight in the output dataset. how can i go about in converting or transpoing the dataset to the exact percentage i am getting in proc freq.