DATA Step, Macro, Functions and more

Exporting Regression results to excel

Reply
Contributor
Posts: 51

Exporting Regression results to excel

Hi

 

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:

result.png

Thanks,

Niloo

Super User
Posts: 19,815

Re: Exporting Regression results to excel

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.
Contributor
Posts: 51

Re: Exporting Regression results to excel

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)

Super User
Posts: 19,815

Re: Exporting Regression results to excel

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.
Super User
Posts: 11,343

Re: Exporting Regression results to excel

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.

 

Contributor
Posts: 51

Re: Exporting Regression results to excel

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

Super User
Posts: 19,815

Re: Exporting Regression results to excel

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.
Ask a Question
Discussion stats
  • 6 replies
  • 465 views
  • 1 like
  • 3 in conversation