topic Re: Macro for Categorical variables using proc freq in SAS Programming

Macro for Categorical variables using proc freq

srisaipradeep — Fri, 01 Mar 2013 12:09:37 GMT

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.

Re: Macro for Categorical variables using proc freq

esjackso — Fri, 01 Mar 2013 12:51:26 GMT

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,

Re: Macro for Categorical variables using proc freq

data_null__ — Fri, 01 Mar 2013 13:19:48 GMT

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;

...

Re: Macro for Categorical variables using proc freq

chrej5am — Fri, 01 Mar 2013 13:31:36 GMT

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

data_null__ — Fri, 01 Mar 2013 13:46:17 GMT

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

Re: Macro for Categorical variables using proc freq

Tom — Fri, 01 Mar 2013 13:57:42 GMT

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

%mend;

...

tables %twoway(A B C D E) ;

...

Re: Macro for Categorical variables using proc freq

srisaipradeep — Mon, 04 Mar 2013 06:01:37 GMT

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

Re: Macro for Categorical variables using proc freq

srisaipradeep — Mon, 04 Mar 2013 06:03:44 GMT

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.

Re: Macro for Categorical variables using proc freq

srisaipradeep — Mon, 04 Mar 2013 06:15:14 GMT

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

Re: Macro for Categorical variables using proc freq

srisaipradeep — Mon, 04 Mar 2013 07:35:47 GMT

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

Re: Macro for Categorical variables using proc freq

Tom — Mon, 04 Mar 2013 10:26:44 GMT

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.

Re: Macro for Categorical variables using proc freq

Tom — Mon, 04 Mar 2013 10:31:57 GMT

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

Re: Macro for Categorical variables using proc freq

srisaipradeep — Tue, 05 Mar 2013 06:43:30 GMT

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;

%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

Re: Macro for Categorical variables using proc freq

Tom — Tue, 05 Mar 2013 12:58:07 GMT

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;

Re: Macro for Categorical variables using proc freq

srisaipradeep — Wed, 06 Mar 2013 06:28:00 GMT

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

Re: Macro for Categorical variables using proc freq

srisaipradeep — Wed, 06 Mar 2013 06:45:45 GMT

Hi Tom,

I need one more guidance on the proc frequency. I have report to be generated with 250 attributes or variables. in the below code i want the attributes to keep changing till 250 automatically and get the combination. is there any way or do i need to hardcode.

proc freq;

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

weight weight ;

run;

What i am currently doing is

%macro attrib;

proc freq;

tables brand*market*aa1*(&attributes)*b5_own /noprint out=freq ;

weight weight ;

run;

%mend attrib;

%attrib (attributes= attrib1 attrib2 attrib3.....till attrib250).

Can you please guide is there any smarter way to do this?

Thanks and Regards,

S.S.Pradeep

Re: Macro for Categorical variables using proc freq

Tom — Wed, 06 Mar 2013 15:35:00 GMT

If your variables are named with numeric suffixes as in your example then you can use variable list syntax.

attrib1 - attrib250

If not, but you know the physical order of the variables in the dataset then you can use -- between the names

age -- race

Re: Macro for Categorical variables using proc freq

srisaipradeep — Thu, 07 Mar 2013 08:10:56 GMT

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.

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