topic Re: proc report and group percents in SAS Programming

proc report and group percents

eawh100 — Fri, 04 Oct 2024 13:02:07 GMT

Hello, I need some help with reporting percentages. I have a data set with list of cities and countries and am counting how often a city appears (for example Melbourne, Australia is in 18 rows, per proc report count n).

pctn is calculating percent out of all countries? I think.

1. How would I calculate so each country is 100% and the city count percentages will add to 100% per country. in the snippet below, Austria summary 100%, salzburg 66%/vienna 33%.

2. What if I wanted to calculate percent out of a completely different number? say "each city count"/1000. Can I define my own denominator in a calculate field?

proc report data=city_data;

columns country city (n pctn);

define country /group;

define city /group;

define pctn /format =percent9.1 'Percent';

define n /'count';

rbreak before /summarize;

break before country/summarize style=header;

compute before;
country='All countries';
endcomp;

run;

Countries	Cities	count	Percent
All Countries		199	100.00%
Australia		18	9.00%
Australia	Melbourne	18	9.00%
Austria		3	1.50%
Austria	Vienna	1	0.50%
	Salzberg	2	1.00%
Belize		1	0.50%
Belize	Belmopan	1	0.50%
Bulgaria		3	1.50%
Bulgaria	Sofia	3	1.50%
Brazil		9	4.50%
Brazil	Sao Paolo	2	1.00%
	Rio de Janeiro	4	2.00%
	Brasilia	4	2.00%
	Porto Alegre	1	0.50%

.......

Re: proc report and group percents

PaigeMiller — Fri, 04 Oct 2024 13:21:20 GMT

My answer to both questions is to do the calculations and get them the way you want them to be before using PROC REPORT. For example, using PROC SUMMARY and/or PROC FREQ and/or DATA steps. If you do that, then PROC REPORT is simple to program. I say this because I feel much more comfortable using those tools to program with, rather than the internal tools in PROC REPORT.

Re: proc report and group percents

eawh100 — Fri, 04 Oct 2024 13:43:30 GMT

ok, thanks. I'd just calculate whatever percents I want in a new column in say a data step and then use that new column within proc report?

Re: proc report and group percents

PaigeMiller — Fri, 04 Oct 2024 14:03:43 GMT

Yes, all the info winds up in a data set, so you know you have done the calculations properly, and then that data set is used in PROC REPORT

Re: proc report and group percents

Cynthia_sas — Fri, 04 Oct 2024 14:11:55 GMT

@PaigeMiller -- I agree with you. If someone is not comfortable with PROC REPORT and using helper variables and COMPUTE blocks, then pre-summarizing and computing the percents in a DATA step or switching to PROC TABULATE (which has custom denominators and also the ROWPCT/COLPCT statistics) is a preferred approach.

For the sake of completeness, here's an example of how to do it within PROC REPORT using helper variables. I switched to using SASHELP.SHOES because the OP did not provide data. This report has a percent of region and then a fake percent where the denominator was set to a constant value of 3371.

Ths places where the 2 new percents are calculated are highlighted:

Basically, if you want to calculate percent of region with PROC REPORT, you have to "grab" and hold the count for that region into a helper variable called RCNT, which is automatically retained and then use rcnt in the divide for REGPCT. The calculation for FAKEPCT just uses a numeric constant as the denominator.

Cynthia