BookmarkSubscribeRSS Feed
Aquaroyal72
Calcite | Level 5

How to convert XML/XLS format file into xlsx to display as webpage on SP2010 thru SAS?

10 REPLIES 10
SASKiwi
PROC Star

What version of SAS are you using and how are your XML/XLS format files created? If you have SAS 9.2 or 9.3 you can create XLSX format Excel workbooks directly if you have SAS/ACCESS to PC Files licensed.

Aquaroyal72
Calcite | Level 5

I'm using SAS 9.2. Can you send me any code. I tried it, created in xlsx, but when tried to open its won't open. Can you please help?

Thanks

Aquaroyal72
Calcite | Level 5

I have to run the Stored process thrue SAS-Excel addins. to get the report. Can you help?

Cynthia_sas
SAS Super FREQ

Hi:

  What you say above that you have to "run the Stored process thru SAS-Excel addins" seems incompatible with your first post that you need to "convert XML/XLS format file into xlsx to display as webpage".

 

  When you use the SAS Add-in for Microsoft Office, you are using what is essentially an "extra" .NET module added to Office that allows Excel (and Word, and PPT and Outlook) to communicate with SAS data (via the whole metadata/BI Platform infrastructure) in order to 1) run Tasks and Wizards against SAS data; 2) surface SAS data directly in Excel and/or 3) populate an Excel workbook or worksheet with results from a Stored Process (or populate other Office products with stored process results).

 

  Excel is NOT a "web page" --as far as I know, if you try to send an XLSX or XLS file to a browser (via the HTTP protocol), the browser will launch a helper application to open the file. (For example, the helper application for a PDF is Acrobat Reader and the helper application for a CSV or XLS or XLSX file is Excel)

  

  Part of the elegance of the SAS Stored Process is that when you run it in Excel, the results are returned to Excel. When you run the same stored process in PowerPoint, the results are returned to a presentation; when you run the same stored process in Word, the results are returned in document form. If you run the same stored process in the Information Delivery Portal or using the Stored Process Web App, then the results come back to a browser, as an HTML page (usually).

  So, if you run a stored process in Excel, there is no conversion necessary on your part. You have 2 choices for how to get your results. 1) Using SAS--> Options, you can select the stored process result type to be either: SASReport XML, HTML or CSV results. OR, 2) you can explicitly code an override to &_ODSDEST in your stored process program. Do note that if you want to use ODS TAGSETS.EXCELXP "flavor" of Office 2003 XML, then, you cannot use the SAS Add-in to "receive" those results from a SAS stored process. The SAS Add-in for Excel only receives SASReport XML, HTML or CSV results; just as the SAS Add-in for Word can only receive SASReport XML, RTF or HTML results; and PPT can only receive SASReport XML results. SASReport XML is very flexible XML and when you have the SAS BI Platform you are able to use SASReport XML as a sort of "universal" report format that the BI client applications all know how to open and render.

  Once you get your SP results into Excel, you can save the results and choose how the contents will be refreshed. For example, if you want the results to be automatically refreshed, that is a choice. Or you can manually refresh the workbook contents, the next time you open the workbook.

  So, given your two statements about what you want, I am not entirely sure that your stored process is coded to do what you want. And it's not clear to me (since you haven't shown any code or provided any other descriptions of your process) what else might be going on with your stored process.

  It might be better for you to open a track with Tech Support, since they can look at ALL your code. They can have you send them the metadata information for your stored process. They can figure out which version of the Add-in for Microsoft Office you have and which version of the BI Platform you have and they can collect information on how you're running your stored process and they can help you figure out whether your stored process is coded correctly to return results to both a web browser and to the SAS Add-in for Microsoft Office.

  You would not normally use PROC EXPORT within a SAS Stored Process, so I don't recommend going down that road as an alternative to the stored process method.

cynthia

Aquaroyal72
Calcite | Level 5

Hi Cynthia,

Thanks for the help. I want to tell you something that , My SAS 9.2 is running on Unix server and my MS addins and EG 4.2 is running on windows. I try to create my reports in xlsx format. In tagsets.ExcelXP using 9.2 you can create it but when I try to open the report itas give me and error and empty spreadsheet comes. Is that any way I tried all things, like convert the report after its created in xls and run the VBA script to convert in xlsx format. Is that and codes or way you can create it.

All help should be appereciated,

Thanks you,

Shailesh Patel

Cynthia_sas
SAS Super FREQ

Hi:

  First of all, as I explained, when you use the SAS Add-in for Microsoft Office and a stored process, the only type of output that the Excel Add-in can "receive" is HTML, CSV or SASReport XML. The SAS Add-in CANNOT receive TAGSETS.EXCELXP output from a stored process. So, I don't understand whether you're still talking about stored processes or whether you're talking about using TAGSETS.EXCELXP to just create files that Excel can open.

  Second, let's assume that you ARE using TAGSETS.EXCELXP on Unix to create a file that Excel can open. It would be totally wrong to do this:

ods tagsets.excelxp file='c:\temp\wrongfile.xlsx';

... more code ...

ods tagsets.excelxp close;

The type XML created by TAGSETS.EXCELXP is Microsoft Office 2003 Spreadsheet Markup Language XML. An .XLSX file is a proprietary Microsoft XML format that was introduced in Office 2007 and is DIFFERENT XML from that created by Microsoft for Office 2003. So, it is inappropriate to use .XLSX as the file extension for an Office 2003 XML file. This is what you should do when you create TAGSETS.EXCLEXP output:

ods tagsets.excelxp file='c:\temp\rightfile.xml';

... more code ...

ods tagsets.excelxp close;

Or, I have seen some people use a .XLS file extension with TAGSETS.EXCELXP. But that is it. So back to the original question. Are you using stored processes with the Add-in for Excel or not? If so, then you can only use the Stored Process Web Application in order to run a stored process that returns TAGSETS.EXCELXP results. If you are returning stored process results to the SAS Add-in, you can only return HTML, CSV and/or SASReport XML results.

So, it's not clear what code you're using. You haven't posted anything that clarifies whether you're using a stored process or not. You have only described something that occurs when you use a file extension that you shouldn't be using. A file extension of .XLSX is irrelevant if you're running a stored process, because stored processes don't have file extensions. When you save your stored process results using the SAS Add-in, you can pick the file extension you want. However, if you are NOT doing a stored process, and you are using TAGSETS.EXCELXP then you should be using a file extension of .XML; and because of these things -- not understanding exactly WHAT code you are running, whether you're running a stored process or not and whether you're using SAS to make a static file or whether you're using SAS via the MS Add-in, I think your best resource for help is to work with Tech Support.

cynthia

Alexsander
Fluorite | Level 6

Hi Cynthia,

     I just read your post on this topic. I liked your explanation on thi topic. I learned few points from your post. Thank you for that.

I have similar kind of issues. I need your valuable suggestion on excel output with .XLSX

We have been working in SAS 9.2 suits for our project, especially BI suits (STP, WRS,IDP, DI Studio, OLAP cubes IMAP), soon we are going to move to SAS 9.3 platform.

We have been generating excel file (XLS) using TAGSETS.EXCELXP in stored process, below is the sample code:

%if "%upcase(&_ODSDEST)" = "TAGSETS.EXCELXP" %then %do;

    data _null_;

/* some code*/

    run;

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

    %let rc = %sysfunc(appsrv_header(Content-disposition,attachment%str(;) filename=%trim(&file_name).xls));

%end;

This is working fine. The challenge for us now is that we have to reduce the steaming time of the excel file output while we execute our STP as our web server's timeout is reduce to 15 minutes from 30 minutes. I was wondering if we generage excel file with .XLSX instead of XLS file the steaming time might be less. As you know the latest excel file (.XLSX) is small in size compared with previous version (.XLS).Note that we have excel2010 installed on our client machine.

Please let me know how to generage .XLSX excel file using stored process. Do we need to change the Tagset or need to change the browser setting or installed lattest hotfix to get XLSX file.

Thanking you inadvance.

Regards,

Alexsander

Cynthia_sas
SAS Super FREQ

Hi, Alexsander:

  First things first -- in the future, please do NOT "piggyback" your new question onto the end of a year old posting. Many people do not read these old posts (including me, if I'm in a hurry) and in fact, your October 17 post got put in the middle of the whole stream, so if you scroll to the very bottom of all the posts, for the most recent, the very last post is from 2012, so that is confusing. The usual etiquette is to start a new post and insert a link to the earlier post you are referencing.

  Next, TAGSETS.EXCELXP is NOT capable of creating a "true, proprietary" .XLSX file. In fact, the reason an XLSX file is so small is that essentially it is a ZIP archive or compressed archive of all the different files that Excel now uses to define the XLSX proprietary format. The only way I know of to create an XLSX file at this time is using PROC EXPORT or the LIBNAME engine. Unlike ODS, these methods do a true export from SAS dataset proprietary format to Excel spreadsheet proprietary format.

  When you say this "We have been generating excel file (XLS) using TAGSETS.EXCELXP in stored process", you are not correct. TAGSETS.EXCELXP does NOT create a "true, binary" Excel file in Excel 97-2003 format. TAGSETS.EXCELXP creates an ASCII text file of XML instructions that conform to the Office 2003 specification for Spreadsheet Markup Language XML. So you are generating an XML file that Excel knows how to open when you use TAGSETS.EXCELXP -- NOT an XLS file. Although you might think of the file as being "XLS", naming the file with the .XLS extension is just a convenient way to fool the Windows registry into launching Excel if the file appeared in Windows Explorer and you did a double click. Since the output created from TAGSETS.EXCELXP is merely ASCII text XML tags, generally, XML files are very verbose and tend to be larger than a proprietary XLS file. The APPPSRV_HEADER function that you use is merely telling the receiving application (browser), what helper application (using a MIME type) should be used to open what is being sent in the stream.

  For a comparison of the same file in differing formats, consider the sizes shown here. The file is the same, only the file format is different. 396 rows/7 columns. The XML file is Office 2003 XML format. Note that the XLSX file is just a bit smaller than the CSV file. The propietary .XLS file is bigger than the XLSX, but not by much. The XML file is largest of all. But, let's look inside the XLSX file -- the second screenshot shows what is actually inside the XLSX file -- it is not 1 file, it is a collection of files in a structure defined by Microsoft and then compressed. All I did to "look inside" the XLSX file was rename the file to ZIP and then open the file with WinZIP to take the screenshot.

  If you are having performance issues, with your stored processes, you should open a track with Tech Support. I can't imagine what type of output you're creating that takes -MORE- than 15 minutes to complete. Someone will have to look at ALL your code, not just the APPSRV_HEADER pieces to see where your code is inefficient and can be improved. And that is a separate issue from creating the XLSX file issue. Right now, it's possible that your program could be improved to take less time but would then allow you to keep using TAGSETS.EXCELXP. As I explained, at this time, TAGSETS.EXCELXP cannot create/does not create an XLSX format file. This is a proprietary file format introduced by Microsoft in Office 2007.

  Given that you are having performance issues and given that this is in a stored process, I would suggest that you open a track with Tech Support for more specific help.

cynthia


compare_file_sizes.pngwhat_is_inside_xlsx.png
SASKiwi
PROC Star

Check out this SAS Note: http://support.sas.com/kb/42/981.html

which has this example:

proc export data=sashelp.class outfile='\\phibred.com\jh\users2\notise\test2.xlsx' dbms=excel2007 replace;

sheet='class';

run;

There are other SAS Notes on this issue - just search on EXPORT XLSX.

If you are using the the SAS Add-in I suggest you check out the documentation for this.

sebrah
Calcite | Level 5

HI,

If you are trying to convert xml files to xls, please refer to

http://support.sas.com/kb/43496


I use SAS 9.2 and find this code very useful and you can actually automate the task of converting xml file to xlsx.

Regards,

Shen

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 10 replies
  • 11023 views
  • 6 likes
  • 5 in conversation