How to avoid converting a numeric column to a character variable with the LIBNAME EXCEL statement?

[Peter_Boonants]

Dear Sir,

Dear Madam,

When I execute

LIBNAME ExcInv EXCEL "&Werkmap\Invoer.xls";

in SAS 9.4, the Getal column in the Excel sheet Tabel2 of the Excel file Invoer.xls with only numeric values and missing values (empty cells) is converted into a character variable. (So, contrary to what I want, the variable Getal in ExcInv."Tabel2$"n is a character variable, not a numeric variable.)

You will find herewith a copy of the Excel file Invoer.xls that is located in the &Werkmap folder.

On the other hand, the Getal column in the Excel sheet Tabel1, which also contains only numeric values and missing values (empty cells), is transferred to a numeric variable as desired.

Here is an illustration of the result:

Result of LIBNAME ExcInv EXCEL "&Werkmap\Invoer.xls";

I do think that when running the same command in SAS 9.3, both the Getal column in the Tabel1 sheet and the Getal column in the Tabel2 sheet are converted to a numeric variable. I cannot test this myself now, as I no longer have SAS 9.3 at my disposal.

However, is there a possibility in SAS 9.4 to transfer the Getal column in the Excel sheet Tabel2 to a numeric variable with the LIBNAME statement using the EXCEL engine?

I know I get through

DATA Ds2; SET ExcInv."Tabel2$"n (DBSASTYPE = (Getal = NUMERIC)); RUN;

or

PROC IMPORT DATAFILE = "&Werkmap\Invoer.xls" OUT = Ds2 DBMS = XLS REPLACE; SHEET = "Tabel2"; RUN;

the desired result in Ds2, but the intention is to make sure that the variable Getal in ExcInv."Tabel2$"n is also numeric immediately after executing the LIBNAME statement using the EXCEL engine.

Can somebody help me?

Thanks in advance.

Peter Boonants

Statbel (Statistics Belgium)

[Tom]

I don't see any difference in the structure PROC IMPORT creates for your two sheets.

%let fname = c:\downloads\Invoer.xls ;

proc import datafile="&fname" dbms=xls out=test replace;
run;

proc contents data=test varnum; run;


proc import datafile="&fname" dbms=xls out=test2 replace;
 sheet='Tabel2';
run;

proc contents data=test2 varnum; run;

proc compare data=test(obs=0) compare=test2(obs=0);
run;

In general if you want to control the definition of your data do not use an unstructured format like Excel files.

Either generate a structured format (SAS dataset, SPSS dataset , etc) or just generate a text file and write your own program to read it so that you have full control over how the dataset is generated.

[ChrisNZ]

> On the other hand, the Getal column in the Excel sheet Tabel1, which also contains only numeric values and missing values (empty cells), is transferred to a numeric variable as desired.

SAS makes its decision based on what it finds in  Excel. What's the difference between these columns?

Make sure the clean the values and to format all the cells properly.

I can't check your fil as downloading MS Office files from in known sources is not possible nor wise here.

[ballardw]

Your picture of Table1$ shows periods in the column getal. Are there actually periods in the XLS file? The engines for reading from XLS will treat periods without any numerals as text generating the result you see.

I am not going to open your XLS for a number of reasons.

Almost any process that relies on getting consistent results from any of the XLS, XLSX files without an explicit code to parse the values, such as saving as CSV and reading with a data step or using a data step to process columns using similar input from column  to variable is very likely to have problems because the original file formats do not have any restrictions on the columns and garbage in the data interferes with clean conversion to SAS variables.

[Peter_Boonants]

Hey everyone,

Many thanks to everyone who replied for the interesting answers and helpful advice.

In the meantime, I have investigated the matter a little more deeply and have come to the conclusion that whether or not putting the Excel column headings in bold has an influence. (Strange, but apparently true!)

I would also like to emphasize that I have executed everything in SAS 9.4. Running the code in a different version of SAS (such as SAS 9.3) may give a different result.

Of course I understand very well that the Excel file that I added last Tuesday was not opened for security reasons. But, don't worry, you can easily construct it yourself (except for a certain formatting) by executing the following SAS code, provided that you have first chosen the desired working directory using %LET Werkmap = ...;:

%MACRO PrExport;
  %DO I = 1 %TO 2;
    PROC EXPORT DATA = Tabel&I DBMS = EXCEL OUTFILE = "&Werkmap\Invoer.xls" REPLACE; RUN;
  %END;
%MEND PrExport;

%LET IDl = %STR(INPUT Id 2. Getal 1. Letter $1. Miss 1. Bool 1.; DATALINES;);
DATA Tabel1; %UNQUOTE(&IDl)
 1 A 1
 26B 1
 3 C 0
 4 D 1
 58E 0
;
DATA Tabel2; %UNQUOTE(&IDl)
 1 A 0
 2 B 0
 3 C 0
 4 D 0
 5 E 0
 6 F 0
 7 G 1
 8 H 0
 9 I 0
10 J 1
11 K 1
126L 1
13 M 0
148N 0
15 O 0
165P 1
17 Q 1
183R 0
19 S 0
20 T 1
21 U 0
228V 0
231W 1
246X 0
25 Y 0
26 Z 0
;
%PrExport

After running the SAS code above, we notice as expected that each cell of each sheet in the generated Excel file &Werkmap\Invoer.xls has the default number format general (number category general for each cell, including each cell in the Getal column). This is also the case for the attachment I added last Tuesday.

As in Tuesday's attachment, there are in &Werkmap\Invoer.xls no cells at all that contain a period.

When, with the help of the following code, the LIBNAME command is executed, we notice that we still obtain the desired result. This means, among other things, that the variable Getal is numeric in both ExcInv."Tabel1$"n and ExcInv."Tabel2$"n.

%MACRO LibnPrCont;
  LIBNAME ExcInv EXCEL "&Werkmap\Invoer.xls";
  %DO I = 1 %TO 2;
    PROC CONTENTS DATA = ExcInv."Tabel&I$"n ORDER = VARNUM; RUN;
  %END;
%MEND LibnPrCont;

%LibnPrCont

But when before executing the LIBNAME command (or the LibnPrCont macro), the column headings are (manually) set in bold in both Excel sheets, we get the odd result as I described last Tuesday. In Tuesday's attachment, column headings are also in bold in both sheets.

So, despite the same formatting in Excel for both Getal columns, we get with bold Excel column headers after executing LIBNAME ExcInv EXCEL "&Werkmap\Invoer.xls"; in SAS 9.4 a different format for the Getal variable in ExcInv."Tabel1$"n (namely numeric) than for the Getal variable in ExcInv."Tabel2$"n (namely character)!

It is that which is so remarkable.

Can it be explained why whether or not the Excel column headings are bold can affect the format in SAS?

Note that in the Tabel2 sheet, the first record with no missing value in the Getal column is the record with Id = 12, while that is the record with Id = 2 in the Tabel1 sheet.

However, if we save &Werkmap\Invoer.xls as an xlsx file, say as &Werkmap\Input.xlsx, and if we then use in SAS 9.4 in the LIBNAME statement the XLSX engine as illustrated below, we will get the desired result, namely that the variable Getal is in both XlsxInv.Tabel1 and in XlsxInv.Tabel2 numeric, regardless of whether the column headings in &Werkmap\Input.xlsx are in bold or not.

LIBNAME XlsxInv XLSX "&Werkmap\Invoer.xlsx";

So working with the XLSX engine instead of the EXCEL engine is apparently also a possible option to avoid the problem.

Many greetings from Brussels.

Peter

[Tom]

Ok. So it sounds like your problem is with Microsoft software and not with SAS.