The SAS Output Delivery System and reporting techniques

Adding title & footnotes to a dataset?

Reply
N/A
Posts: 0

Adding title & footnotes to a dataset?

Hello,

I was wondering if someone could help me. I am working with SASIntrnet, and I am generating a dataset, and I want the user to be able to save it as an excel file. I am using the macro %ds2csv. It works great. But I want that particular excel file to have the associated footnote and title. Any suggestions?

Many thanks!
SAS Employee
Posts: 174

Re: Adding title & footnotes to a dataset?

There are several ways with ODS to do what you want to do. Which version(s) of Excel are you targeting? Knowing the version is the first step towards suggesting the appropriate approach.

-- David Kelley, SAS
N/A
Posts: 0

Re: Adding title & footnotes to a dataset?

Hi David,

I am using Excel 2000.
SAS Employee
Posts: 174

Re: Adding title & footnotes to a dataset?

For Excel 2000, a good initial approach is to try the MSOFFICE2K tagset. You're using 9.1, right? Do:

[pre]
ods tagsets.msoffice2k file="myfile.xls";
/* run your job */
ods tagsets.msoffice2k close;
[/pre]

This will write an Excel-friendly variant of HTML into the specified file. Titles and footnotes will be retained.

-- David Kelley, SAS
Message was edited by: David@SAS at May 9, 2006 4:05 PM
N/A
Posts: 0

Re: Adding title & footnotes to a dataset?

Hi David,

Where is the myfile.xls being saved to? How would the users download this particular file?

Thanks for your help.
SAS Super FREQ
Posts: 8,744

Re: Adding title & footnotes to a dataset?

If you are using SAS/IntrNet, you must be writing to _webout -- is that correct??? Have you ever changed the APPSRV_HEADER to change the HTTP content-type of the stream that you are sending back to the client???

David was pointing you in the right direction with tagsets.msoffice2k -- but with SAS/IntrNet, you'd have to do a bit more work.

First, you have to decide whether you 1) want all the ODS styles in Excel or 2) whether you want to send the users a plain CSV file.

Then you have to decide whether you 1) want to show them the report and then put a button on the report where they will initiate the download or 2) when they click on the report link, you will run the report and automatically send the results back as either CSV or HTML.

Remember that _webout is a pipeline between the web server and the web browser. Normally, when you use ODS with _webout, what comes down that pipeline is HTML content and so SAS/IntrNet automatically generates the correct HTTP header (also known as a content-type header or MIME header).

If you would use the tagsets.msoffice2k type of output with _webout, then you would not need a content type header, because it is Microsoft HTML -- the issue however is that the file would open in a browser. If you want the file to be opened by Excel, instead, then you would have to (inside you program) make sure that the APPSRV_HEADER function sent the content-type header for the browser to launch Excel when it received the file.

If you use ODS CSV to send plain CSV from the server to the client machine with SAS/Intrnet, I think you still need to send the right header for Excel to launch.

You can find some examples of using APPSRV_HEADER with SAS/IntrNet here:
[pre]
http://support.sas.com/rnd/web/intrnet/dispatch/
http://support.sas.com/rnd/web/intrnet/dispatch/srvhead.html
[/pre]
cynthia
SAS Employee
Posts: 285

Re: Adding title & footnotes to a dataset?

Pulling together what David and Cynthia have said, here is some (untested) sample code that should work:

%let RV=%sysfunc(appsrv_header(Content-type,application/vnd.ms-excel));

ods tagsets.MSOffice2K file=_webout path=&_tmpcat (url=&_replay) rs=none ;

title 'This is my title';
footnote 'This is my footnote';

proc print data=sashelp.class; run; quit;

tagsets.MSOffice2K close;
This will cause the Open/Save dialog to be presented. If Open is selected, the output will be opened with Excel, instead of the Web browser.

The key points are (1) use APPSRV_HEADER() to set the content type header before any content is written to _WEBOUT and (2) direct ODS to send the output to the _WEBOUT fileref.

Additionally, you may find these papers helpful:

http://www2.sas.com/proceedings/sugi28/052-28.pdf

http://support.sas.com/rnd/papers/#office2005

Vince DelGobbo
SAS R&D
SAS Super FREQ
Posts: 8,744

Re: Adding title & footnotes to a dataset?

Thanks Vince...I did not have my SAS/IntrNet examples available, so I couldn't send that code. I thought that &_tmpcat and &_replay were only needed for SAS/IntrNet in a FRAME= situation or a SAS/Graph output situation.
cynthia
Ask a Question
Discussion stats
  • 7 replies
  • 300 views
  • 0 likes
  • 4 in conversation