xml to xls with SAS ods

[SamA]

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.

[Reeza]

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

[SamA]

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/)?

[Reeza]

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;

[twocanbazza]

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