DATA Step, Macro, Functions and more

Output table in excel

Reply
Occasional Contributor
Posts: 16

Output table in excel

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,

Super User
Super User
Posts: 9,599

Re: Output table in excel

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.

 

Occasional Contributor
Posts: 16

Re: Output table in excel

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

Super User
Super User
Posts: 9,599

Re: Output table in excel

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.

Super User
Posts: 13,542

Re: Output table in excel

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

Ask a Question
Discussion stats
  • 4 replies
  • 75 views
  • 0 likes
  • 3 in conversation