topic Re: .xlsx output using ODS option in SAS Programming

.xlsx output using ODS option

Data_User — Tue, 16 May 2017 12:08:41 GMT

Hi,

Is there a way to export the SAS output into .xlsx format using ODS option? The below program is not working.

proc sort data=sashelp.class out=test;

by age;

run;

ods excel file="/sasserver/excel_conversion_file.xlsx";

proc print data=test;

run;

ods excel close;

Re: .xlsx output using ODS option

PaigeMiller — Tue, 16 May 2017 12:13:38 GMT

"Not working"?

Please tell us EXACTLY what is not working. Show us the SASLOG. Give us details.

Just a guess, is that a valid file location next to FILE= ???

Re: .xlsx output using ODS option

Data_User — Tue, 16 May 2017 12:25:50 GMT

Yes, it's working when I use .xlsx version. File is getting created, cannot open it. Thanks for looking into it.

It says 'Excel cannot open the file because the file format or file extenstion is not valid'. However this is working fine with .xls format.

Is there a way to get the output file in .xlsx without any error?

proc sort data=sashelp.class out=test;

by age;

run;

ods tagsets.excelxp file="/sas server path/excel_conversion_file.xlsx";

proc print data=test;

run;

ods tagsets.excelxp close;

Re: .xlsx output using ODS option

Data_User — Tue, 16 May 2017 12:26:57 GMT

Location used in the FILE= is correct.

Re: .xlsx output using ODS option

PaigeMiller — Tue, 16 May 2017 13:01:29 GMT

Is the problem happening using ODS EXCEL like in your original post, or using ODS TAGSETS.EXCELXP as in your second example?

What version of Excel do you use?

Re: .xlsx output using ODS option

Cynthia_sas — Tue, 16 May 2017 13:46:13 GMT

Hi:
TAGSETS.EXCELXP does NOT, NOT, NOT, ever create an XLSX file. TAGSETS.EXCELXP can ONLY create a simple .XML file which contains Spreadsheet Markup Language 2003 XML tags for a workbook/worksheet. The "official" file extension is .XML. If you use a .XLS file extension, typically, Excel will open the file, but display a message about the file extension not matching the contents. With TAGSETS.EXCELXP you should NEVER use XLSX as the file extension.

With ODS EXCEL, on the other hand, the ONLY file extension you should use is .XLSX. If you truly used ODS EXCEL, then you should be able to open the .XLSX file with Excel, if you a creating the file on a Unix box, you might have to move the .XLSX file to a Windows machine so you can open the file.

If you are using TAGSETS.EXCELXP, try using the simple .XML file extension and then moving the file to a system with Excel to open the file.

cynthia