summarizing data

[jcis7]

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!

[Reeza]

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?

[jcis7]

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

[Reeza]

How does the facility play into the final table then?

[ballardw]

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