PROC IMPORT not reading Excel column formats correctly

[Rovmenpaul]

Hi Everyone,

I’m importing an Excel file using PROC IMPORT in SAS 9.4, but the numeric columns with decimal values are being read as character or getting truncated. I’ve tried using DBSASLABEL=NONE and even increased the GUESSINGROWS, but the issue still happens.

Some columns mix numbers and text, but others are just decimals and they still don’t import cleanly.

Is there a better way to control the column types during import, or is it better to switch to libname with XLSX?

Thanks!

[andreas_lds · Accepted solution]

If a column contains text and number, it will always be imported as text, because there is no other way to maintain as much of the content as possible. Columns containing only numbers should result in a numeric variable. Problems could be caused by multi-row headers and other fancy stuff that should not exists in data-sources.

Afaik libname uses the same mechanics, so it won't help, most likely. 

Can you show the code used to read the file? Are there any interesting notes/warnings in the log? 

[PaigeMiller]

Better to switch to CSV or tab delimited, then the log from PROC IMPORT contains code that you can then modify so that it works better, and run again if necessary.

[Tom]

You should examine the cells in those columns in the XLSX file.  Most likely at least one of them has a character string instead of a number in it.

When a column has mixed types of cells then SAS will make it as character.

When a column is completely empty then SAS will make it as character (a one byte character variable uses less storage space than a number in a SAS dataset).

If the XLSX file consistently has character cells when the values have decimal places then perhaps the process that created the file has trouble with decimal places.  Perhaps you have locale setting issues and Excel was expecting commas as decimal place character instead of periods (or the reverse) and that is what caused it to make those cells as character.

[Kurt_Bremser]

Save the spreadsheet to a CSV file and read that with a DATA step.

[Tom]

---

@Rovmenpaul wrote:

Hi Everyone,

I’m importing an Excel file using PROC IMPORT in SAS 9.4, but the numeric columns with decimal values are being read as character or getting truncated. I’ve tried using DBSASLABEL=NONE and even increased the GUESSINGROWS, but the issue still happens.

Some columns mix numbers and text, but others are just decimals and they still don’t import cleanly.

Is there a better way to control the column types during import, or is it better to switch to libname with XLSX?

Thanks!

---

Please provide more details as your word choices are ambiguous.

What do you mean by FORMAT?  That word as a special meaning in SAS.  A FORMAT is instructions for how to convert values into text which can be used for printing.  So the DATE9. format specification will interpret a number as the number of days since 1960 and display in as a 9 character string in the style of DDMONYYYY.

Or do you mean the TYPE of the variable? SAS only has two types of variables. Fixed length character strings and floating point binary numbers.

What do you mean by DECIMAL?  Do you mean real numbers that are not also whole numbers?  Or do you mean numeric values stored using base 10 numbers instead of the normal binary floating point numbers used by most computer systems?

What do you mean by TRUNCATE?  Do you mean to shorten a string? As in truncating a string of 12 characters into only the first 8 characters?  Or you do mean rounding down?

For example it might be that your issue is that you original data was using large base 10 numbers (DECIMAL data type in many database systems) that have more digits that SAS can store precisely as a number.  In that case you might see values that get displayed using scientific notation (1.23E9) and so might appear to humans as character strings if they weren't expecting them.  And since 64 but floating point numbers can only store 15 decimal places precisely large digit strings might appear to have been rounded down to the nearest power of 2 and so appear "truncated".

[Ksharp]

Here is a way to control the column types during import.

proc import datafile = "c:\temp\date.xlsx" out =have replace dbms =excel ;
dbdsopts="dbsastype=(age='numeric' weight='char(20)')";
run;

[Tom]

---

@Ksharp wrote:

Here is a way to control the column types during import.

proc import datafile = "c:\temp\date.xlsx" out =have replace dbms =excel ;
dbdsopts="dbsastype=(age='numeric' weight='char(20)')";
run;

---

DBMS=EXCEL only works if you are running SAS on Windows.  And you have a compatible version of EXCEL installed on the same machine.

[Rovmenpaul]

---

@Edulearnx wrote:

Hi Everyone,

I’m importing an Excel file using PROC IMPORT in SAS 9.4, but the numeric columns with decimal values are being read as character or getting truncated. I’ve tried using DBSASLABEL=NONE and even increased the GUESSINGROWS, but the issue still happens.

Some columns mix numbers and text, but others are just decimals and they still don’t import cleanly.

Is there a better way to control the column types during import, or is it better to switch to libname with XLSX?

Thanks!

---

Thank you for all 👍 my issue got solved..
Regards, Rovmen