DATA Step, Macro, Functions and more

How to stack summary table

Reply
Contributor
Posts: 29

How to stack summary table

[ Edited ]

Hi I come across a task that requires the final output of the data to be one big summary table (all the rows are categorical variables, want to see the distribution break down; and the columns reflect different population group, I want to see the count), it looks like: 

 

If there is a missing variable, report missing as well.

 

 Patients who got vaccine Patients who didn't get vaccine 
    
 NumberNumber
Gender    
Male    
Female    
Race    
American Indian    
Asian    
Black    
White    
Multi-Race    
NA    
Ethinicity    
Hispanic    
Non-Hispanic    
Age    
18-39    
40-64    
65+    

 

The input data looks like:

patient_IDRaceGenderEthnicityAge GroupFollowup_FlagVaccine_Flag
1American IndianFHispanic18-3911
2AsianMNon-Hispanic18-3900
3BlackFNA40-6401
4White Hispanic65+11
5Multi-RaceFNon-Hispanic18-3901
6NAMHispanic18-3911
7American IndianFNon-Hispanic40-6500
8AsianF 65+10
9BlackMNon-Hispanic18-3900
10American IndianFNA18-3901
11AsianMHispanic40-6611
12BlackFNon-Hispanic65+01

 

 

The methods I know, proc report, proc mean, etc, only give me summary statistics for one category at a time, and I am not interested in how different categories intersects with each other, don't need something like Race*Age. The real data I work with have way more different population categories like the two big columns, but the rows that need to be reported are the same across population groups.

 

I am wondering if there is any way to systematically 'stack' summary statistics of each row category, so that I won't need to stack them by hand for my real data (10 row categories, 20 column population categories).

 

Any suggestion is greatly appreciated.

 

Thank you!

Trusted Advisor
Posts: 1,586

Re: How to stack summary table

Posted in reply to LisaYIN9309

Try using PROC TABULATE.

If you have difficulties with it - please:

1) post your data in a input datalines format

2) post the code you tried (and log if needed) to point where is your issue.

Trusted Advisor
Posts: 1,934

Re: How to stack summary table

Posted in reply to LisaYIN9309

PROC REPORT will do this.

 

Better yet is to do the calculations in PROC SUMMARY and then have PROC REPORT make the final report in the format you want.

Super User
Posts: 19,878

Re: How to stack summary table

Posted in reply to LisaYIN9309

I have a macro that does some of this. You can find it here. You'll likely need to customize your output further to get exactly what you need.

 

https://gist.github.com/statgeek/b308ac2cfc9b4db0ee3d793567627af0

Super User
Posts: 11,343

Re: How to stack summary table

Posted in reply to LisaYIN9309

Please describe your problem in terms of your variables. Your output has two column headings but it is not clear what role the variable followup_flag may play, or which "%" you want: row, column or table.

 

If you want the two levels of Vaccine_flag to generate the output, with 1=got vaccine and 2= didn't then something like this may be a start:

 

proc format library=work;
format vacc
1="Patients who got vaccine"
0="Patients who didn't get vaccine"
;
run;
proc tabulate data=have;
   class gender race ethnicity age;
   class  vaccine_flag /descending;
   format vaccine_flag vacc.;
   table gender race ethnicity age,
         vaccine_flag=''*(n='Number' RowPctN='%')
         /misstext=' '; 
run;
Contributor
Posts: 29

Re: How to stack summary table

Thank you @ballardw, I should have specified, the top column heading was supposed to indicate which group of people, you got it right. When I copied my table in, this posting section don't allow me to merge the two cells. Sorry about the confusion.
Ask a Question
Discussion stats
  • 5 replies
  • 128 views
  • 2 likes
  • 5 in conversation