I have the following data | |||||||||
county | school | enrollment | vax1 | vax2 | schooltype | ||||
countyA | little tots | 50 | 48 | 45 | private | ||||
countyA | happy days | 100 | 88 | 77 | public | ||||
countyA | playtime | 25 | 22 | 23 | private | ||||
countyB | busy beez | 23 | 22 | 21 | public | ||||
countyB | child time | 27 | 25 | 25 | public | ||||
We'd like to aggregate by county and by type, like the output below? | |||||||||
vax1 | |||||||||
all | public | private | |||||||
county | enrollment | number | percent | enrollment | number | percent | enrollment | number | percent |
countyA | 175 | 158 | 90.3% | 100 | 88 | 0.88 | 75 | 70 | 93.3% |
countyB | 50 | 47 | 94.0% | 50 | 47 | 0.94 | 0 | 0 | 0 |
Thanks!
Like this?
data HAVE;
input COUNTY $ SCHOOL $ ENROLLMENT VAX1 VAX2 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;
option missing='0';
proc tabulate;
class COUNTY SCHOOLTYPE;
var VAX1 ENROLLMENT;
table COUNTY=''
, (all SCHOOLTYPE='') *(ENROLLMENT=''*sum='Enrolment' *f=comma8.0
VAX1='' *(sum ='Vax1' *f=comma8.0
pctsum<ENROLLMENT>='%'))
/box='County';
run;
County | All | private | public | ||||||
---|---|---|---|---|---|---|---|---|---|
Enrolment | Vax1 | % | Enrolment | Vax1 | % | Enrolment | Vax1 | % | |
countyA | 175 | 158 | 90.29 | 75 | 70 | 93.33 | 100 | 88 | 88.00 |
countyB | 50 | 47 | 94.00 | 0 | 0 | 0 | 50 | 47 | 94.00 |
Yes! How do you export the output into Excel to look like the HTML? Thank you!
Crude but works:
ods html3 style=seaside file="%sysfunc(pathname(work))\t.xls";
option missing='0';
proc tabulate;
class COUNTY SCHOOLTYPE;
var VAX1 ENROLLMENT;
table COUNTY=''
, (all SCHOOLTYPE='') *(ENROLLMENT=''*sum='Enrolment' *f=comma8.0
VAX1='' *(sum ='Vax1' *f=comma8.0
pctsum<ENROLLMENT>='%'))
/box='County';
run;
ods html3 close;
Thanks. What if it's a network drive such as L:\ ?
I'm getting an error message:
954 ods html3 style=seaside file="%sysfunc(L:\(work))\t.xls";
ERROR: Expected open parenthesis after macro function name not found.
NOTE: Writing HTML3 Body file: \(work))\t.xls
ERROR: Physical file does not exist, C:\(work))\t.xls.
ERROR: No body file. HTML3 output will not be created.
The %SYSFUNC is a macro function that allows you to execute a DATA step function. The PATHNAME function returns the physical location of a libref. In the code by @ChrisNZ, the physical location of the WORK directory is inserted into the path for the ODS file=. If you know the path write it directly without the %SYSFUNC.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.