BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LineMoon
Lapis Lazuli | Level 10

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ
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

8 REPLIES 8
LineMoon
Lapis Lazuli | Level 10

@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
Super User

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.

sabisw
SAS Employee

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
Lapis Lazuli | Level 10

@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
SAS Super FREQ
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
Lapis Lazuli | Level 10

Thank you.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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