topic proc tabulate -percent from total column per each group in SAS Programming

proc tabulate -percent from total column per each group

Ronein — Tue, 09 Mar 2021 12:43:07 GMT

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;

Re: proc tabulate -percent from total column per each group

ballardw — Tue, 09 Mar 2021 13:11:53 GMT

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

Re: proc tabulate -percent from total column per each group

Cynthia_sas — Tue, 09 Mar 2021 15:50:42 GMT

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

Re: proc tabulate -percent from total column per each group

Ronein — Tue, 09 Mar 2021 20:12:31 GMT

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

Re: proc tabulate -percent from total column per each group

Cynthia_sas — Wed, 10 Mar 2021 02:53:20 GMT

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

Re: proc tabulate -percent from total column per each group

Ronein — Wed, 10 Mar 2021 06:00:09 GMT

May you show the code pls?

Re: proc tabulate -percent from total column per each group

Cynthia_sas — Wed, 10 Mar 2021 14:13:58 GMT

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