topic Re: How to export SAS output into Excel in SAS Programming

How to export SAS output into Excel

lalohg — Sat, 30 May 2020 22:04:49 GMT

Hi there,

I ran the following proc freq in SAS and got the SAS output as shown in the attached Word.doc

proc freq data=test;
tables (age_group gender)*disease/chisq norow nopercent relrisk;
Title 'How to export SAS output into Excel';
run;

I would like to get the results directly from SAS to Excel on a table as shown in the attached Excel file.

Is there a way to modify the proc freq in SAS to get the results as shown in the attached Excel file?

I have several variables beside age and gender and am I tiered of copy and paste.

it would be great if all the formating in the table of the attached Excel file can be done in SAS but if it is not possible getting the numbers and name of variables as shown would be great

all your help will be appreciate it

Thanks

Re: How to export SAS output into Excel

ghosh — Sat, 30 May 2020 23:09:12 GMT

I don't think anyone will open your attached files, however you can put your code where I have indicated

FILENAME out '~/folder/file.xlsx';
ods listing close;
ods excel file=out  options(sheet_name='#byval1');

*your code goes here;
 
ods excel close;
ods listing;

Re: How to export SAS output into Excel

Reeza — Sun, 31 May 2020 00:37:41 GMT

Proc freq doesn't go as nicely as you'd like to Excel, PROC TABULATE will give you more control.

To get any output in Excel, wrap it with ODS EXCEL before and after and it will pipe all the results to Excel. You can control what sheets things go to as well as the sheet names.

ods excel file='/folders/myfolders/demo.xlsx' style=meadow options (sheet_interval='proc');

proc freq data = sashelp.class;
table age*sex / nopercent norow nocol;
run;

ods excel close;

Re: How to export SAS output into Excel

ballardw — Sun, 31 May 2020 18:00:49 GMT

Since you asking to put Chi-square p-values and relative risk information on the same row as other values you aren't going to get proc freq to do that.

Or without significant work, any other procedure. You would generate the output into two or more data sets, combine some how and then display.

Re: How to export SAS output into Excel

lalohg — Sun, 31 May 2020 18:07:26 GMT

Hi ballardw,
Thanks for your email.
Is there a way to get the column percentage in a New column next to The
frequency in Excel after I get rid of the relative risk and chi-square
option in proc freq?

Thanks

Re: How to export SAS output into Excel

Reeza — Sun, 31 May 2020 19:40:16 GMT

I would suggest using the ODS output tables instead that's how they're outputted.

See an example here. Use PROC PRINT to display the final table only.
https://gist.github.com/statgeek/e0903d269d4a71316a4e