Why the class statement in Proc Tabulate mess the code?

[Scorpx]

Hello!

I have a set of 17 variables from a survey.

I am running some tests, so I did a simple proc freq:

proc freq data=sw1;
table Q20_1;
run;

And I get this result: 

   

Q20_1	Frequency	Percent	CumulativeFrequency	CumulativePercent
1	74	21.02	74	21.02
2	126	35.80	200	56.82
3	113	32.10	313	88.92
4	39	11.08	352	100.00
Frequency Missing = 668

Then I tried a proc tabulate because I needed to do further analysis with the data, and everything when alright:

proc tabulate data=sw1;
class Q20_1;
table (n='Count'*f=7. pctn<Q20_1>='Test1'*f=pctfmt9.2),Q20_1;
run;

Results are the same but transposed, and that's ok.

But, when I added the rest of the variables to the code to start getting the tables, the results were messed up. 

proc tabulate data=sw1;
class Q20_1 Q20_2 Q20_3 Q20_4 Q20_5 Q20_6 Q20_7 Q20_8 Q20_9 Q20_10 Q20_11 Q20_12 Q20_13 Q20_14 Q20_15 Q20_16 Q20_17;
table (n='Count'*f=7. pctn<Q20_1>='Test1'*f=pctfmt9.2),Q20_1;
table (pctn<q20_2>='Test2'*f=pctfmt9.2),q20_2;
table (pctn<q20_3>='Test3'*f=pctfmt9.2),q20_3;
table (pctn<q20_4>='Test4'*f=pctfmt9.2),q20_4;
table (pctn<q20_5>='Test5'*f=pctfmt9.2),q20_5;
table (pctn<q20_6>='Test6'*f=pctfmt9.2),q20_6;
table (pctn<q20_7>='Test7'*f=pctfmt9.2),q20_7;
table (pctn<q20_8>='Test8'*f=pctfmt9.2),q20_8;
table (pctn<q20_9>='Test9'*f=pctfmt9.2),q20_9;
table (pctn<q20_10>='Test10'*f=pctfmt9.2),q20_10;
table (pctn<q20_11>='Test11'*f=pctfmt9.2),q20_11;
table (pctn<q20_12>='Test12'*f=pctfmt9.2),q20_12;
table (pctn<q20_13>='Test13'*f=pctfmt9.2),q20_13;
table (pctn<q20_14>='Test14'*f=pctfmt9.2),q20_14;
table (pctn<q20_15>='Test15'*f=pctfmt9.2),q20_15;
table (pctn<q20_16>='Test16'*f=pctfmt9.2),q20_16;
table (pctn<q20_17>='Test17'*f=pctfmt9.2),q20_17;
run;

Output for test1:

		Q20_1
	1	2	3
Count	3	1	2
Test1	50.00	16.67	33.33

Why is this "big" class statement affecting another statement where I am not calling the rest of the variables?

Thanks for your time, and I appreciate your help.

SAS EG 7.12 (7.100.2.3350) (64-bit)

[Reeza]

How is it messing it up? Maybe I'm missing it, but don't see any explanation of what's happening that's unexpected.

In general though CLASS statements only really affect if you have missing data. In that case, usually all missing are excluded. If that's what's happening, you need to either split the code up, one for each variable OR add the MISSING option to your CLASS statement so missing values are included OR (my recommendation) transpose your data so that the Question Number is a variable and responses are a different variable. Then you can use a single CLASS statement on that QUESTION and your code is also much simplified. This is usually now I code my summaries.

[Scorpx]

Hi @Reeza 

Thanks, the /missing partially solved the issue

But as you know,/missing makes SAS count missing values in the calculation. So now, my percentages are not correct.

I will try to clarify further:

Without /missing and only one variable in the class statement:

proc tabulate data=sw1;
class Q20_1;
table (n='Count'*f=7. pctn<Q20_1>='Test1'*f=pctfmt9.2),Q20_1;
run;

I got this:

		Q20_1
	1	2	3	4
Count	74	126	113	39
Test 1	21.02	35.80	32.10	11.08

With several variables in the class statement without /missing:

proc tabulate data=sw1;
class Q20_1 Q20_2 Q20_3 Q20_4 Q20_5 Q20_6 Q20_7 Q20_8 Q20_9 Q20_10 Q20_11 Q20_12 Q20_13 Q20_14 Q20_15 Q20_16 Q20_17;
table (n='Count'*f=7. pctn<Q20_1>='Test1'*f=pctfmt9.2),Q20_1;
table (pctn<q20_2>='Test2'*f=pctfmt9.2),q20_2;
table (pctn<q20_3>='Test3'*f=pctfmt9.2),q20_3;
table (pctn<q20_4>='Test4'*f=pctfmt9.2),q20_4;
table (pctn<q20_5>='Test5'*f=pctfmt9.2),q20_5;
table (pctn<q20_6>='Test6'*f=pctfmt9.2),q20_6;
table (pctn<q20_7>='Test7'*f=pctfmt9.2),q20_7;
table (pctn<q20_8>='Test8'*f=pctfmt9.2),q20_8;
table (pctn<q20_9>='Test9'*f=pctfmt9.2),q20_9;
table (pctn<q20_10>='Test10'*f=pctfmt9.2),q20_10;
table (pctn<q20_11>='Test11'*f=pctfmt9.2),q20_11;
table (pctn<q20_12>='Test12'*f=pctfmt9.2),q20_12;
table (pctn<q20_13>='Test13'*f=pctfmt9.2),q20_13;
table (pctn<q20_14>='Test14'*f=pctfmt9.2),q20_14;
table (pctn<q20_15>='Test15'*f=pctfmt9.2),q20_15;
table (pctn<q20_16>='Test16'*f=pctfmt9.2),q20_16;
table (pctn<q20_17>='Test17'*f=pctfmt9.2),q20_17;
run;

I got this:

		Q20_1
	1	2	3
Count	3	1	2
Test1	50.00	16.67	33.33

I added the /missing to the class statement, returning the output to "normal." But the percentages are incorrect (because I don't want it to count the missing values).

  

			Q20_1
	.	1	2	3	4
Count	668	74	126	113	39
Test1	65.49	7.25	12.35	11.08	3.82

PS: I don't know if I am working double, but I do the transpose AFTER I get the proc tabulate results 

[Reeza · Accepted solution]

Transpose before you get the PROC TABULATE results.

When you have missing values in CLASS statements that row will get excluded from all values, which means your values will be wrong.

So as stated you have two options - either call proc tabulate for each variable alone.
Or transpose and then do a proc tabulate. Since you're proc transposing after, this is the better option but you should also check the PROC FREQ code I posted which could help you avoid this all together.

[Reeza]

This may also get you what you want: 

*Run frequency for tables;
ods table onewayfreqs=temp;
proc freq data=sw1;
	table Q20_1 - Q20_17 / missing;
run;

*Format output;
data want;
length variable $32. variable_value $50.;
set temp;
Variable=scan(table, 2);

Variable_Value=strip(trim(vvaluex(variable)));

keep variable variable_value frequency percent cum:;
label variable='Variable' 
	variable_value='Variable Value';
run;

*Display;
proc print data=want(obs=20) label;
run;

---

@Scorpx wrote:

Hello!

I have a set of 17 variables from a survey.

I am running some tests, so I did a simple proc freq:

proc freq data=sw1;
table Q20_1;
run;

And I get this result: 

   

Q20_1	Frequency	Percent	CumulativeFrequency	CumulativePercent
1	74	21.02	74	21.02
2	126	35.80	200	56.82
3	113	32.10	313	88.92
4	39	11.08	352	100.00
Frequency Missing = 668

Then I tried a proc tabulate because I needed to do further analysis with the data, and everything when alright:

proc tabulate data=sw1;
class Q20_1;
table (n='Count'*f=7. pctn<Q20_1>='Test1'*f=pctfmt9.2),Q20_1;
run;

Results are the same but transposed, and that's ok.

But, when I added the rest of the variables to the code to start getting the tables, the results were messed up. 

proc tabulate data=sw1;
class Q20_1 Q20_2 Q20_3 Q20_4 Q20_5 Q20_6 Q20_7 Q20_8 Q20_9 Q20_10 Q20_11 Q20_12 Q20_13 Q20_14 Q20_15 Q20_16 Q20_17;
table (n='Count'*f=7. pctn<Q20_1>='Test1'*f=pctfmt9.2),Q20_1;
table (pctn<q20_2>='Test2'*f=pctfmt9.2),q20_2;
table (pctn<q20_3>='Test3'*f=pctfmt9.2),q20_3;
table (pctn<q20_4>='Test4'*f=pctfmt9.2),q20_4;
table (pctn<q20_5>='Test5'*f=pctfmt9.2),q20_5;
table (pctn<q20_6>='Test6'*f=pctfmt9.2),q20_6;
table (pctn<q20_7>='Test7'*f=pctfmt9.2),q20_7;
table (pctn<q20_8>='Test8'*f=pctfmt9.2),q20_8;
table (pctn<q20_9>='Test9'*f=pctfmt9.2),q20_9;
table (pctn<q20_10>='Test10'*f=pctfmt9.2),q20_10;
table (pctn<q20_11>='Test11'*f=pctfmt9.2),q20_11;
table (pctn<q20_12>='Test12'*f=pctfmt9.2),q20_12;
table (pctn<q20_13>='Test13'*f=pctfmt9.2),q20_13;
table (pctn<q20_14>='Test14'*f=pctfmt9.2),q20_14;
table (pctn<q20_15>='Test15'*f=pctfmt9.2),q20_15;
table (pctn<q20_16>='Test16'*f=pctfmt9.2),q20_16;
table (pctn<q20_17>='Test17'*f=pctfmt9.2),q20_17;
run;

Output for test1:

		Q20_1
	1	2	3
Count	3	1	2
Test1	50.00	16.67	33.33

Why is this "big" class statement affecting another statement where I am not calling the rest of the variables?

Thanks for your time, and I appreciate your help.

SAS EG 7.12 (7.100.2.3350) (64-bit)

---