BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

Hello

I have some questions:

1-Using TAGSETS.EXCELXP to export data from sas to excel.

What is the different between .XLS  file and  .XML file?

Is it recommended to use .XLS  file  or   .XML file?

n

2-What is the best way to export formatted tables created in sas to excel?

 

 

thanks

Ronein

5 REPLIES 5
PaigeMiller
Diamond | Level 26
  1. I always use .XLSX files
  2. Use ODS EXCEL
--
Paige Miller
Ronein
Meteorite | Level 14

please look at this example

the file name is  .xls and not .xml as you said

 

 

ODS tagsets.excelXP options (sheet_name='disease by sex')

file= "C:\Temp\SASSI\examp10a.xls";

 

 

PROC TABULATE data=sassi.examp10a;

CLASS disease sex;

TABLES disease, sex*n;

RUN;

 

ODS tagsets.excelxp close;

Reeza
Super User

@Ronein wrote:

please look at this example

the file name is  .xls and not .xml as you said

 

 

ODS tagsets.excelXP options (sheet_name='disease by sex')

file= "C:\Temp\SASSI\examp10a.xls";

 

 

PROC TABULATE data=sassi.examp10a;

CLASS disease sex;

TABLES disease, sex*n;

RUN;

 

ODS tagsets.excelxp close;


The file name and extension is set by the user. The file generated by ODS TAGSETS.EXCELXP is an xml file regardless of the extension. The rational for naming it xls is to basically trick Excel into opening the file, though it's starting to run into issues with newer versions of Excel. It's an older technology that works still, but ODS EXCEL will overtake it soon enough.

 

Excel files (xslx) are simply zipped xml. You can change the extension to .zip, unzip and see the components if desired. 

 

 

ballardw
Super User

@Ronein wrote:

please look at this example

the file name is  .xls and not .xml as you said

 

 

ODS tagsets.excelXP options (sheet_name='disease by sex')

file= "C:\Temp\SASSI\examp10a.xls";

 

 

PROC TABULATE data=sassi.examp10a;

CLASS disease sex;

TABLES disease, sex*n;

RUN;

 

ODS tagsets.excelxp close;


The extension in the windows world may be set so that a specified program will attempt to open the file. When the actual contents is different than the associated program expects the results may be undesired. In the example you have if change the file to

file= "C:\Temp\SASSI\examp10a.RTF" then Word (most likely will attempt to open the file. Try it (changing only the XLS to RTF or DOC). Then try with DOCX.

Then try with PDF. Then HTML.

 

Note that different programs or expectations generate different types of errors or partially legible results. Moral of the story: Do not lie to your computer and use the correct file extension. You can set Excel to be the default program for opening XML and things will generally work just fine and no annoying messages about mismatched names or content conversion.

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
  • 5 replies
  • 927 views
  • 4 likes
  • 5 in conversation