DATA Step, Macro, Functions and more

TAGSETS.EXCELXP

Reply
Frequent Contributor
Posts: 95

TAGSETS.EXCELXP

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

Respected Advisor
Posts: 2,661

Re: TAGSETS.EXCELXP

  1. I always use .XLSX files
  2. Use ODS EXCEL
--
Paige Miller
Super User
Posts: 9,611

Re: TAGSETS.EXCELXP

tagsets.excelxp creates XML output, so the file should be named .xml.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 95

Re: TAGSETS.EXCELXP

Posted in reply to KurtBremser

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;

Super User
Posts: 22,874

Re: TAGSETS.EXCELXP


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

 

 

Super User
Posts: 13,084

Re: TAGSETS.EXCELXP


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

Ask a Question
Discussion stats
  • 5 replies
  • 84 views
  • 4 likes
  • 5 in conversation