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.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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