BookmarkSubscribeRSS Feed
niloo
Obsidian | Level 7

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

6 REPLIES 6
Reeza
Super User
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
Obsidian | Level 7

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
Super User
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
Super User

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
Obsidian | Level 7

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

Reeza
Super User
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.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 4498 views
  • 1 like
  • 3 in conversation