Search code examples
sassas-ods

SAS regression result output to excel in one sheet

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?

Solution

  • 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;