03-21-2017 11:24 PM
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?
03-21-2017 11:34 PM
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.
03-22-2017 01:17 AM - edited 03-22-2017 01:17 AM
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;
03-22-2017 04:05 AM
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.
03-22-2017 05:59 AM
03-22-2017 06:40 AM
03-22-2017 07:44 AM
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.
03-22-2017 10:28 PM
03-23-2017 03:09 AM
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?
03-23-2017 05:34 AM - edited 03-23-2017 05:39 AM
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;
03-23-2017 05:49 AM
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?
03-23-2017 06:00 AM
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).
03-23-2017 06:16 AM
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.