Help using Base SAS procedures

Percentages by type

Reply
Regular Contributor
Posts: 192

Percentages by type

[ Edited ]
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!

Super User
Posts: 17,750

Re: Percentages by type

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

2. Calculate percentages. 

3. Transpose to desired structure. 

Super User
Posts: 9,671

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;
Regular Contributor
Posts: 192

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.
Ask a Question
Discussion stats
  • 3 replies
  • 131 views
  • 0 likes
  • 3 in conversation