topic Re: summarizing data in SAS Procedures

summarizing data

jcis7 — Tue, 02 Jun 2015 20:51:12 GMT

Hello - appreciate any help you can give!

I'm trying to summarize data and put it into one table.

The original layout of the data is:

facilityname facilitytype enrollment utd_vac1 utd_vac2 utd_vac3

busybeez pub 100 88 90 50

peter pan priv 23 22 11 23

Desired final layout is in percentage using total enrollment for the denominator to determine percentage

All Pub Priv

utd_vac1 89.4 88.0 22.0

utd_vac2 82.1 90.0 11.0

utd_vac3 59.3 50.0 23.0

I figured out I can use proc summary:

proc sort data=table1;

by facilitytype;

run;

*--to get summary stats by type;

proc summary data=table1 (rename= (col1=private col1=public);

by facilitytype;

var enrollment utd_var1 utd_var2 utd_var3;

output out=table1sum (drop=_type_ _freq_) sum=utd_var1 utd_var2 utd_var3;

run;

*--to get summary stats for all;

proc summary data=table2 (rename= (col1=private col1=public);

var enrollment utd_var1 utd_var2 utd_var3;

output out=table2sum (drop=_type_ _freq_) sum=utd_var1 utd_var2 utd_var3;

run;

*--transpose table1sum;

proc transpose data=table1sum output out=tablesumt (rename=(col1=public col2=private0);

run;

*--transpose table2sum;

proc transpose data=table2sum output out=table2sum5 (rename = (col1=public col2=private));

run;

Then, I could divide the number enrollment to get the percentages. Then, somehow append them into one table using proc append?

But, is there a simpler way?

Appreciate any help you can give - Thanks so much!

Re: summarizing data

Reeza — Wed, 03 Jun 2015 00:02:42 GMT

Do you have multiple facilities in your table (facility name) or only one facility? Is the sample data the full table or only a portion?

Re: summarizing data

ballardw — Wed, 03 Jun 2015 15:31:48 GMT

Might be as easy as:

proc tabulate data=table1;

class facilitytype;

var enrollment utd_vac1 utd_vac2 utd_vac3;

table (utd_vac1 utd_vac2 utd_vac3)* pctsum<enrollment> ,

all facilitytype;

run;

I'm sure there is also a way with proc report but I use tabulate more.

NOTE: Your use of proc summary was inefficient for this purpose:

proc summary data=table1 (rename= (col1=private col1=public);

CLASS facilitytype;

var enrollment utd_var1 utd_var2 utd_var3;

output out=table1sum (drop= _freq_) sum=utd_var1 utd_var2 utd_var3;

run;

The _type_ variable value of 0 includes the equivalent of your second proc summary and _type_=1 would be the output of the first

Re: summarizing data

jcis7 — Thu, 11 Jun 2015 22:23:02 GMT

The sample data is only a portion. There are thousands of facilities. thanks!

Re: summarizing data

Reeza — Fri, 12 Jun 2015 03:28:21 GMT

How does the facility play into the final table then?