I feel silly asking this question but for some reason, I am having issue with this particular dataset when converting character to numeric. See attached for the data I am running this on. This problem seems to only be happening with PROC IMPORT. If you don't want to download the file, the basic problem is that when importing an XLSX for columns with numeric values but the numeric values have a space after it, I am unable to convert the values to numeric. PROC IMPORT DATAFILE="have.xlsx" DBMS=xlsx REPLACE OUT=have;
RUN;
DATA want;
SET have;
FORMAT col1 col2;
INFORMAT col1 col2;
col1_nothing=INPUT(col1,8.);
col1_compress=INPUT(COMPRESS(col1),8.);
col1_strip=INPUT(STRIP(col1),8.);
col1_trim=INPUT(TRIM(col1),8.);
RUN; The output is below: All of the cells that are corresponding with a cell that is a numeric value followed by a space have an end result of numeric missing. Performing COMPRESS, TRIM, STRIP does not remove this space. This error does not occur when creating a dataset within SAS and only when importing with PROC IMPORT DATA test;
test="0.14 ";
test2=INPUT(test,8.);
RUN; I know the solution is just editing the dataset but ideally, I'd like to fix this without manually editing data I am given for my position. This error is occurring when run on SAS EG 8.6, SAS 9.4 Also just a side question, I left in a COL2 that when PROC IMPORT'ed, 0.07 gets converted to "7.0000000000000007E-2". I assume this is just floating point but not sure why this happens if it's being read as character.
... View more
