ODS to Excel (Viewer)

[Bill]

I currently produce a report using ods html to produce a file that excel is able to open. We have a number of users that only have excel viewer - which is unable to open an ods html or ods csv produced file. While proc export will work, I want to use labels instead of variable names, hence the ods method.

Is there an ods method that I can use to produce excel viewer readable files?
Or some other option?
Using SAS 9.1.3 on Windows.

Thanks,
wd

[Cynthia_sas]

Hi:
When I use the Excel Viewer to open files, the only supported file types are:
*.xlsx, *.xlsm, *.xlsb, *.xltx; *.xltm, *.xls, *.xlt, *.xml, *.xlw

Since ODS does not create "true" Excel files -- it only creates ASCII-text HTML or XML or CSV files -- that means that ODS output will not be openable in the Excel Viewer. If you try to "fool" the Windows registry by giving the ODS output an extension of .XLS, you can open the ODS file with Excel, but NOT with the Excel Viewer -- the Viewer knows that the real content of the file is HTML or XML or CSV and the Viewer will not open the file.

So you have a few choices:
1) create your ODS output from PROC REPORT and run a VB script to "resave" the file to a "true" Excel .XLSX or .XLS format or .XLSB format so the resaved file can be opened in the Excel Viewer. This approach would also preserve the colors, fonts and titles used for the ODS output.;
OR
2) If you have SAS/Access to PC file formats, try this code (it worked for me in 9.2 -- not sure whether PUTNAMES was available in 9.1):
[pre]

data class;
set sashelp.class(obs=3);
label name = 'Koala'
age = 'The Age';
run;

proc export data=class
file='c:\temp\class.xls'
dbms=xls replace label;
putnames=yes;
run;
[/pre]

when I run this form of PROC EXPORT, I can use the PUTNAMES=YES option with the LABEL option to write the labels (so I see Koala and The Age) in the Excel Viewer when I open the file. If there is no label specified for a variable, then the variable name is used in the output file. PUTNAMES is described on page 44-45 of the documentation:
http://support.sas.com/documentation/cdl/en/acpcref/63184/PDF/default/acpcref.pdf

There's also a discussion of the difference between DBMS=XLS and DBMS=Excel for PROC EXPORT in the doc -- PUTNAMES only works with DBMS=XLS for Excel files.

cynthia

[Bill]

Thanks Cynthia;

SAS 9.1.3 does not support your novel ideas. For now at least, the users will have to live with variable names via prod export.

wd