BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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!
7 REPLIES 7
David_SAS
SAS Employee
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
deleted_user
Not applicable
Hi David,

I am using Excel 2000.
David_SAS
SAS Employee
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
deleted_user
Not applicable
Hi David,

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

Thanks for your help.
Cynthia_sas
SAS Super FREQ
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
Vince_SAS
Rhodochrosite | Level 12
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
Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1592 views
  • 0 likes
  • 4 in conversation