Exporting data set into .xlsx in SAS 9.2

Accepted Solution Solved
Reply
Super Contributor
Posts: 371
Accepted Solution

Exporting data set into .xlsx in SAS 9.2

[ Edited ]

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.

 


Accepted Solutions
Solution
‎01-03-2017 06:57 AM
SAS Super FREQ
Posts: 8,720

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

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

View solution in original post


All Replies
Esteemed Advisor
Posts: 6,684

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

9.2 can only create xls format files, IIRC.

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 371

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

[ Edited ]

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

 

Esteemed Advisor
Posts: 6,684

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Posts: 6,684

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
SAS Employee
Posts: 3

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

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

Super Contributor
Posts: 371

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

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

Solution
‎01-03-2017 06:57 AM
SAS Super FREQ
Posts: 8,720

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

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
Super Contributor
Posts: 371

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

Thank you.

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 223 views
  • 4 likes
  • 4 in conversation