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!
1. Aggregate numbers (Enrollment, VaxA/B) by School type
2. Calculate percentages.
3. Transpose to desired structure.
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;
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.
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.