DATA Step, Macro, Functions and more

.xlsx output using ODS option

Reply
Contributor
Posts: 43

.xlsx output using ODS option

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;

Trusted Advisor
Posts: 1,909

Re: .xlsx output using ODS option

Posted in reply to Data_User

"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= ???

Contributor
Posts: 43

Re: .xlsx output using ODS option

Posted in reply to PaigeMiller

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;

Contributor
Posts: 43

Re: .xlsx output using ODS option

Posted in reply to PaigeMiller

Location used in the FILE= is correct.

Trusted Advisor
Posts: 1,909

Re: .xlsx output using ODS option

Posted in reply to Data_User

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?

SAS Super FREQ
Posts: 8,862

Re: .xlsx output using ODS option

Posted in reply to PaigeMiller
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
Ask a Question
Discussion stats
  • 5 replies
  • 344 views
  • 0 likes
  • 3 in conversation