topic Re: how to calculate percentage over total in Proc report in SAS Programming

how to calculate percentage over total in Proc report

NazaninSAS — Wed, 19 Feb 2020 15:59:28 GMT

Hi,

This is my code:

proc report nowd data=work.Elig;

title "CRA Eligibility to Retire4";

columns Retfy1 PRI rate;

define Retfy1 / group 'Eligibility to Retire';

define PRI/ analysis n format=comma8. "# of Employees " center;

define rate / computed format=percent8.1 "Rate(%)" center;

compute Rate;

Rate= PRI.n/PRI.n;

endcomp;

rbreak after / dol skip summarize style=Header{fontweight=bold} ;

compute after ;

Retfy1="Total";

endcomp;

footnote j=1 "Source: CAS";

run;

I would like to be able to calculate the rate over the total here:

I don't want to have 100% as shown on the table, however I want to be able to calculate the percentage of each row over the total.

Eligibility to Retire	# of Employees	Rate(%)
2019-20	1,247	=1247/ 34458
2020-21	1,428	100.00%
2021-22	1,367	100.00%
2022-23	1,290	100.00%
2023-24	1,262	100.00%
2024-25	1,046	100.00%
2025-26	931	100.00%
2026-27	936	100.00%
2027-28	1,161	100.00%
2028-29	1,053	100.00%
2029-30	1,141	100.00%
20xx	17,870	100.00%
Already Eligible	3,726	100.00%
Total	34,458	100.00%

Re: how to calculate percentage over total in Proc report

PaigeMiller — Wed, 19 Feb 2020 16:17:09 GMT

I don't have your data, so I can't test this, but the formula for rate should be

Rate= PRI/PRI.sum;

Re: how to calculate percentage over total in Proc report

NazaninSAS — Wed, 19 Feb 2020 16:26:34 GMT

unfortunately, it doesn't work.

Thanks,

Nazanin

Re: how to calculate percentage over total in Proc report

PaigeMiller — Wed, 19 Feb 2020 16:30:36 GMT

Why not?

Re: how to calculate percentage over total in Proc report

NazaninSAS — Wed, 19 Feb 2020 16:31:56 GMT

it doesn't accept sum

Re: how to calculate percentage over total in Proc report

PaigeMiller — Wed, 19 Feb 2020 16:34:39 GMT

How about

Rate= PRI/PRI.n;

Re: how to calculate percentage over total in Proc report

NazaninSAS — Wed, 19 Feb 2020 16:39:02 GMT

it brings empty results

Re: how to calculate percentage over total in Proc report

PaigeMiller — Thu, 20 Feb 2020 14:07:10 GMT

While I'm sure it is possible to do what you want enitrely in PROC REPORT, I find myself much more comfortable using DATA step and other PROCs (such as PROC SUMMARY and PROC FREQ) in doing these calculations. Then once you have computed the proper values of N and the percents, I would use PROC REPORT to produce the final output table.