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

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

rsb7678 — Thu, 25 Mar 2010 13:55:32 GMT

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

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

Cynthia_sas — Thu, 25 Mar 2010 14:21:17 GMT

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

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

rsb7678 — Thu, 25 Mar 2010 14:54:26 GMT

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.

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

sbb — Thu, 25 Mar 2010 15:00:33 GMT

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.

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

rsb7678 — Thu, 25 Mar 2010 15:16:48 GMT

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.

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

sbb — Thu, 25 Mar 2010 18:29:38 GMT

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

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

Cynthia_sas — Thu, 25 Mar 2010 19:17:26 GMT

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.

Assuming that you have coded the HTML file yourself or used a product such as DreamWeaver to make the HTML page or you have used some method to create an HTML file that sits on your web server, then you need to read about the use of the tag in order to make hyperlinks on web pages. Your browser may or may not launch Excel when the hyperlink specifies that an XLS file should be opened. For general HTML information, such as how to code an ANCHOR tag, there is no better resource than the W3SCHOOLS site:
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

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

rsb7678 — Mon, 29 Mar 2010 12:15:20 GMT

Thank you , Cynthia and Scott. Was successfully able to create the button using HTML and ODS to populate the excel report.