BookmarkSubscribeRSS Feed
nazmul
Quartz | Level 8

Hello everyone,

 

I have the following regression output in the SAS output. I want to export the output to excel in column and rows. I have attached the SAS output in the attached file.

 

I used the followinf code to export the file:

 

ODS TAGSETS.EXCELXP
file='F:worksas9regression.xls'
STYLE=minimal
OPTIONS ( Orientation = 'landscape'
FitToPage = 'yes'
Pages_FitWidth = '1'
Pages_FitHeight = '100' );

 

 

sas regression code

 

 

ods tagsets.excelxp close;

 

But I am not getting any useable excel data. Could you please give me the code to transfer the output to excel? I will really appreciate your help.

 

Capture.PNG

20 REPLIES 20
Reeza
Super User

What are you defining as usable Excel data? 

What version of SAS are you using? If it's the latest ODS EXCEL may perform better.  But without knowing what you want that's impossible to answer. And what proc are you using, we can't test anything without more information. 

 

You may may have to preformat your data but let's check the other options first. 

nazmul
Quartz | Level 8
Hi Reeza,

I am using SAS 9.4 windows version.

Hasan

##- Please type your reply above this line. Simple formatting, no
attachments. -##
nazmul
Quartz | Level 8

Hi Rezza,

 

I am using Eventus macro in sas to do event study from WRDS.

 

My actual code:

 

rsubmit;

Eventus getdata;
Request insas=pcwork.weqdata1 datefmt=crsp autodate AllowMissingDate
nodividx shift1=-31 ndays=1 SP500;
Returns FAMAFRENCH outsas=pcwork.study1;

endrsubmit;

 

eventus;

return

Reeza
Super User

Assuming 9.4 TS1M3+ (you can check your version) try ODS Excel. 

If that doesn't work, post better sample code/data and illustrate what doesn't work for you. 

 

 

nazmul
Quartz | Level 8

I used the following code to perform fama frech 4 factor regression analysis in SAS. I am trying to export the SAS regression output to excel. I want the name in one column, regression coefficient in another column, t statistics in another column and all the independet variables in different rows. Using ODS code, I am getting all the data in a singel cell of excel. But you know, having all data in a single cell of excel is of no use. My output excel file with ODS code looks like the following

 

 

Capture.PNG

 

ODS TAGSETS.EXCELXP
file='F:worksas9regression.xls'
STYLE=minimal
OPTIONS ( Orientation = 'landscape'
FitToPage = 'yes'
Pages_FitWidth = '1'
Pages_FitHeight = '100' );

 

 

rsubmit
inheritlib=(work=pcwork); * giving wrds access to work folder;
Eventus Monthly;
Request insas=pcwork.HAVE CusiPerm ID=id IDFmt=4.;
EvtStudy pre=0 post=12 EQUAL FamaFrench MOMENTUM CTPR outsas=pcwork.study1;
endrsubmit;

 

 

ods tagsets.excelxp close;

Reeza
Super User

That's not ODS Excel, that's TAGSETS. Try ODS Excel. 

 

Otherwise, yes, you'll have to capture the ODS table and then use PROC PRINT to display that.

http://blogs.sas.com/content/iml/2017/01/09/ods-output-any-statistic.html

 

Given that it's a WRDS and a custom macro I'm not sure how much of above will apply, but I (and most others) can't test anything either.

nazmul
Quartz | Level 8

My SAS software information:

 

Capture.PNG

nazmul
Quartz | Level 8

Dear Reeza, ODS option is not working.

Reeza
Super User

@nazmul wrote:

Dear Reeza, ODS option is not working.


I don't know what that means. 

Option 2 is listed above. 

nazmul
Quartz | Level 8
Dear Reeza
 
I am sorry for being late in responding to your comment.I responded to your comment with my email. But I do not know why is is not showing up here.  Actually I applied all of your suggested methods. But I could not transfer the Sas output to excel. I think my problem is not a SAS problem. it may be a Eventus issue. So I have contacted with them. They could not give me a solution yet.
 
Thank you for your help. You helped me several times so far. I am glad to have u in this platform 
 
Reeza
Super User

I suspect there's an easy answer to this. Take a look at the macro docs or your work library. There may tables in the library with the data you need since they had to calculate it. It may need an option in the macro to keep the data. 

 

Or you could try ODS CSV. 

 

oDS CSV file='myoutput.csv';

 

code for eventus macro;

 

ods csv close;

nazmul
Quartz | Level 8

Dear Reeza,

 

I talked to a representation of Eventus. She confirmed me that currently I cannot get eventus regression output in excel. They will employ a macro this summer to do the job.

 

I also tried ODS CSV but could not generate csv file.

 

Thank you for your help.

 

Hasan

Cynthia_sas
SAS Super FREQ
Hi:
Also checking whether this is a typo:
file='F:worksas9regression.xls'
I would expect to see a slash \ in the PATH name:
file='F:\worksas9regression.xls'

cynthia
nazmul
Quartz | Level 8

Dear Cynthia,

 

I put slash last time. But did not work. Thank you for being helpful.

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
  • 20 replies
  • 1605 views
  • 2 likes
  • 5 in conversation