BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
srisaipradeep
Calcite | Level 5


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
chrej5am
Quartz | Level 8

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

View solution in original post

17 REPLIES 17
esjackso
Quartz | Level 8

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
Calcite | Level 5

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__
Jade | Level 19

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
Quartz | Level 8

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__
Jade | Level 19

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
Calcite | Level 5

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
Super User Tom
Super User

%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
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5

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
Super User Tom
Super User

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

srisaipradeep
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 17 replies
  • 4340 views
  • 10 likes
  • 5 in conversation