topic Re: Output table in excel in SAS Programming

Output table in excel

Tomcaty — Thu, 21 Jun 2018 12:31:35 GMT

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:

Demographics	Total	Males	Females
	N	%	N	%	N	%
Age
Mean (SD)	73.7 (8.8)		72.3 (6.8)		76.5 (14.8)
Median	73.5		73.5		76.5
Range	(63-87)		(63-79)		(66-87)
Race
White	3	50	2	33.3	1	16.7
Black	1	16.7	0	0	1	16.7
Asian	1	16.7	1	16.7	0	0
Unknown	1	16.7	1	16.7	0	0
Event	3	100	2	33.3	1	16.7

Thank you,

Re: Output table in excel

RW9 — Thu, 21 Jun 2018 12:43:18 GMT

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.

Re: Output table in excel

Tomcaty — Thu, 21 Jun 2018 13:03:37 GMT

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

Re: Output table in excel

RW9 — Thu, 21 Jun 2018 13:07:01 GMT

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.

Re: Output table in excel

ballardw — Thu, 21 Jun 2018 14:38:37 GMT

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