topic Re: Exporting data set into .xlx s in SAS 9.2 in SAS Programming

Exporting data set into .xlsx in SAS 9.2

LineMoon — Mon, 02 Jan 2017 22:17:30 GMT

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.

Re: Exporting data set into .xlx s in SAS 9.2

Kurt_Bremser — Mon, 02 Jan 2017 21:43:45 GMT

9.2 can only create xls format files, IIRC.

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

Re: Exporting data set into .xlx s in SAS 9.2

sabisw — Mon, 02 Jan 2017 21:45:46 GMT

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

Re: Exporting data set into .xlx s in SAS 9.2

LineMoon — Mon, 02 Jan 2017 22:15:04 GMT

@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 ?

Re: Exporting data set into .xlx s in SAS 9.2

LineMoon — Mon, 02 Jan 2017 22:28:48 GMT

@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;

Re: Exporting data set into .xlx s in SAS 9.2

Kurt_Bremser — Tue, 03 Jan 2017 00:04:35 GMT

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.

Re: Exporting data set into .xlx s in SAS 9.2

Kurt_Bremser — Tue, 03 Jan 2017 00:06:26 GMT

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

Re: Exporting data set into .xlx s in SAS 9.2

Cynthia_sas — Tue, 03 Jan 2017 02:29:48 GMT

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

Re: Exporting data set into .xlx s in SAS 9.2

LineMoon — Tue, 03 Jan 2017 11:59:02 GMT

Thank you.