Pyrite | Level 9

## Percentages by type

```data HAVE;
input COUNTY \$ SCHOOL \$ 	ENROLLMENT 	VAXA 	VAXB 	SCHOOLTYPE \$ ;
cards;
countyA 	littlet 	50 	48 	45 	private
countyA 	happyda 	100 	88 	77 	public
countyA 	playtim 	25 	22 	23 	private
countyB 	busybee 	23 	22 	21 	public
countyB 	childti 	27 	25 	25 	public
run;```

Hi,I have the data above and ultimately need the following calculations to get the final output:

Calculations:

All                                                                                                  Public                                                                                                      Private

vax 1  ((SUM OF ENROLLMENT- sum of vaxA)/sum of enrollment)*100      (( SUM OF ENROLLMENT FOR PUBLIC ONLY-sum of vaxA for public only)/sum of enrollment for public only)*100          ((SUM OF ENROLLMENT FOR PRIVATE ONLY-sum of vaxA for private only)/sum of enrollment for private only)*100

vax 2   ((SUM OF ENROLLMENT - sum of vaxB)/sum of enrollment)*100       ((SUM OF ENROLLMENT FOR PUBLIC ONLY - sum of vaxB for public only)/sum of enrollment for public only)*100          (SUM OF ENROLLMENT FOR PRIVATE ONLY - sum of vaxB for private only)/sum of enrollment for private only)*100

Final Output:

All       Public       Private

vaxA  9.0%   10.0%      7.0%

vaxB  15.0%   18.0%      9.0%

Thank you!

3 REPLIES 3
Super User

## Re: Percentages by type

1. Aggregate numbers (Enrollment, VaxA/B) by School type

2. Calculate percentages.

3. Transpose to desired structure.

Super User

## Re: Percentages by type

It is very convenient for SQL.

``````data HAVE;
input COUNTY \$ SCHOOL \$ 	ENROLLMENT 	VAXA 	VAXB 	SCHOOLTYPE \$ ;
cards;
countyA 	littlet 	50 	48 	45 	private
countyA 	happyda 	100 	88 	77 	public
countyA 	playtim 	25 	22 	23 	private
countyB 	busybee 	23 	22 	21 	public
countyB 	childti 	27 	25 	25 	public
;
run;
proc sql;
select 'vaxA' label='#',
(select sum(vaxA)/sum(ENROLLMENT) from have) as All format=percent7.2,
(select sum(vaxA)/sum(ENROLLMENT) from have where SCHOOLTYPE='public') as public format=percent7.2,
(select sum(vaxA)/sum(ENROLLMENT) from have where SCHOOLTYPE='private') as private format=percent7.2
from have(obs=1)
union
select 'vaxB' label='#',
(select sum(vaxB)/sum(ENROLLMENT) from have) as All format=percent7.2,
(select sum(vaxB)/sum(ENROLLMENT) from have where SCHOOLTYPE='public') as public format=percent7.2,
(select sum(vaxB)/sum(ENROLLMENT) from have where SCHOOLTYPE='private') as private format=percent7.2
from have(obs=1)
;
quit;``````
Pyrite | Level 9

## Re: Percentages by type

Appreciate your help everyone. I tried the code and realized I'd forgotten(!!) an important step which is summing the enrollment and then subtracting for example the sum of vaxA, etc. I edited the original post and used CAPS to indicate which portions I edited. Forgive my oversight. Thank you for your help.
Discussion stats
• 3 replies
• 803 views
• 0 likes
• 3 in conversation