Help using Base SAS procedures

Aggregate data by type

Reply
Regular Contributor
Posts: 192

Aggregate data by type

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!

PROC Star
Posts: 1,566

Re: Aggregate data by type

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

 

Regular Contributor
Posts: 192

Re: Aggregate data by type

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

PROC Star
Posts: 1,566

Re: Aggregate data by type

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;

 

 

Regular Contributor
Posts: 192

Re: Aggregate data by type

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.

 

Valued Guide
Posts: 632

Re: Aggregate data by type

[ Edited ]

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.

Ask a Question
Discussion stats
  • 5 replies
  • 189 views
  • 1 like
  • 3 in conversation