topic Re: Exporting Regression results to excel in SAS Programming

Exporting Regression results to excel

niloo — Wed, 16 Dec 2015 22:51:24 GMT

I am trying to export my regression results to an excel file after running all the regressions. I need all the results to be on a single sheet but the ODS statement that I have sends each table to a seperate sheet:

data a;
set ev.final2;

if amval4wp ne 0 and amval4wp ne . then
ratio=tmval4wp/amval4wp;

lnTEq=log(EqValAn);
lnTEn=log(EnValAn);
lnTMV=log(TMVal4wp);
lnAMV=log(amval4wp);
lnSIZE=log(HOSTATASS);
year = year(Andate);
Industry= TMaCode;
run;

ODS TAGSETS.EXCELXP
file='C:\Users\esy\Desktop\niloofar\DePaul Research\M&AJAPAN\Majority\Results\regression.xls'
STYLE=minimal
OPTIONS ( Orientation = 'landscape'
FitToPage = 'yes'
Pages_FitWidth = '1'
Pages_FitHeight = '100' );

proc glm data=a;
class ConsiStr type year Industry;
model Acar1 = year / noint;
QUIT;
run;
proc glm data=a;
class ConsiStr type year Industry;
model Acar1 = ratio / noint;
QUIT;
run;
proc glm data=a;
class ConsiStr type year Industry;
model Acar1 = TobinQ/ noint;
QUIT;
run;
...(more proc glm statements)
proc glm data=a;
class ConsiStr type year Industry;
model Acar1 = ADebtratio ratio / noint;
QUIT;
run;

I need the exported excel to look like this:

Thanks,

Niloo

Re: Exporting Regression results to excel

Reeza — Wed, 16 Dec 2015 22:57:31 GMT

That doesn't make sense to me...I would expect to see the h/v/c/ps variables in single column and all values in the other columns. For a specific request like this you'll have to create the output data set yourself.

Re: Exporting Regression results to excel

niloo — Wed, 16 Dec 2015 23:04:36 GMT

If I want the results to be shown in the order you mentioned "I would expect to see the h/v/c/ps variables in single column and all values in the other columns", what should I do?(all in one sheet, my statements send each table to a different sheet)

Re: Exporting Regression results to excel

Reeza — Wed, 16 Dec 2015 23:11:03 GMT

Capture the table using ODS output statements and append them using a model.
Or change my data structure so I can do all my regressions at once using a BY variable.

In this case you would transpose all your variables into one variable and create a variable name field. Then run the regression with a BY statement. This works well if all predictors are numeric. If they're mixed (categorical/numeric) it won't work.

Re: Exporting Regression results to excel

ballardw — Wed, 16 Dec 2015 23:28:00 GMT

This might help:

ODS TAGSETS.EXCELXP
file='C:\Users\esy\Desktop\niloofar\DePaul Research\M&AJAPAN\Majority\Results\regression.xls'
STYLE=minimal
OPTIONS ( Orientation = 'landscape'
FitToPage = 'yes'
Pages_FitWidth = '1'
Pages_FitHeight = '100' sheet_interval='NONE' );

though the alignment won't be as you specified. The default for sheet_interval is 'Table' which would place table on a separate sheet.

Re: Exporting Regression results to excel

niloo — Thu, 17 Dec 2015 04:29:55 GMT

It only worked the first time I ran it but after that it gave me this error: error:

Re: Exporting Regression results to excel

Reeza — Thu, 17 Dec 2015 05:14:49 GMT

Tagsets generates an XML file not a native XLSX file. There's usually another bug in the code when you get that error. Either the file wasn't closed or an incorrect option was used.