The SAS Output Delivery System and reporting techniques

create Excel report using ODS and have a link to the excel in Web

Reply
Occasional Contributor
Posts: 14

create Excel report using ODS and have a link to the excel in Web

Hi

I would like to create an excel report using ODS and then have a intranet link through SASWeb to the Excel file which is stored in Unix (not the intranet share) ? How can I achieve this ?

Thanks
SAS Super FREQ
Posts: 8,743

Re: create Excel report using ODS and have a link to the excel in Web

Hi:
For ODS HTML, RTF and PDF, links that you build with the URL= style attribute can be either a relative link:
[pre]
define region /group 'Region'
style(column)={url='allreg.html'};
[/pre]

but it can also be a fully qualified URL:
[pre]
define region /group 'Region'
style(column)={url='http://www.wombat.com/somedir/allreg.html'};
[/pre]

I think, (but am not entirely sure) that it can be a network computer name/location like this:
[pre]
define region /group 'Region'
style(column)={url='\\othername\usr\local\somedir\allreg.html'};
[/pre]

You might want to check with Tech Support for 2 reasons:
1) to find out exactly what is allowed in general for URL= (for example, is the machine name version of the URL allowed) and
2) specifically, what type of URL value is supported in Excel, when using the various methods to create files.

When you use ODS to create a file, you are creating an ASCII text file that Excel knows how to open. You are not creating a "true binary .XLS" file in Excel proprietary format. There are three ODS methods for creating ASCII text files that Excel can open:
1) create a CSV file using ODS CSV or ODS CSVALL
2) create an HTML file using ODS HTML or ODS MSOFFICE2K (or other HTML-based destinations)
3) create a Spreadsheet Markup Language XML file using ODS TAGSETS.EXCELXP

I suspect that, since ODS CSV does not use any style template information, the URL= style attribute will not work for method 1 (CSV) files created with ODS. That leaves you with URL= in #2 or #3. Tech Support will know whether there are any differences in how Excel supports hyperlinks specified in an HTML file versus how Excel supports hyperlinks specified in a Spreadsheet Markup Language XML file.

I know there have been several previous forums postings about hyperlinks with TAGSETS.EXCELXP -- as I remember, mostly they were questions about linking from sheet to sheet within one workbook or across workbooks, but on the same file system. This might be another question for Tech Support.

cynthia
Occasional Contributor
Posts: 14

Re: create Excel report using ODS and have a link to the excel in Web

Hi ,

title; footnote ;
ods listing close;
Ods html file='G:\Documents and Settings\SAHASRA\Desktop\MyExcelSASdata.xls' style=styles.sasweb;
Proc print data=TestData;
Run;
Ods html close;
Ods listing;

1. Instead of specifying the location (above ) of the excel file - - "MyExcelSASdata.xls", Can I dynamically generate the Excel file from my SAS dataset?

2. Next step is to create a hyperlink or button on my Web page. This hyperlink or button when clicked should open the above Excel file - - - "MyExcelSASdata.xls".

Please help.
Super Contributor
Super Contributor
Posts: 3,174

Re: create Excel report using ODS and have a link to the excel in Web

Honestly - I sense less "Please help." and more "Please write the SAS code for me."

So, how do you expect to author, own and support the SAS application program code that gets written for someone, instead of by someone.

Now, if we can convince Cynthia to post her PayPal account or banking information, you can engage her services for a fee.

Scott Barry
SBBWorks, Inc.
Occasional Contributor
Posts: 14

Re: create Excel report using ODS and have a link to the excel in Web

Hi Scott,

I admit that I'm new to creating SAS web based applications and not asking for SAS code. As I havnt done this earlier, if you can atleast guide me or point me to some samples / notes / document that would be great help.

Thanks.
Super Contributor
Super Contributor
Posts: 3,174

Re: create Excel report using ODS and have a link to the excel in Web

Regarding your two queries, some guidance:

Question #1:
1. Instead of specifying the location (above ) of the excel file - - "MyExcelSASdata.xls", Can I dynamically generate the Excel file from my SAS dataset?

Reply: Yes, it is possible to use the SAS macro facility to generate macro variables and use a general-use SAS program to substitute the file-name (destination of your SAS-generated report output).

Question #2:
2. Next step is to create a hyperlink or button on my Web page. This hyperlink or button when clicked should open the above Excel file - - - "MyExcelSASdata.xls".

Reply: SAS programming logic can be used to generate a "my Web page" HTML document with hyperlink and optionall a button/box to click for opening a local (or remote served) file/document.

Scott Barry
SBBWorks, Inc.

Recommended Google advanced search arguments, this topic/post items:

ods tagset excelxp site:sas.com

introduction macro facility site:sas.com

ods generate html documents site:sas.com

add hyperlink site:sas.com
SAS Super FREQ
Posts: 8,743

Re: create Excel report using ODS and have a link to the excel in Web

Hi:
I did not interpret your original question as asking how to code an HTML page or button to launch Excel for an ODS HTML-created file. Scott's answers are right on target. I just have a little extra "seasoning" to add to Scott's post:

In your original post you asked:
"I would like to create an excel report using ODS and then have a intranet link through SASWeb to the Excel file which is stored in Unix (not the intranet share) ? How can I achieve this ?"


Then, in your clarification, you said:
[pre]
title; footnote ;
ods listing close;
Ods html file='G:\Documents and Settings\SAHASRA\Desktop\MyExcelSASdata.xls' style=styles.sasweb;
Proc print data=TestData;
Run;
Ods html close;
Ods listing;
[/pre]


1. Instead of specifying the location (above ) of the excel file - - "MyExcelSASdata.xls", Can I dynamically generate the Excel file from my SAS dataset?

2. Next step is to create a hyperlink or button on my Web page. This hyperlink or button when clicked should open the above Excel file - - - "MyExcelSASdata.xls".


First, as I said previously. When you use ODS HTML, you are merely creating an ASCII text file that Excel knows how to open. Just because you name the file with the ".XLS" file extension, does not make it a "true, binary" Excel file. If you open your file 'G:\Documents and Settings\SAHASRA\Desktop\MyExcelSASdata.xls' with Notepad or some text editor, you will see HTML tags in the file.

So, knowing that you have just created an HTML file, let's look at your questions:
Q #1: Can you "DYNAMICALLY" generate the Excel file from your SAS dataset?? I don't know what you mean by this. Generally, when people use the term "DYNAMICALLY" they are talking about using either SAS/IntrNet or Stored Processes to create results that can be opened in Excel. I don't know whether you have those products. Your code snippet does not show the use of _WEBOUT or %STPBEGIN, so I am not sure whether you are using SAS/IntrNet or SAS Stored Processes. When you use ODS to create an HTML file -- even if the file extension is .XLS, the HTML file needs to be opened with the right application when the browser returns the results from the web server. SAS/IntrNet and Stored Processes on the BI Platform give you a way to control this. (More about this below)

As Scott explained, you could also just want the name of your output file to be dynamically generated from the name of the SAS dataset -- this is possible using some kind of SAS Macro variable or some kind of Macro processing.
[pre]
ods html file="&path.&fname..xls" style=styles.sasweb;
proc print data=&lib..&fname;
run;
ods html close;
[/pre]

where &LIB, &PATH and &FNAME were either created with %LET statements or CALL SYMPUT statements. This paper is a good introduction to SAS Macro Facility capabilities:
http://www2.sas.com/proceedings/sugi28/056-28.pdf

Q #2: Next, you said (in your clarification) that you wanted to create a hyperlink on your web page that would open the file created in #1. A standard ANCHOR tag with an HREF= attribute is how you link to or open any file.

Assum...
tag in order to make hyperlinks on web pages. Your browser may or may not launch Excel when the hyp...
http://www.w3schools.com/tags/tag_a.asp

You could code the entire HTML page creation with SAS, as Scott suggested. On the other hand, if you were using SAS/IntrNet, you would not need to code the whole HTML page yourself and I would tell you to investigate the APPSRV_HEADER function to send the right content-type header for the browser; if you were using the BI Platform and stored processes, I would tell you to investigate the STPSRV_HEADER function to send the right content-type header for the browser -- but since I don't know whether you are running in the BI Platform or with SAS/IntrNet or how you are making your web pages, I can only suggest that you might want to work with Tech Support on this question. Tech Support can look at your data, your code and review your product suite and make suggestions about the best approach for what you want to do.

cynthia
Occasional Contributor
Posts: 14

Re: create Excel report using ODS and have a link to the excel in Web

Thank you , Cynthia and Scott. Was successfully able to create the button using HTML and ODS to populate the excel report.
Ask a Question
Discussion stats
  • 7 replies
  • 415 views
  • 0 likes
  • 3 in conversation