topic Re: Aggregate data by type in SAS Procedures

Aggregate data by type

jcis7 — Wed, 01 Mar 2017 00:11:00 GMT

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!

Re: Aggregate data by type

ChrisNZ — Wed, 01 Mar 2017 01:47:37 GMT

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
County	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

Re: Aggregate data by type

jcis7 — Thu, 02 Mar 2017 20:01:13 GMT

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

Re: Aggregate data by type

ChrisNZ — Fri, 03 Mar 2017 00:52:40 GMT

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;

Re: Aggregate data by type

jcis7 — Mon, 01 May 2017 19:41:07 GMT

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.

Re: Aggregate data by type

ArtC — Tue, 02 May 2017 06:05:27 GMT

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.