SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

PROC IMPORT Problem

Reply
New Contributor
Posts: 2

PROC IMPORT Problem

Hello, after the file import with XLSX I have very strange columns behaviour - length of the actual data is equal to the length of the column, for example I try to create new integer column from string and get the following note:
NOTE: Invalid numeric data, '22247...' and new column is empty after that.

I concatenated '!!!' to all values and got the following result - '22247   !!!' - can't compress it either. Have somebody seen such a thing?

Super User
Super User
Posts: 6,381

Re: PROC IMPORT Problem

Use $HEX format to see what characters are actually in your data.

For example if you character variable is named XX and you wanted to create new variable name N from it you could code:

data new ;

set old;

n=input(xx,32.)

if n=. and x ne ' ' then put 'Invalid Number: ' xx $hex. ;

run;

New Contributor
Posts: 2

Re: PROC IMPORT Problem

Good morning, I ve got the fillowing log

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 3734373538000000

Values=74758    n=. _ERROR_=1 _N_=1

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 3632383037000000

Values=62807    n=. _ERROR_=1 _N_=2

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 3130343232310000

Values=104221   n=. _ERROR_=1 _N_=3

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 3637323431000000

Values=67241    n=. _ERROR_=1 _N_=4

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 3131303138310000

Values=110181   n=. _ERROR_=1 _N_=5

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 3534303537000000

Values=54057    n=. _ERROR_=1 _N_=6

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 3433333535000000

Values=43355    n=. _ERROR_=1 _N_=7

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 3134303033390000

Values=140039   n=. _ERROR_=1 _N_=8

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 3339343035000000

Values=39405    n=. _ERROR_=1 _N_=9

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 3931323334000000

Values=91234    n=. _ERROR_=1 _N_=10

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 3236383334000000

Values=26834    n=. _ERROR_=1 _N_=11

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 3932383131000000

Values=92811    n=. _ERROR_=1 _N_=12

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 3533323037000000

Values=53207    n=. _ERROR_=1 _N_=13

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 3739373639000000

Values=79769    n=. _ERROR_=1 _N_=14

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 3238313834310000

Values=281841   n=. _ERROR_=1 _N_=15

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 3130343831320000

Values=104812   n=. _ERROR_=1 _N_=16

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 3834310000000000

Values=841      n=. _ERROR_=1 _N_=17

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 3232323437000000

Values=22247    n=. _ERROR_=1 _N_=18

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 0000000000000000

Values=         n=. _ERROR_=1 _N_=19

NOTE: Invalid argument to function INPUT at line 46949 column 7.

Invalid Number: 0000000000000000

WARNING: Limit set by ERRORS= option reached.  Further errors of this type will not be printed.

Values=         n=. _ERROR_=1 _N_=20

Invalid Number: 0000000000000000

Invalid Number: 0000000000000000

Invalid Number: 0000000000000000

Invalid Number: 3134343936363000

NOTE: Mathematical operations could not be performed at the following places. The results of the

      operations have been set to missing values.

      Each place is given by: (Number of times) at (Line)Smiley SadColumn).

      24 at 46949:7

NOTE: There were 24 observations read from the data set WORK.FACT2014050_3.

NOTE: The data set WORK.AAAA has 24 observations and 2 variables.

NOTE: DATA statement used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

Esteemed Advisor
Posts: 6,706

Re: PROC IMPORT Problem

You've got hex null characters in your string. VERY BAD.

Make sure that in the original data these are replaced with blanks (hex 20).

Since I have no idea how to input '00'x's in Excel, I suspect that they came there from another application.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 3 replies
  • 502 views
  • 0 likes
  • 3 in conversation