topic Re: Cross tabulation of variables using (multiple variables as row) in SAS Procedures

Cross tabulation of variables using (multiple variables as row)

dac_js — Tue, 27 Oct 2020 11:06:34 GMT

I have the following table:

ID	color	VAR_1	VAR_2	VAR_3	VAR_4
1	1	1	0	1	0
2	1	0	0	1	1
3	1	1	0	1	0
4	1	0	0	1	1
5	1	1	0	0	0
6	0	1	0	1	1
7	0	1	0	1	0
8	0	1	0	1	0
9	0	1	1	1	0
10	0	1	1	1	1

and I want this in the following form:

	color
	0	1
VAR_1 (=1)	5	3
VAR_2 (=1)	2	0
VAR_3 (=1)	5	4
VAR_4 (=1)	2	2

The following produces separate tables:

data have;
input ID color var_1 var_2 var_3 var_4;
datalines;
1 1 1 0 1 0
2 1 0 0 1 1
3 1 1 0 1 0
4 1 0 0 1 1
5 1 1 0 0 0
6 0 1 0 1 1
7 0 1 0 1 0
8 0 1 0 1 0
9 0 1 1 1 0
10 0 1 1 1 1
;
run;

proc freq data=have;

table (var_1 var_2 var_3 var_4)*color/ nopercent norow nocol;

run;

I am looking for a procedure that gives the answer in the same table.

Re: Cross tabulation of variables using (multiple variables as row)

SASJedi — Tue, 27 Oct 2020 11:31:15 GMT

Moved to the SAS Procedures community for better visibility.

Re: Cross tabulation of variables using (multiple variables as row)

PeterClemmensen — Tue, 27 Oct 2020 12:00:40 GMT

My 2 cents

data have;
input ID color VAR_1 VAR_2 VAR_3 VAR_4;
datalines;
1 1 1 0 1 0
2 1 0 0 1 1
3 1 1 0 1 0
4 1 0 0 1 1
5 1 1 0 0 0
6 0 1 0 1 1
7 0 1 0 1 0
8 0 1 0 1 0
9 0 1 1 1 0
10 0 1 1 1 1
;

proc tabulate data = have;
   class color;
   var var_:;
   table (var_:)*sum=''*f=8., color / row=float;
run;

Re: Cross tabulation of variables using (multiple variables as row)

SASJedi — Tue, 27 Oct 2020 12:04:44 GMT

Sweet!

Re: Cross tabulation of variables using (multiple variables as row)

SteveDenham — Tue, 27 Oct 2020 12:11:21 GMT

There is a way to do this using PROC MEANS, but it is extremely messy (I just now gave writing the code), and another using PROC SQL, which isn't nearly as bad, but the best way that ends up with the counts in a table automatically is the one proposed by @PeterClemmensen .

SteveDenham

Re: Cross tabulation of variables using (multiple variables as row)

dac_js — Tue, 27 Oct 2020 13:14:14 GMT

Thanks for your help. Here is a follow up question.

Is it possible to get the data in the following format:

	color
	0	0 (%)	1	1 (%)	Total
VAR_2	5	62.5	3	37.5	8
VAR_3	2	100	0	0	2
VAR_4	5	55.56	4	44.44	9
VAR_5	2	50	2	50	4

Adding an ALL to your code gives me the total column.

proc tabulate data = have;
class color;
var var_:;
table (var_:)*sum=''*f=8., color all/ row=float;
run;

Could not find a way to add percentage to each cells.

Re: Cross tabulation of variables using (multiple variables as row)

Ksharp — Wed, 28 Oct 2020 04:54:06 GMT

data have;
input ID color VAR_1 VAR_2 VAR_3 VAR_4;
datalines;
1 1 1 0 1 0
2 1 0 0 1 1
3 1 1 0 1 0
4 1 0 0 1 1
5 1 1 0 0 0
6 0 1 0 1 1
7 0 1 0 1 0
8 0 1 0 1 0
9 0 1 1 1 0
10 0 1 1 1 1
;

proc tabulate data = have;
   class color;
   var var_:;
   table (var_:) , color*(sum='n'*f=8.0 rowpctsum='n(%)') sum='Total'*f=8.0/ row=float;
run;

Re: Cross tabulation of variables using (multiple variables as row)

dac_js — Wed, 28 Oct 2020 06:44:26 GMT

It worked perfectly.
Thank you

Re: Cross tabulation of variables using (multiple variables as row)

PeterClemmensen — Wed, 28 Oct 2020 11:19:40 GMT

@Ksharp Very nice!