Reading in variables as numeric from xlsx

[lhinrichs]

Hello

I am a very new to SAS on demand for academics. I have imported my data from excel, all variables are numeric. However, SAS is reading all in as a character. Is there a way to tell SAS to import the columns as numeric? Or am I going to have to convert my many variables separately? Thank you! I appreciate your time 

[ballardw]

Show the code you use to read the data.

If you are using Excel data that has multiple header rows then the text in the second or third or whatever head tells SAS the column has mixed data. Since SAS only has numeric and character types it goes with character to preserve the data.

If this sounds likely then edit the file to a single row of headers.

Make sure that your "numeric" columns do not have any values like "null" "NA" "missing" or similar text in the column as that will also cause this issue. And unfortunately because Excel allows anything in any cell what looks like a number may actually be text.

You may have better luck saving the file as CSV from Excel and read that. If you are using Proc Import to read the data you can specify more options with a text file such as DATAROW statement to specify which row of the file has the first actual row of data.

[Ksharp]

Check option DBSASTYPE.

libname x excel 'c:\temp\temp.xlsx';
data want;
 set x.temp(dbsastype=(ssssss='num'));
run;

[Kurt_Bremser]

Save as csv, and read that with a data step where you control the variable attributes. Then, when you execute your step, invalid data will cause NOTEs in the log, which enables you to deal specifically with such values (e.g. by creating a custom informat that reads the string "N/A" as a missing value).

All tools that you have in SAS for reading Excel rely on making guesses about content, with inconsistent results.

See Maxims 22, 27 and 31.

[Tom]

SAS will make the variable character if any cell in that column contains character strings.

So check the XSLX file and make sure you do not have non-numeric text in any of the cells (other than the header row which should be characters strings only).