topic Re: Whats the easiest way to calculate percentages within a group in SAS Programming

Whats the easiest way to calculate percentages within a group

sachin01663 — Wed, 13 Jun 2012 23:34:17 GMT

For example I have:

Country	Town	Population	Percentage_of_population_each_town
UK	A	10
UK	A	20
UK	B	20
US	C	10
US	C	20
US	D	5
Greece	E	20
Greece	K	10

I want to calculate last column 'Percentage of population each group" - which is population of a town as a percentage of a total population of that country.

I want this:

Country	Town	Population	Percentage_of_population_each_town
UK	A	30	60%
UK	B	20	40%
Total		50
US	C	30	85.70%
US	D	5	14.30%
35		35
Greece	E	20	66%
Greece	K	10	33%

Re: Whats the easiest way to calculate percentages within a group

PGStats — Thu, 14 Jun 2012 00:49:19 GMT

Simple query (with remerging) :

data have;
length country town $12;
input country town population;
datalines;
UK A 10
UK A 20
UK B 20
US C 10
US C 20
US D 5
Greece E 20
Greece K 10
;

proc sql;
create table want as

select country, town, population, population/sum(population) as pctPop format=percent8.1

from (select country, town, sum(population) as population from have group by country, town)

group by country;

select * from want;

quit;

Corrected by PG to account for multiple town records.

Re: Whats the easiest way to calculate percentages within a group

Linlin — Thu, 14 Jun 2012 01:44:14 GMT

not as short as PG's code:

data have;

input Country $ Town $ Population;

cards;

UK A 10

UK A 20

UK B 20

US C 10

US C 20

US D 5

Greece E 20

Greece K 10

proc sql;

create table temp1 as

select country,sum(population) as t

from have

group by country;

create table temp2 as select country,town,sum(population) as population

from have

group by country,town;

create table want as

select a.country,town,population,population/t as percentage format=percent8.2

from temp2 as a,temp1

where a.country=temp1.country;

quit;

proc print;run;

Linlin

Re: Whats the easiest way to calculate percentages within a group

MikeZdeb — Thu, 14 Jun 2012 02:06:30 GMT

hi ... another idea (as long as all you want are totals and percentages, not output that looks exactly like what you posted) ...

assuming that the country data are grouped already in the data set ...

proc freq data=have;

table town;

by country notsorted;

weight population;

run;

country=UK

Cumulative Cumulative

town Frequency Percent Frequency Percent

A 30 60.00 30 60.00

B 20 40.00 50 100.00

country=US

Cumulative Cumulative

town Frequency Percent Frequency Percent

C 30 85.71 30 85.71

D 5 14.29 35 100.00

country=Greece

Cumulative Cumulative

town Frequency Percent Frequency Percent

E 20 66.67 20 66.67

K 10 33.33 30 100.00

Re: Whats the easiest way to calculate percentages within a group

PGStats — Thu, 14 Jun 2012 02:19:27 GMT

Or,

proc freq data=have noprint;
by country notsorted;
table town / sparse OUT=WANT;
weight population;
run;

proc print; run;

Re: Whats the easiest way to calculate percentages within a group

Alpay — Thu, 14 Jun 2012 04:49:56 GMT

If it's a report output you want you can utilize proc report.

data x;

input Country $ Town $ Population;

datalines;

UK A 10

UK A 20

UK B 20

US C 10

US C 20

US D 5

Greece E 20

Greece K 10

;

run;

proc format;

value newpctfmt . = ' '

other = [percent9.2];

run;

proc report data=x nowd missing out=y;

column Country Town Population Pct;

define Country / group order=data ;

define Town / group ;

define Population / analysis;

define Pct / computed format=newpctfmt.;

compute before Country;

town_total = Population.Sum;

endcomp;

Compute Country;

if Country ne '' then hold_Country = Country;

else Country = hold_Country;

endcomp;

compute after Country;

Country = 'Total';

Pct = .;

endcomp;

break after Country / summarize style=[font_style=Roman];

compute Pct;

Pct = Population.Sum / Town_Total;

endcomp;

run;

If needed, one can get this into a table from proc report's output data set y.

data z;

set y(drop=_BREAK_);

by Country notsorted;

if not first.Country or Country = 'Total';

format Pct newpctfmt.;

run;

Re: Whats the easiest way to calculate percentages within a group

Haikuo — Thu, 14 Jun 2012 12:42:24 GMT

Of course Proc SQL, freq or even proc report have the native edge to tackle this problem, while good old data step can still provide an option:

data have;

input country$ town$ population;

cards;

UK A 10

UK A 20

UK B 20

US C 10

US C 20

US D 5

Greece E 20

Greece K 10

;

data want;

do until (last.country);

set have;

by country town notsorted;

_c+population;

end;

do until (last.country);

do until (last.town);

set have;

by country town notsorted;

_t+population;

end;

percent=_t/_c;

output;

call missing(_t);

end;

call missing(_c);

format percent percent 8.2;

drop _c population;

rename _t=population;

run;

proc print;run;

Haikuo