topic Re: export SAS output to excel in column and rows in SAS Programming

export SAS output to excel in column and rows

nazmul — Wed, 14 Jun 2017 21:35:25 GMT

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.

Re: export SAS output to excel in column and rows

Reeza — Wed, 14 Jun 2017 22:28:35 GMT

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.

Re: export SAS output to excel in column and rows

nazmul — Wed, 14 Jun 2017 22:47:49 GMT

Hi Reeza,

I am using SAS 9.4 windows version.

Hasan

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Re: export SAS output to excel in column and rows

nazmul — Wed, 14 Jun 2017 22:53:31 GMT

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

Re: export SAS output to excel in column and rows

Reeza — Wed, 14 Jun 2017 22:54:27 GMT

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.

Re: export SAS output to excel in column and rows

nazmul — Wed, 14 Jun 2017 23:23:00 GMT

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

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;

Re: export SAS output to excel in column and rows

Reeza — Wed, 14 Jun 2017 23:26:56 GMT

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.

Re: export SAS output to excel in column and rows

nazmul — Wed, 14 Jun 2017 23:27:01 GMT

My SAS software information:

Re: export SAS output to excel in column and rows

nazmul — Thu, 15 Jun 2017 01:08:17 GMT

Dear Reeza, ODS option is not working.

Re: export SAS output to excel in column and rows

Reeza — Thu, 15 Jun 2017 02:19:52 GMT

@nazmul wrote:

Dear Reeza, ODS option is not working.

I don't know what that means.

Option 2 is listed above.

Re: export SAS output to excel in column and rows

ballardw — Fri, 16 Jun 2017 23:02:57 GMT

Your output that you have shown appears to be generating a line of text from the macro. So that is what you get when you export the result.

You may to dig into the macro code and find the part that is actually writing the output. From what you are showing you might find a data step with PUT statements. There should be a data set that is being used to create those PUT statments. That is the data set that you likely need to export. You may want to add appropriated labels to variables or other formatting options and then try Proc Print with an ODS destination.

Re: export SAS output to excel in column and rows

nazmul — Sat, 17 Jun 2017 17:15:44 GMT

Dear Ballardw

I like your idea. But I am sorry to say that I am not this much efficient in SAS. Is it possible for you to explain the issue with an example perferably with a SAS dataset and relevant code.

I really appreciate your help.

Dear Reeza,

I have contacted with Eventus representative. They could not give me any solution.

Re: export SAS output to excel in column and rows

nazmul — Sat, 17 Jun 2017 17:19:29 GMT

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

Re: export SAS output to excel in column and rows

Reeza — Sat, 17 Jun 2017 18:42:21 GMT

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;

Re: export SAS output to excel in column and rows

nazmul — Sat, 17 Jun 2017 21:28:42 GMT

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

Re: export SAS output to excel in column and rows

Cynthia_sas — Sat, 17 Jun 2017 22:10:37 GMT

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

Re: export SAS output to excel in column and rows

nazmul — Sat, 17 Jun 2017 22:48:07 GMT

Dear Cynthia,

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

Re: export SAS output to excel in column and rows

Patrick — Sun, 18 Jun 2017 03:15:22 GMT

@nazmul

What you're asking from @ballardw "perferably with a SAS dataset and relevant code" is what I'm missing from you.

Why don't you post a data step creating the SAS table structure you want to export to Excel. This will allow us to post working and tested code samples for what you're asking for.

Or are you asking for these reports you've posted pictures of to be written to Excel? If that's the case then most likely changes to the macro creating these reports would be required and you would have to post a working sample with all the code first (so a data step creating source sample data, the full macro code and the call to the macro with the sample source data creating the output - just something with fully works and which we can run in our environment to then modify and create the Excel output you're after).

Re: export SAS output to excel in column and rows

nazmul — Mon, 19 Jun 2017 06:10:06 GMT

Hi Patrick,

Thank you for coming forward to helping me. I found so many nice people like you in this platform. I think I do not want to spend time in trying to convert the HTML sas output to excel output anymore. I have already spent one whole day on it. I am expressing my cordial gratitude to all of you for helping me

Hasan

Re: export SAS output to excel in column and rows

Reeza — Mon, 19 Jun 2017 13:19:54 GMT

Not ideal but you can copy and paste the HTML to an Excel file manually. It gets separated to cells automatically though you'll probably have to adjust some fields for now.