import xls with numeric/characters data in SAS

[JohnChen_TW]

HI, I'm using SAS 9.3 to import xls file in SAS. Before this, I exported excel file by "ODS TAGSETS.EXCELXP" from SAS and the type of all excel files will be "xls". The question is that the character data in the variable with both num and char type will be transferred to "." when I importing it. However, I have tried to use "GUESSINGROWS" but it did not work...because the default of rows in xls file is 8.

Also I have searched another way to resolve this question from wedsite is to change xls file into csv. But I think it will make procedure more complicated (maybe).

Does anyone has any suggestion?

[Reeza]

Files exported using Tagsets.ExcelXP aren't suited to being imported again - for one they're xml not xls. Assuming you've actually converted the files to xls then check the MIXED=YES option, the default is NO.

GuessingRows is not a valid option for Excel files or at least it won't work as expected, it needs to be set in the registry.

[JohnChen_TW]

Thank you Reeza,
The file which I converted by "ODS TAGSETS.EXCELXP FILE='xxx.xls'" is created by "PROC REPORT statement" first.
Yes, I imported the xls files with MIXED=YES (code as below) but I need to open it to import. And this is an another trouble.

Import code: PROC IMPORT DATAFILE="xxx.xls" OUT=yyy REPLACE ;MIXED=Yes;SHEET="Sheet"; RUN;

John

[andreas_lds]

What @Reeza with "converted" meant, was that you have to open the file in Excel and save it as xls/xlsx file manually. Only then it can be imported again.

[JohnChen_TW]

Okay, thanks for your explanation. Now, I understand what "converted" means.

[JohnChen_TW]

Actually, it's not that I export excel file but export all outputs to excel file by "ODS TAGSETS.EXCELXP".

[Reeza]

Tagsets.excelxp DOES NOT create an XLS file, it creates an XML file. This is the default, you can change the extension but the underlying file is still xml.

[JohnChen_TW]

I do not understand what you mean. It can save output as excel.xls indeed.

Code:
ods tagsets.excelxp file="C:\Users\john.chen\Desktop\spacing.xls";

proc sort data=sashelp.class out=class;
by age;
run;

proc print data=class;
by age;
run;

ods tagsets.excelxp close;

JC

[Kurt_Bremser]

ods tagsets.excelxp file="C:\Users\john.chen\Desktop\spacing.xls";

This does NOT create an Excel file, it creates an incorrectly named XML file. To verify that, just open it with a text editor, and then try to do the same with a .xls file saved from Excel. This is why Excel later complains when opening such an incorrectly named file (at least the recent versions of Excel do).

The content of this file is XML code which is kind of a precursor to the XML that is now used in .xlsx files. But these XLSX files are also zip-compressed when saved, while the output from TAGSETS.EXCELXP is not.

[JohnChen_TW]

Yes, your are right.
I tried to open an XLS file created by 'ods tagsets.excelxp' with text editor and the format of data is matched with XML code.
Sorry, I do not understand what the TAGSETS.EXCELXP really do.
Bear with me, still learning - if the file created by TAGSETS.EXCELXP is not XLS file for real. Why I could import it by PROC IMPORT DATAFILE="xxx.XLS"?.

JC

[Kurt_Bremser]

proc import for xls files implements a module supplied by Microsoft that converts tabular data from Excel files into a standardized format, which is then imported into SAS. This module can basically open everything that Excel itself can open, but it has its limitations, as you experienced.

From another post of yours I have the impression that you use Excel just as some kind of glorified input tool. If that guess is right, wouldn't you be better off by using a stored process and HTML input forms?

[JohnChen_TW]

Could you give a specific example of stored process and HTML input forms or how to export output to a real excel? I have no idea how to create another kind of files to stored my output instead of using TAGSETS.EXCELXP. Furthermore, the format of these files have to match the corresponding PDF file.

Please see the following code and attachments as PDF file and XML file:

data class; set sashelp.class; run;

/*=== PDF and XLS(incorrectly named XML) ===*/
ODS TAGSETS.EXCELXP FILE='C:\Users\john.chen\Desktop\Try.xls'; 
PROC REPORT data=class nowd
split= ',';
columns Name Sex Age Height Weight;
define Name      /center 'Name';
define Sex       /center 'Sex' '';
define Age       /center 'Age,(years)';
define Height    /center 'Height,(feet)';
define Weight    /center 'Weight,(lb)';

ods printer pdf file="C:\Users\john.chen\Desktop\Try.pdf"; run;
ods printer pdf close;
ODS TAGSETS.EXCELXP CLOSE;
run;

[Kurt_Bremser]

I wanted to take a view on your whole process. What is being done with the Excel file between export and import? What data is added to it?

Does it deal with whole tables, or are single lines added?

[JohnChen_TW]

After that I would use the following code to import it in SAS.

proc import datafile="C:\Users\john.chen\Desktop\Try.xls" out=Try replace; mixed=yes; sheet="Table 1 - Detailed and or summa"; run;

This is the whole process to output data in PDF and XLS file, and then import it to SAS again to check if the data was correctly trasferred from the source data (sashelp.class).

[Kurt_Bremser]

It only makes sense to check for correct output when you export DATA only. When you use tagsets.excelxp to create Excel style formatting, you change/expand the data so that it becomes un-importable.

You might be better off using libname xlsx, use proc copy to copy data into a sheet in the xlsx, and then run proc compare on that.