Two way table using PROC TABULATE

[Priyamvada07]

I have the following data

proc format;
picture count (round)
0-4 = ' <5' (NOEDIT);
picture pcnt (round)
0 = ' - '
other = '009.9%';

DATA HAVE;
input year dz $8. area;
cards;
2000 stroke 08
2000 stroke 06
2000 stroke 06
2001 stroke 08
2001 stroke 06
2001 stroke 06
2002 stroke 08
2002 stroke 06
2002 stroke 06
;
run;

I want a 2-D table and want to present 'Frequency' and 'Row Pct' and retain the 'Total' row; I also want to mask values below 5 in 'Frequency' using format 'count.' and 'pcnt.'

I have tried using PROC TABULATE which would be an easier option for me

proc tabulate data=have;
class area year;
classlev year / s=[just=left];
keylabel pctn='%';
tables year=' ' ((area=' ')*(n='Frequency'*f=count. rowpctn='Percent'*f=pcnt.)
(all='Total')*(n='Frequency'*f=count. rowpctn='Percent'*f=pcnt.))
/box=year;
run;

However this code is giving me the following error

ERROR: Too few dimensions specified in the following nesting : area * RowPctN * f.
ERROR: Too few dimensions specified in the following nesting : All * RowPctN * f.

I am new to proc tabulate and cannot understand what this error is about. 

When using proc freq I get the below output.

proc freq data=HAVE;
tables year*area;
run;

When I use the below code to remove cumulative row I get the table in another way which is not desired.

proc freq data=HAVE;
tables area / list nocum out=a;
run;

In a one way table I was able to create a output using 'out=a' and then use the formats 'count.' and 'pcnt.' in the PROC PRINT step. The code that I used for one way table is as below

proc freq data=HAVE_rev;
tables area / list nocum out=a;
run;

proc print data=a;
format count count. percent pcnt. ;
run;

However, this is not giving me the desired result for two way table!

Any guidance on this preferably using PROC TABULATE (PROC FREQ will also do)?

Thanks!

[Reeza · Accepted solution]

When I use the below code to remove cumulative row I get the table in another way which is not desired.

proc freq data=HAVE;
tables area / list nocum out=a;
run;

NOCUM removes the cumulative, but LIST changes the layout of the table. If you don't want the layout changed, remove the LIST option. But your previous code as a nxn table and this is a one way table so it's not even the same problem. A nxn table does not have a cumulative column, it has the row/col percent which can also be controlled with options in PROC FREQ.

Please show EXACTLY what you want as output. You have a couple of requirements but it's not clear, so far:

1. N and PCTN for one way tables, not sure about nxn tables. 
2. Masking for N>5 -> what about the percent values
3. Which columns combined? FREQ puts them on the same cell, TABULATE does not. 
4. I suspect your code from the previous post is missing a comma.

FYI - I'm assuming you've reviewed the documentation and are slowly building your procs step by step. So first start with a base table and then add in the features such as (ALL, PCTN and formats) as you go so you can test each option as you add it. If I tried to write a PROC TABULATE with that structure from scratch I'd get it wrong for sure and would also follow this process even though I've been programming for 15 years. 

[Priyamvada07]

Looks like in the PROC TABULATE code 'it was all about the comma'!!!! Thank you very much for pointing that out and all other info, very very helpful. I got my desired table 🙂

[Reeza]

FYI - if you display percents and your total is >5 and displayed you can work backwards to figure out the N's so that suppression may not be adequate.