12-15-2014 11:37 AM
I am at a loss for this one, I am able to output my proc report or proc tabulate in HTML, PDF...but I can't seem to get the output into Excel using SAS 9.3 with 32 bit and Excel 2007 32 bit.
Here is the ODS statement that give errors.
ods tagsets.excelxp file=\\Servername\hrp\SAS\Output\WUSS1.xml style=minimal;
proc report data = whatever;
Column Retirement Gender Age count;
Define Retirement / group order = data "Eligibility To Retire" style(column)=[ just=c cellwidth=1.4 in indent=.3 in] style(header)=[indent=.1 in];
Break after Retirement / summarize skip ol suppress; ;
RBreak after / Summarize style = [foreground=black just=c font=('calibri',11pt,bold)] ;
Define count / 'Total';
ods tagsets.excelxp close;
12-15-2014 12:39 PM
Other than the fact that your FILE= option should be quoted, I am not sure what is happening. Have you tried it with and without the style= option? I no longer have SAS 93 or Excel 2007 to test with, but this code worked for me when I did have 9.3 and Excel 2007:
As an additional comment: SKIP and OL will not work for ANY ODS destination -- they are LISTING only options. I do not really expect INDENT= to work. I believe it is only for PDF and RTF, possibly HTML. There were problems in earlier versions of SAS with the STYLE=MINIMAL and TAGSETS.EXCELXP -- that's why I suggested trying the default. And, in some versions of SAS, the .XML used to launch the file using a browser. You might try .XLS. Do you have Excel on the \\Servername? If you open the created file with Notepad, do you see XML tags?
This might be something to work with Tech Support, they can test using older versions of SAS. I just ran the program below using SAS 9.4 and Excel 2013 and everything worked OK (except for INDENT=, which I didn't expect). And, besides, it doesn't make sense to use INDENT= anyway when you have JUST=C. Or you could be running into this issue with STYLE=MINIMAL and PROC REPORT: 53344 - An invalid file might be generated when the REPORT procedure is used with the ExcelXP tagset.
ods tagsets.excelxp file='c:\temp\sometest.xml' style=sasweb;
title '1) TAGSETS.EXCELXP with style=sasweb';
proc report data=sashelp.class nowd;
Column Age Sex Name height;
Define age / group order = data "Eligibility To Retire"
style(column)=[just=c cellwidth=1.4 in ]
define sex / order;
define name / order;
Define height / mean 'Height Stats' f=6.1;
Break after Age / summarize style=[foreground=black font_weight=bold];
compute after age ;
name = 'Average';
line ' ';
RBreak after / Summarize style=Header[foreground=black font_weight=bold] ;
Name = 'Average';
ods tagsets.excelxp close;