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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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