Cross tabulation of variables using (multiple variables as row)

[dac_js]

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.

[SASJedi]

Moved to the SAS Procedures community for better visibility.

[PeterClemmensen · Accepted solution]

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;

[SASJedi]

Sweet!

[SteveDenham]

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

[dac_js]

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. 

[Ksharp]

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;

[dac_js]

It worked perfectly.
Thank you

[PeterClemmensen]

@Ksharp Very nice!