Exporting data set into .xlsx in SAS 9.2

[LineMoon]

Hello Experts,

Please, I want to export a sas data set into .xlsx file in sas 9.2.

I have used export proc and ods intruction(it generate .xlsx file), but I can ont open the .xlsx file.

Thank you.

[Kurt_Bremser]

9.2 can only create xls format files, IIRC.

Please post code&log, and which operating system your SAS runs on.

[LineMoon]

@Kurt_Bremser :Thank you.

Please, IIRC ? you mean by that  ? : if I recall correctly

 ods tagsets.excelxp file='K/JM/multisheet.xlsx' options( sheet_name='Test_1' );
  proc print data=First_test;
  run;
  ods tagsets.excelxp close;

[Kurt_Bremser]

When using ods tagsets.excelxp, you create a XML file, so you also should use the .xml filename extension. Excel will complain if the file format does not match the extension. And you are right about the IIRC acronym.

[Kurt_Bremser]

And which error message are you getting from Excel when trying to open the file?

[sabisw]

Hi @LineMoon,

In order to use the IMPORT and EXPORT procedures to read and write Microsoft Excel files requires the SAS/ACCESS to PC Files module. This maybe the reason why you are not able to open the .xlsx file.

Thanks,

Samantha

[LineMoon]

@sabisw : Thank you.

But, why I can open .xls file, when I use ods with proc print ? but I can create .xlsx file, but I can not open it ?

[Cynthia_sas · Accepted solution]

Hi:
TAGSETS.EXCELXP creates a file that MUST be named either .XML or .XLS (and Excel will complain if you call it XLS because it is really an XML file).

The "new" .XLSX file type introduced with Excel (like the new DOCX and PPTX) is a proprietary XML file type that is really a zip archive that defines the workbook in XML format. The XLSX extension should NOT be used with TAGSETS.EXCELXP. The "flavor" of XML created by TAGSETS.EXCELXP is Spreadsheet Markup Language XML from Office 2003. The "flavor" of the XLSX file is Open Office XML as specified in Office 2007 -- the 2 file formats (.XML versus .XLSX) are completely different. Try using this:

ods tagsets.excelxp file='K:/JM/multisheet.xml' options( sheet_name='Test_1' );
proc print data=sashelp.class;
run;

ods tagsets.excelxp options(sheet_name='Cars');
proc print data=sashelp.cars(obs=5);
run;
ods tagsets.excelxp close;

using an XML file extension and the file should open for you correctly.

The new (SAS 9.4) destination ODS EXCEL is the ONLY destination that creates a true zip archive XLSX file that conforms to the Microsoft specification. But since you are running 9.2, you will not be able to use ODS EXCEL.

cynthia

[LineMoon]

Thank you.