proc tabulate -percent from total column per each group

[Ronein]

Hello

I want to calculate percent from total in each category.

I want to create the following summary table.

What is the way to do it please?

Data Rawtbl;
input ID field $  category  year ;
cards;
1 x 1 2020 
1 w 2 2020
1 z 2 2020
2 x 2 2020
2 w 3 2020
2 z 2 2020
3 x 2 2020
3 w 2 2020
3 z 2 2020
4 x 1 2020
4 w 2 2020
4 z 1 2020
5 x 3 2020
5 w 2 2020
5 z 2 2020
6 x 2 2021 
6 w 3 2021
6 z 1 2021
2 x 1 2021
2 w 1 2021
2 z 1 2021
3 x 2 2021
3 w 2 2021
3 z 3 2021
4 x 3 2021
4 w 2 2021
4 z 1 2021
;
run;
PROC TABULATE DATA=Rawtbl OUT=ttt ;
CLASS  field category  year ;
VAR ID ; 
TABLE (field='')*(category='' ALL='Total'),year=''*N/misstext='0';
RUN;

[ballardw]

If the columns have to be in that order Tabulate is not going to do that.

[Cynthia_sas · Accepted solution]

Hi:

  The reason that TABULATE won't do the columns in that order is that you want the percents to be based on the category values in each field group and for that to happen, both category and field need to be in the row dimension in TABULATE. You can get THIS order to the columns:

But you can't get FIELD and CATEGORY on the far right the way you envision. To get the percent of the subgroup for each field, you need to use a custom denominator in TABULATE (if you can live with the column order).

Cynthia

[Ronein]

May anyone show another way to do it (Maybe via proc report )?

[Cynthia_sas]

Hi:
PROC REPORT might be able to get you closer. You could take a first pass using TABULATE to get the percents calculated correctly, and then use PROC REPORT. But still the output won't look exactly as you envision.
Cynthia

[Ronein]

May you show the code pls?

[Cynthia_sas]

Hi:

  I would probably use this approach. However, I think that while it comes close, it doesn't look as good as what you get from TABULATE.

Note that because FIELD and CATEGORY moved (or were copied) to the right side of the table, the cell for field cannot span the cells for category as you show. PROC REPORT will not do that.

Cynthia