BookmarkSubscribeRSS Feed
jcis7
Pyrite | Level 9

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!

4 REPLIES 4
Reeza
Super User

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
Pyrite | Level 9

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

Reeza
Super User

How does the facility play into the final table then?

ballardw
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 869 views
  • 0 likes
  • 3 in conversation