BookmarkSubscribeRSS Feed
Tomcaty
Obsidian | Level 7

Hello all,

 

I wanted to generate a table in excel using demographic data. Usually I use either means or frequency procedures to output to excel. But I need an automated program that I can use to generate the similar table format even if the data changes. I have one continuous variable and two categorical variables. For the variable 'Event' I only need the occurrence '1'. I would greatly appreciate if someone would help me with this. 

 

 

data have;
input id sex$ race$ age event;
datalines;
01	F	W	66	0
02	F	B	87	1
03	M	W	63	1
04	M	A	79	0
05	M	W	75	1
06	M	U	72	0
;

 

My output table in excel should have a format as below:

 

DemographicsTotal MalesFemales
 N%N%N%
Age       
Mean (SD)73.7 (8.8) 72.3 (6.8) 76.5 (14.8) 
Median73.5 73.5 76.5 
Range(63-87) (63-79)  (66-87) 
Race      
White350233.3116.7
Black116.700116.7
Asian116.7116.700
Unknown116.7116.700
Event3100233.3116.7

 

 

Thank you,

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Seems to be quite a simple matter of arranging your data, two proc means, then maybe an sql statement.

proc means data=have;
  var age;
  output out=total n=n mean=mean...;
run;
proc means data=have;
  by sex;
  var age;
  output out=byg...;
run;

Just manipulate this into the output format you like ready to be transposed up.

Run a freq or proc sql to get numbers and percentages, and then proc transpose the total rows.

 

Tomcaty
Obsidian | Level 7

Thanks for the quick reply, RW9. But I wanted the table in exact format. I have attached the excel format

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, and this is why I mentioned that you need to use datastep to manipulate this data into the format you want.  There is no magic button to do this for you.  You can get fixed output from the procedures, then manipulate it into the output layout you specifically want - my outputs for instance with that data look quite different from yours, so my data manipulation would be different.

ballardw
Super User

I submit that having a column heading of N and a value in a cell of "73.7 (8.8)" or "(63-87)" as shown above is misleading at best. To fit into a single "cell" the result would have to be character to appear as shown. So you would be mixing a column with numeric and character values.

 

The SAS report procedures will require a column to be all of a single type. Therefore the "easy" approaches such as Proc Tabulate or Report are not going to be available

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 798 views
  • 0 likes
  • 3 in conversation