BookmarkSubscribeRSS Feed
JohnChen_TW
Quartz | Level 8

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?

20 REPLIES 20
Reeza
Super User

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
Quartz | Level 8

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
Jade | Level 19

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
Quartz | Level 8
Okay, thanks for your explanation. Now, I understand what "converted" means.
JohnChen_TW
Quartz | Level 8

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

Reeza
Super User
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
Quartz | Level 8
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
Super User
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
Quartz | Level 8
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
Super User

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 20 replies
  • 1525 views
  • 5 likes
  • 5 in conversation