how to calculate percentage over total in Proc report

[NazaninSAS]

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%

[PaigeMiller]

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

Rate= PRI/PRI.sum;

[NazaninSAS]

unfortunately, it doesn't work.

Thanks,

Nazanin

[PaigeMiller]

Why not?

[NazaninSAS]

it doesn't accept sum

[PaigeMiller]

How about

Rate= PRI/PRI.n;

[NazaninSAS]

it brings empty results

[PaigeMiller]

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.