export SAS output to excel in column and rows

Reply
Contributor
Posts: 66

export SAS output to excel in column and rows

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

Super User
Posts: 19,038

Re: export SAS output to excel in column and rows

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. 

Contributor
Posts: 66

Re: export SAS output to excel in column and rows

Hi Reeza,

I am using SAS 9.4 windows version.

Hasan

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Contributor
Posts: 66

Re: export SAS output to excel in column and rows

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

Super User
Posts: 19,038

Re: export SAS output to excel in column and rows

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. 

 

 

Contributor
Posts: 66

Re: export SAS output to excel in column and rows

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;

Super User
Posts: 19,038

Re: export SAS output to excel in column and rows

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.

Contributor
Posts: 66

Re: export SAS output to excel in column and rows

My SAS software information:

 

Capture.PNG

Contributor
Posts: 66

Re: export SAS output to excel in column and rows

Dear Reeza, ODS option is not working.

Super User
Posts: 19,038

Re: export SAS output to excel in column and rows


nazmul wrote:

Dear Reeza, ODS option is not working.


I don't know what that means. 

Option 2 is listed above. 

Contributor
Posts: 66

Re: export SAS output to excel in column and rows

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 
 
Super User
Posts: 19,038

Re: export SAS output to excel in column and rows

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;

Contributor
Posts: 66

Re: export SAS output to excel in column and rows

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

SAS Super FREQ
Posts: 8,815

Re: export SAS output to excel in column and rows

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
Contributor
Posts: 66

Re: export SAS output to excel in column and rows

Dear Cynthia,

 

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

Ask a Question
Discussion stats
  • 20 replies
  • 231 views
  • 2 likes
  • 5 in conversation