How do I convert "NULL" values when importing a data set into missing values on SAS?

[Leanna]

I'm a very new SAS user, and I'm trying to import a data set of over 41,000 values into Base SAS 9.4. The data set is saved in a csv format. Many hundreds of these data points are missing entries and have the value "NULL" written in an otherwise numerical set of values. When I try and import the data using the Import Data tool it comes up with many of these error messages:

"NOTE: Invalid data for v_child in line 732 147-150."

And then I get this warning message:
"WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed."

Followed by this:

"NOTE: 41817 records were read from the infile 'J:\ABU Dataset 2010 to 2018.csv'.
The minimum record length was 696.
The maximum record length was 1588.
NOTE: The data set SASDATA.ABU has 41817 observations and 145 variables.
NOTE: DATA statement used (Total process time):
real time 30.07 seconds
cpu time 1.28 seconds

Errors detected in submitted DATA step. Examine log.
41817 rows created in SASDATA.ABU from J:\ABU Dataset 2010 to 2018.csv.

ERROR: Import unsuccessful. See SAS Log for details."

How can I fix this so that I can import the data successfully? Thank you in advance!

[Tom]

If you can get the person that created the file to create it in a more useful form.  The standard for delimited files (like a CSV) is to just leave no space between the delimiters when the value is "null".

You can make your own INFORMAT for numeric variables that will treat NULL as missing.

Example:

proc format ;
invalue null (upcase)
  'NULL'=.
  other=[32.]
;
run;

data want;
  infile cards dsd truncover ;
  input id :$5. (v1-v3) (:null.) ;
cards;
a,1,null,3
b,NULL,5,6
c,,8,
;

proc print;
run;

Obs    id    v1    v2    v3

 1     a      1     .     3
 2     b      .     5     6
 3     c      .     8     .

[ballardw]

Another option is to use the ?? error message suppressor on the input statement:

data example ;
   input x ??;
datalines;
1 
2
null
3
;

I would probably use the custom informat because the above approach suppresses all error messages and you might want them for values that appear other than NULL. Such as when someone changes the order of variables in the file and what was a numeric field is aligning with a new character field.

[Astounding]

If these are the only messages, you don't have to do anything.  Your data is fine as is.  The messages are annoying but not critical.

The issue is this.  When a numeric field contains "NULL", what do you want SAS to do?  Well, if the variable is to be numeric, SAS can't store "NULL" as the value.  SAS changes it to a missing value instead, which is a decent outcome.  Did you have a different outcome in mind?

Check a few lines that generated these messages, and see if you are happy with the result.