TAGSETS.EXCELXP

[Ronein]

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

[PaigeMiller]

1. I always use .XLSX files
2. Use ODS EXCEL

[Kurt_Bremser]

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

[Ronein]

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]

---

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

---

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