Exporting Regression results to excel

[niloo]

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:

Thanks,

Niloo

[Reeza]

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.

[niloo]

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)

[Reeza]

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.

[ballardw]

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.

[niloo]

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

[Reeza]

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.