topic Re: import xls with numeric/characters data in SAS in SAS Programming

import xls with numeric/characters data in SAS

JohnChen_TW — Wed, 22 Mar 2017 03:24:00 GMT

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?

Re: import xls with numeric/characters data in SAS

Reeza — Wed, 22 Mar 2017 03:34:43 GMT

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.

Re: import xls with numeric/characters data in SAS

JohnChen_TW — Wed, 22 Mar 2017 05:17:43 GMT

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

Re: import xls with numeric/characters data in SAS

andreas_lds — Wed, 22 Mar 2017 08:05:13 GMT

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.

Re: import xls with numeric/characters data in SAS

Kurt_Bremser — Wed, 22 Mar 2017 08:25:31 GMT

When using TAGSETS.EXCELXP, always use the proper filename extension (.xml).

That prevents mixups as you have just experienced.

Excel usually complains nowadays when the filename extension does not match the file contents.

Re: import xls with numeric/characters data in SAS

JohnChen_TW — Wed, 22 Mar 2017 09:18:29 GMT

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

Re: import xls with numeric/characters data in SAS

JohnChen_TW — Wed, 22 Mar 2017 09:32:55 GMT

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

Re: import xls with numeric/characters data in SAS

Reeza — Wed, 22 Mar 2017 09:59:26 GMT

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.

Re: import xls with numeric/characters data in SAS

JohnChen_TW — Wed, 22 Mar 2017 10:40:53 GMT

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

Re: import xls with numeric/characters data in SAS

Kurt_Bremser — Wed, 22 Mar 2017 11:44:31 GMT

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.

Re: import xls with numeric/characters data in SAS

ballardw — Wed, 22 Mar 2017 14:49:34 GMT

Why are you re-importing results from SAS output? Do you mean that you with your system create output with Tagsets. Excelxp and then turn around and import that? Why?

Re: import xls with numeric/characters data in SAS

JohnChen_TW — Thu, 23 Mar 2017 02:07:39 GMT

ballardw, thanks for your asking.

The reason is that there will two excel files need to be created by two persons from one source dataset which was arranged first, and then outputted by PROC REPORT statement, respectively.

After that I will import these two excel files and verify them to ensure the data was correctly transferred from the source dataset.

So, that is why I need to import excel files back again.

Re: import xls with numeric/characters data in SAS

JohnChen_TW — Thu, 23 Mar 2017 02:28:46 GMT

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

Re: import xls with numeric/characters data in SAS

Kurt_Bremser — Thu, 23 Mar 2017 07:09:36 GMT

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?

Re: import xls with numeric/characters data in SAS

JohnChen_TW — Thu, 23 Mar 2017 09:39:52 GMT

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;

Re: import xls with numeric/characters data in SAS

Kurt_Bremser — Thu, 23 Mar 2017 09:49:38 GMT

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?

Re: import xls with numeric/characters data in SAS

JohnChen_TW — Thu, 23 Mar 2017 10:00:49 GMT

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

Re: import xls with numeric/characters data in SAS

Kurt_Bremser — Thu, 23 Mar 2017 10:16:03 GMT

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.

Re: import xls with numeric/characters data in SAS

JohnChen_TW — Thu, 23 Mar 2017 10:23:37 GMT

Thanks for your explanation.
So, 'libname xlsx' or 'proc copy' can be used with proc report, am I right?

Re: import xls with numeric/characters data in SAS

Kurt_Bremser — Thu, 23 Mar 2017 10:50:33 GMT

@JohnChen_TW wrote:
Thanks for your explanation.
So, 'libname xlsx' or 'proc copy' can be used with proc report, am I right?

NO! Proc report creates reports, which are no suited for re-importing. Proc copy copies data. Data can be re-imported and compared.