SAS regression result output to excel in one sheet

Question

I want to output my SAS regression result into excel.
The code is:

proc import datafile = 'cmds.csv'
out = Work.cmds
dbms = CSV;
run; 

ODS TAGSETS.EXCELXP 
file="dt.xls";
STYLE = STATISTICAL;

proc sort data=Work.Cmds out=Work.Cmds;
by year;
run;

proc reg data=Work.Cmds outest=want tableout;
by year;
model Investment = Size Growth_New Leverage complex Deficit pc_income_NEW Density/hcc adjrsq ;
ods output parameterestimates=want2;
run;

ODS TAGSETS.EXCELXP CLOSE;

Although it successfully generates the excel file, it contains many sheets. I want to generate all things in one sheet. How can I do?

@Reeza it seems like warning. Just click yes and it opens. But yours are awesome. — Peter Chen, Oct 18 '18 at 15:37
The `style=statistical` option is not in the correct place, that should generate a note/warning in the log and it won't apply it correctly the way you have it specified. — Reeza, Oct 18 '18 at 15:58

Reeza · Accepted Answer · 2018-10-18T16:08:08.960

3

There are options within the tagsets, in specific sheet_interval. To have all go to one page, set the sheet interval option to none.

ODS TAGSETS.EXCELXP file="dt.xls" STYLE = STATISTICAL options (sheet_interval='none');

However, TAGSETS.EXCELXP generates an XML file, not an Excel file. If you have SAS 9.4 TS1M4+ then I would recommend ODS EXCEL instead.

ods excel file="dt.xlsx" style=statistical options (sheet_interval = 'none');

List of all options for ODS TAGSETS.EXCELXP is here: https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html

Full example that will generate a single tab:

ods tagsets.excelxp file='C:\_localdata\demo.xls' options(sheet_interval='none');
proc sort data=sashelp.cars out=cars;
by origin;
run;

proc reg data=cars outest=demo tableout;
by origin;
model mpg_city = mpg_highway invoice cylinders;
ods output parameterEstimates=want;
run;

ods tagsets.excelxp close;

edited Oct 18 '18 at 16:08

answered Oct 18 '18 at 15:04

Reeza

20,510
4
21
38

Great. However, it still generates multiple sheets. I guess it is because U model by group `year`. Do you know how to make all into one sheet no matter their `year`. – Peter Chen Oct 18 '18 at 15:33
No it should not. please modify your question to include your full code. – Reeza Oct 18 '18 at 15:59
Also, check your version of Tagsets.excelxp, it prints in the log when you run the code. It should be: v1.131 2015. – Reeza Oct 18 '18 at 16:07
I solved the problem. I type `;` at the wrong position. Thank you. – Peter Chen Oct 18 '18 at 17:15

SAS regression result output to excel in one sheet

1 Answers1