BookmarkSubscribeRSS Feed
jcis7
Pyrite | Level 9
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!

5 REPLIES 5
ChrisNZ
Tourmaline | Level 20

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

 

jcis7
Pyrite | Level 9

Yes!  How do you export the output into Excel to look like the HTML?  Thank you!

ChrisNZ
Tourmaline | Level 20

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;

 

 

jcis7
Pyrite | Level 9

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.

 

ArtC
Rhodochrosite | Level 12

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1784 views
  • 1 like
  • 3 in conversation