topic xml to xls with SAS ods in ODS and Base Reporting

xml to xls with SAS ods

SamA — Thu, 19 Jan 2012 22:31:42 GMT

Hi,

I am outputting a SAS dataset to a multi-worksheet xml file using excelxp tagsets. I had been trying to produce an excel report using proc report but this became too unweildy due to formatting issues. I have another blank workbook that will have a template in it (ie. the headers and gridlines) for the report, and will have links in it to the output of the SAS program which is in xml. This will solve the difficulty of formatting everything with proc report; for now I am just using proc print to output to the xml file. The problem is that I can't create references in excel workbooks to xml files like I can to other excel workbooks. Is there a way in SAS automatically to save the xml output into an xls file? I may just end up using ods html to produce individual workbooks out of each sheet since these can be directly made into excel files. Any suggestions?

Thanks.

Re: xml to xls with SAS ods

Reeza — Thu, 19 Jan 2012 22:35:36 GMT

Convert to XLS file with SAS.

I'm assuming you're on windows of course.

Here's the code sample from SAS that does it.

And if all else fails there's DDE.

http://support.sas.com/kb/43/496.html

Re: xml to xls with SAS ods

SamA — Thu, 19 Jan 2012 22:45:01 GMT

Hi Reeza,

Actually I am using SAS through linux, but I do have windows on my machine too. I assume all I have to do is change the default and store parameters to linux directories? For example, the default parameter could by %STR(/home/mydir/)?

Re: xml to xls with SAS ods

Reeza — Thu, 19 Jan 2012 23:53:09 GMT

Its a VBScript so I think it needs actual windows to run properly, you'd need to run it on your machine somehow, but then you might as well open and re-save the file.

If it's just 'plain' data, ie no formatting, why not export to excel using libname and excel statements, if you can do that on linux (not familiar with that system at all).

libname test excel 'C:\temp.xls';

data test.one;

set sashelp.class;

data test.two;

set sashelp.class;

run;

libname test;

Re: xml to xls with SAS ods

twocanbazza — Fri, 20 Jan 2012 02:51:56 GMT

And it doesn't work if you are Using EG and a workspace server - there is a usage note about this...

http://support.sas.com/kb/44/927.html

Barry