topic Re: TAGSETS.EXCELXP in SAS Programming

TAGSETS.EXCELXP

Ronein — Thu, 05 Apr 2018 14:18:26 GMT

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?

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

thanks

Ronein

Re: TAGSETS.EXCELXP

PaigeMiller — Thu, 05 Apr 2018 14:31:34 GMT

I always use .XLSX files
Use ODS EXCEL

Re: TAGSETS.EXCELXP

Kurt_Bremser — Thu, 05 Apr 2018 14:47:10 GMT

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

Re: TAGSETS.EXCELXP

Ronein — Thu, 05 Apr 2018 14:52:52 GMT

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;

Re: TAGSETS.EXCELXP

Reeza — Thu, 05 Apr 2018 15:05:46 GMT

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

Re: TAGSETS.EXCELXP

ballardw — Thu, 05 Apr 2018 21:40:54 GMT

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