topic Invalid Data Error on Set with Atypical Blanks in SAS Programming

Invalid Data Error on Set with Atypical Blanks

bonedog — Tue, 27 Aug 2019 19:49:10 GMT

Greetings,

I am trying to upload a large set from a .txt file. If numeric data does not exist in a cell it has been filled in with '--' so SAS is getting confused when trying to read in my numeric variables.

My code is below:

DATA ID2175;
	infile 'C:\Documents\2175.Txt' firstobs = 2;
	input Days 1-4 @6 Date mmddyy10. @17 Milk @23 Cond @29 AMT @34 Activity @43 Weight;
RUN;

I have tried using both missover and compress, but with both I still get the error below:

NOTE: Invalid data for Milk in line 20 17-22.
NOTE: Invalid data for Cond in line 20 23-24.
NOTE: Invalid data for AMT in line 20 29-30.
NOTE: Invalid data for Activity in line 20 34-35.
NOTE: Invalid data for Weight in line 20 43-44.

20  CHAR  18  .06/18/2012.--   .--   .--  .--      .--    . 49
    ZONE  3322033233233330222220222220222202222222202222220
    NUMR  1800906F18F20129DD0009DD0009DD009DD0000009DD00009
Days=18 Date=06/18/2012 Milk=. Cond=. AMT=. Activity=. Weight=. _ERROR_=1 _N_=19

Any recommendations how I can correct this? Ideally I would replace -- with . to make a blank SAS recognized but I haven't been able to do this with an if/then statement either.

Re: Invalid Data Error on Set with Atypical Blanks

ballardw — Tue, 27 Aug 2019 20:17:11 GMT

Proc format with a custom informat to rescue:

proc format library=work;
invalue mymess
'--'=.
other= [best32.];
run;

data example;
   input x :mymess.;
datalines;
1
123
1.4E8
--
.0000123
123.456789
;
run;

The above assigns the SAS missing value. Other values could be assigned is that seemed to make sense.

Easiest might be to add in INFORMAT statement to you data step such as

informat Milk  Cond AMT  Activity Weight mymess.;

prior to the input statement.

Re: Invalid Data Error on Set with Atypical Blanks

bonedog — Tue, 27 Aug 2019 20:41:43 GMT

With a couple extra informats this worked! Thanks!

Re: Invalid Data Error on Set with Atypical Blanks

ballardw — Tue, 27 Aug 2019 20:58:52 GMT

@bonedog wrote:
With a couple extra informats this worked! Thanks!

I was kind of wondering if there were different lengths of -- and ------ or such in the actual data for different variables.

Informats are VERY powerful once you get the idea down. I use them for text that should be consistent but the data providers will randomly insert characters or change agreed upon values like "NA" to "N/A". I use custom informats to read the expected values and use an "other=_error_" as the last element of the format. That way I get errors thrown in the log and can modify the Informat so that both "NA" and "N/A" get mapped to the same code value in the data.