Proc import : numeric values imported as character

[SASdevAnneMarie]

Hello Experts,

I have a question, why, using the proc import, the second column is imported correctly (in numeric values), but the firts is imported with the character values ?

My code is :

proc import 
	datafile= "xxxx\calculs"
	out=PROD_diff
	replace dbms=xlsx;
	getnames=yes;
	range="Frais";
run;

I added the file.

Thank you for your help !

[ballardw]

For one or more reasons the engine reading the data thinks that at least one of the values in the first few rows of the data a character.

Without access to the XLSX file involved specifically can't tell.

Common things to look for: column header that occupies 2 or more rows

Something that is not a number in the column such as a value like "NULL" or "N/A"

Look to see if any of the "numbers" are left justified in the spread sheet. They may have been entered with an ' before the characters making them character and not numeric.

A character such a < , as might appear in a value like <0.0001

By default only the first 20 rows or so are used by Proc Import to set variable type so what ever is happening should be visible in the first 20 rows used.

[SASdevAnneMarie]

Thank you !

[Tom]

For an actual XLSX file it checks the whole column, not just some limited number of cells.

[ballardw]

---

@Tom wrote:

For an actual XLSX file it checks the whole column, not just some limited number of cells.

---

Probably still stuck in XLS thinking then.

[SASdevAnneMarie]

I added the file.

Thank you !

[Tom]

So if the whole column is empty SAS will create the variable as character with length 1.  

Is that what is causing you trouble?

[ballardw]

---

@SASdevAnneMarie wrote:
I added the file.

Thank you !

---

I'm not sure that the file you attached is the one you actually say you read. There are no values of the FRAIS GESTION OL-EURO variable that would in any reasonable way approach 0.008 (the 8.  E-3) . There are values near 0.006, 0.0096, 0.0075 and the 0.01 but nothing with an 0.008.

Long shot, make a copy of the file and clear all the formatting for all the cells and then read that file.

[Tom]

You could try checking the SAS dataset to try and figure out which cell was character.  At least which character string that did make cannot be converted into a number.

Not sure what the variable name is since you just posted a photograph of your data.  Let's use VAR as the name to demonstrate the code.  So look of observations where VAR is not empty but its value cannot be converted to a number.

proc _null_;
  set PROD_diff;
  if not missing(VAR) and missing(input(strip(var),??32.)) then put _n_= var= ;
run;

[SASdevAnneMarie]

Thank you Tom,

I can't find the proc _null_ :
ERROR: Procedure _NULL_ not found.

[Kurt_Bremser]

That is a typo. It should be

data _null_;

If you provide example data in readily usable form (data step with datalines), such mistakes can be found immediately upon testing.

[Kurt_Bremser]

There is something in that column in your range that makes IMPORT think it is not entirely numeric.

Find that first, then decide how to deal with it

• change it in the spreadsheet
• convert in a follow-on step
• save the range to a csv file which you read with a DATA step, using an informat which correctly deals with the "special" values.

[Kurt_Bremser · Accepted solution]

Well, something in your data causes PROC IMPORT to guess (because that's what it does, and - Maxim 31 - Computers Are Dumb) that these columns are character. So I did the usual remedy: avoid the crap file format (Excel) and use something sensible. I saved the spreadsheet to a csv file, uploaded it to my SAS On Demand, and ran this code:

data calculs;
infile "~/calculs.csv" dlm="," dsd truncover firstobs=2;
input
  CD_PRODUIT :$4.
  FRAIS_GESTION_OL_EURO :commax.
  FRAIS_GESTION_OL_C :commax.
  FRAIS_GESTION_OL_PROFIL :commax.
  FRAIS_GESTION_OL_CROISS :commax.
  FRAIS_GESTION_OL_ALLOC :commax.
  FRAIS_GESTION_OP :commax.
  FRAIS_GESTION_OC :commax.
;
run;

And Bob's your uncle.

[SASdevAnneMarie]

Thank you, Kurt !

That works !