topic Create proc freq crosstab for all column combinations in a dataset in SAS Programming

Create proc freq crosstab for all column combinations in a dataset

irvinery — Tue, 27 Feb 2024 18:12:31 GMT

Hi fellow SAS users,

I am trying to create 2x2 tables with Cramer's V to assess correlation of categorical variables. It is a very large table with many columns, but for this example, lets say:

I have a table consisting of 5 columns called A, B, C, D, and E. I want to output a proc freq with Cramer's V for each combination. I know I can do something like:

proc freq data=dataset; table (A--E)*(A--E) / chisq; run;

But I don't want duplicates (i.e., I do not need both the A*B and B*A crosstabs). I do not have much experience writing arrays, but I think it should be possible to accomplish this task using one. The real table has 30+ columns, so typing out all the combinations to avoid duplications isn't practical.

Thanks in advance for any help the community can provide! 😁

Re: Create proc freq crosstab for all column combinations in a dataset

PaigeMiller — Tue, 27 Feb 2024 18:55:12 GMT

I would re-arrange the data such that you can use a BY statement in PROC FREQ. (Alternative is to use a macro)

data have;
    input a b c d e;
    cards;
7 10 11 12 14
7 11 10 12 13
8 10 12 13 14
;

data re_arrange;
    set have;
    array v a--e;
    do i=1 to dim(v)-1;
        do j=i+1 to dim(v);
            value1=v(i);
            value2=v(j);
            type = cats(vname(v(i)),'*',vname(v(j)));
            output;
        end;
    end;
    drop i j;
run;
proc sort data=re_arrange;
    by type;
run;

proc freq data=re_arrange;
    by type;
    table value1*value2/chisq;
run;

Sending this to a text or html output would produce a very long report where you need just a tiny fraction of the results to get Cramer's V. Better would be sending the statistics to a data set, called CHISQ and then filtering the row so you get only the cases where STATISTIC="Cramer's V". Replace the PROC FREQ with this:

ods select none;
ods output chisq=chisq(where=(statistic=:'Cram') drop=table);
proc freq data=re_arrange;
    by type;
    table value1*value2/chisq;
run;

If all the variables have labels (which I would strongly recommend), you could then use the VLABEL() function instread of the VNAME() function.

Re: Create proc freq crosstab for all column combinations in a dataset

ballardw — Tue, 27 Feb 2024 18:57:06 GMT

@irvinery wrote:

Hi fellow SAS users,

I am trying to create 2x2 tables with Cramer's V to assess correlation of categorical variables. It is a very large table with many columns, but for this example, lets say:

I have a table consisting of 5 columns called A, B, C, D, and E. I want to output a proc freq with Cramer's V for each combination. I know I can do something like:
proc freq data=dataset; table (A--E)*(A--E) / chisq; run;
But I don't want duplicates (i.e., I do not need both the A*B and B*A crosstabs). I do not have much experience writing arrays, but I think it should be possible to accomplish this task using one. The real table has 30+ columns, so typing out all the combinations to avoid duplications isn't practical.

Thanks in advance for any help the community can provide! 😁

Since Arrays only exist for the duration of a data step or Proc Iml I am not sure how you are thinking of using this.

Unless you are thinking of placing the the names of the variables into an array and using one of the combinatorial functions to write the code.

Re: Create proc freq crosstab for all column combinations in a dataset

PaigeMiller — Tue, 27 Feb 2024 19:09:31 GMT

@irvinery it seems to me it is wrong to mark that as the correct answer, as I have already shown how to use arrays in this problem.

Re: Create proc freq crosstab for all column combinations in a dataset

irvinery — Tue, 27 Feb 2024 19:29:16 GMT

Hi Paige,
I didn't select that as the right answer, so I"m not sure how that happened.

Re: Create proc freq crosstab for all column combinations in a dataset

FreelanceReinh — Tue, 27 Feb 2024 19:39:02 GMT

You can change the selection easily: Select a different post as the solution after clicking "Not the Solution" in the option menu (see icon below) of the current solution.

Re: Create proc freq crosstab for all column combinations in a dataset

irvinery — Mon, 04 Mar 2024 15:35:14 GMT

Done! Thanks again for the help.