Why are Negative numbers classified as character variable SAS Studio

[stinkydog]

Hi,

I am using SAS University edition, the most recent version.  

I am having trouble with a data set that contains variable columns that have negative numbers.  These numbers are from an excel spreadsheet.  When I input the data set by double clicking on it, then run the program generated, the variable columns with negative numbers are classified as character instead of numeric. I have attached a copy of the dataset.

This is the code that is automatically generated when I double click on the TAMDATA.xlsx file in myfolders:

/* Generated Code (IMPORT) */
/* Source File: TAMDATA.xlsx */
/* Source Path: /folders/myfolders */
/* Code generated on: 5/7/17, 3:19 PM */
%web_drop_table(WORK.TAMDATA);

FILENAME REFFILE '/folders/myfolders/TAMDATA.xlsx';
PROC IMPORT DATAFILE=REFFILE
DBMS=XLSX
OUT=WORK.TAMDATA;
GETNAMES=YES;
RUN;
PROC CONTENTS DATA=WORK.TAMDATA; RUN;

%web_open_table(WORK.TAMDATA);

[Reeza]

It likely has nothing to do with negative numbers. More likely you have blanks in some cells. Make sure the first 10 rows have numbers. 

[stinkydog]

So, I figured it out. It was similar to what you said. It had nothing to do with the negative numbers. About 40 lines down I have some missing information. When I used SAS Studio through SAS University edition about a year and a half ago, as well as previous older SAS editions, missing information needed to be dealt with by using "." Or something like it. With this version I deleted the "." And just left the cells blank and SAS recognized the columns of negative number values as Numeric variables. Now it seems to be working fine. I did not realize this update cleared up the issue with blank spaces.

Thanks for your help!

[Tom]

There is a difference between reading from a XLSX file where SAS can check how the cell is defined and reading from a text file. When reading from a text file if you are doing a simple list more input statement (input var1 var2 varr3) then you need to have something other than spaces for the missing values otherwise SAS will read the other numbers into the wrong column. So the period serves this purpose.  

But to store a period in a Excel file you need to make the cell be a character string. So when SAS read the Excel sheet with periods in it had to make the variable character to be able to store the strings that were in some of the cells in the column.