proc report and group percents

[eawh100]

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%

.......

.......

[PaigeMiller]

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.

[eawh100]

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?

[PaigeMiller]

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

[Cynthia_sas · Accepted solution]

@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