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")
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
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
IT changed the trust settings on the batch file that was running and now things seem to be working
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.