The SAS Output Delivery System and reporting techniques

converting xml to xls

Reply
Contributor
Posts: 73

converting xml to xls


Is there a better way to get output into an actual xls?  I'm running 9.3 on 64bit windows server 2006r2 and office 2013

ods tagsets.excelxp path=xxxx file=spreadsheet.xml style xlsanspprinter;

ods tagsets.excelxp options (

embedded_titles='yes' embedded_footnotes='yes' sheet_name='Reversibility'

                              suppress_bylines='yes'

                              width_points='1' width_fudge='1'

                              absolute_column_width='100,100,100,100,100,100,100,100,100,100,100'

                              autofit_height='yes' orientation='landscape'

                              autofilter='No' frozen_headers='3'

                              center_horizontal ='yes ');

proc report;....

ods gtagsets.excelxp close;

ods listing close;

right now I'm using a vbscript kicked off by sas to convert the xml to xls

Dim xlApp, xlWkb

Set xlApp = CreateObject("excel.application")

set fs = CreateObject("Scripting.FileSystemObject")

SourceFolder="\\srv01\Reports\Reports\"

TargetFolder="\\srv01\Reports\Reports\"

for each file in fs.GetFolder(SourceFolder).files

If Right(LCase(file.Name), 4) = ".xml" Then

Set xlWkb = xlApp.Workbooks.Open(file)

BaseName=  fs.getbasename(file)

FullTargetPath=TargetFolder & "\" & BaseName

xlApp.DisplayAlerts=false

xlWkb.SaveAs FullTargetPath, 51

xlApp.DisplayAlerts=true

xlWkb.close

wscript.sleep 5000

End If

next

fs.DeleteFile("\\srv01\Reports\Reports\\*.xml")

SAS Super FREQ
Posts: 8,864

Re: converting xml to xls

Posted in reply to wkossack_nspirehealth_com

Hi, an "actual xls" file is a proprietary Microsoft format. TAGSETS.EXCELXP creates Microsoft Office Open XML Spreadsheet Markup Language XML 2003 specification. So unless you use the LIBNAME engine or PROC EXPORT, the XML file is as close to Excel "real/actual" format as you can get. with ODS.

  The VBScript solution is a good one. It won't work on all operating systems, however, but it is one way to re-save the files, if you MUST have proprietary format .XLS files. Otherwise, I find that naming the files with the .XML extension and educating the folks who open the .xml file is also a viable alternative.

cynthia

Contributor
Posts: 73

Re: converting xml to xls

Posted in reply to Cynthia_sas

according to SAS Tech support 9.4M1 will produce excel 2010 spreadshets

One thing that is puzzeling me is that the vbscripts don't run consistently in midnight batch resulting in xml files and xlsx files remaining in the output folders confusing clients using a web portal to get their reports.  The vbscript runs fine if I kick it off in batch but not when run at night

Contributor
Posts: 73

Re: converting xml to xls

Posted in reply to wkossack_nspirehealth_com

IT changed the trust settings on the batch file that was running and now things seem to be working

Ask a Question
Discussion stats
  • 3 replies
  • 1565 views
  • 0 likes
  • 2 in conversation