Macro for Categorical variables using proc freq

[srisaipradeep]

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.

[esjackso]

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

[srisaipradeep]

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?

Please let me know

thanks and regards,

S.S.Pradeep

[data_null__]

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;

...

[chrej5am · Accepted solution]

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

[data_null__]

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_);

[srisaipradeep]

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?

Please let me know your thoughts

Thank you.

[Tom]

%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) ;

...

[srisaipradeep]

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?

Please let me know your thoughts,

Thanks and Regards,

S.S.Pradeep

Message was edited by: pradeep sridharan Can we

[Tom]

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.

[srisaipradeep]

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,

S.S.Pradeep

[Tom]

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

[srisaipradeep]

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)

I have modified your code

%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,

S.S.Pradeep

[Tom]

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;

[srisaipradeep]

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.

Please guide,

Thanks and Regards,

S.S.Pradeep