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;
Accepted Solutions
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
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
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
