BookmarkSubscribeRSS Feed
jcis7
Pyrite | Level 9
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
Reeza
Super User

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

2. Calculate percentages. 

3. Transpose to desired structure. 

Ksharp
Super User

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;
jcis7
Pyrite | Level 9
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.

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 593 views
  • 0 likes
  • 3 in conversation