05-07-2017 07:23 AM
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);
05-07-2017 09:45 AM
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.
05-07-2017 10:00 AM
05-07-2017 01:09 PM
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.