Report export in excel

[nannà]

Hi,

I am unable to export the report from Guide to excel,

do you have any suggestions?

[RMP]

a little more information would be helpful 🙂

[nannà]

I use summary table task for create a report;

I want generate the report in xls estension so i insert the code:

ODS

tagsets.msoffice2k file="..."

Proc tabulate........(Guide's code)

ODS

tagsets.msoffice2k close;

but the result is a report with html extension;

;

[art297]

I think the file will open as an Excel file if you set the file's extension to be .xls.

However, depending upon which version of Excel you have, you may want to take a look at: http://support.sas.com/kb/31/956.html

[ct]

If you are using the summary table task, you can just check the box for "Save results to a dataset" on the Results menu.  Then, run the report.  You should see the html file and also a dataset.  Open the dataset and select Microsoft Excel under the Send To menu.

[Hima]

Say that the data set you used to create html report is Table_A. Run the below code, instead of converting the html report to excel.

PROC EXPORT DATA= Table_A

            OUTFILE= "C:\Documents and Settings\Test.xls"

            DBMS=EXCEL REPLACE;

     SHEET="Sheet_1";

RUN;

[art297]

Hima: yes, if the OP has SAS Access for pcfiles and doesn't like the appearance of the original.

[Cynthia_sas]

Hi:

  Look at the explanation and the code snippet at this previous posting:

http://communities.sas.com/message/8719#8719

LIBNAME engine and PROC EXPORT and PCFILES methods create "data-to-data" export without any colors, fonts or formatting control over cell widths, titles, etc. If you want that "plain" look and feel, then use these true export methods.

  ODS methods re not "true" export methods. ODS uses a markup language method of creating output files that Excel can open and render -- ODS methods work because they create ASCII text files (CSV, HTML or XML) that are compliant with Microsoft Office standards. Try the code snippet in the above posting. The only hitch is that inside EG, when you are submitting code with your own ODS statements, you have to turn off the EG automatic creation of output so either

1) go to Tools--> Options --> Results and uncheck ALL the result types (no check next to SASReport, HTML, RTF, etc)

OR

2) use

ODS _ALL_ CLOSE;

BEFORE any of your own code in the EG code editor window. This statement will close anything that EG automatically tries to open and create. The down side of this approach is that you might get a blank SASReport Result tab or a blank HTML Result tab, depending on what you have checked under Results to automatically generate. And, then you will get a separate Result tab for the output created by your ODS statements.

  As the above posting explains, the file extension that you use (.HTML, .XML, versus .XLS) is merely a convenient way to "fool the Windows registry" into launching Excel when you double click on the file name. Normally, a .HTML file will launch a browser, for example -- well, if you use ODS MSOFFICE2K, you don't want to launch a browser for the file, you want to launch Excel. So you either name the file .HTML and go outside of EG to Excel and do a File --> Open of the HTML file from inside Excel, or you "fool" the Windows registry by naming the file .XLS -- your choice.

  The key is that from inside EG, you have the added complication of EG trying to add ODS statements to your code based on your choices under
Tools --> Options (hence the need for you to adjust using #1 or #2 as explained above).

cynthia