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.
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.
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
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.
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;
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.
My SAS software information:
Dear Reeza, ODS option is not working.
@nazmul wrote:
Dear Reeza, ODS option is not working.
I don't know what that means.
Option 2 is listed above.
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;
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
Dear Cynthia,
I put slash last time. But did not work. Thank you for being helpful.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.