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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.