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!
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?
The sample data is only a portion. There are thousands of facilities. thanks!
How does the facility play into the final table then?
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.