topic Re: Data import: how to allow for "NA" values in a numeric variable? in SAS Programming

Data import: how to allow for "NA" values in a numeric variable?

xyxu — Wed, 01 Feb 2023 23:26:40 GMT

I am trying to import data from a CSV file using data step infile command. One numeric variable occasionally has "NA" values. This generates error messages like "NOTE: Invalid data for XXX in line 317 45-46." This doesn't affect the imported dataset but is annoying.

Is there a way to tell SAS to just keep "NA" as missing?

Re: Data import: how to allow for "NA" values in a numeric variable?

SASKiwi — Wed, 01 Feb 2023 23:30:48 GMT

Have you checked your imported data because that will be what it is doing anyway - assigning a missing value because you can't store the characters "NA" in a numeric variable.

Re: Data import: how to allow for "NA" values in a numeric variable?

xyxu — Wed, 01 Feb 2023 23:38:30 GMT

Yes, in the imported data, the value is indeed missing. I want SAS to stop generating error messages on data type mismatch in the import process.

Re: Data import: how to allow for "NA" values in a numeric variable?

SASKiwi — Thu, 02 Feb 2023 00:06:20 GMT

Why? Then you wont be aware of type mismatch errors in your imported data. Perhaps a better way would be to read the offending column as character then convert it to a new numeric column if it is not "NA". Then you can see exactly what gets imported and what gets converted.

Re: Data import: how to allow for "NA" values in a numeric variable?

xyxu — Thu, 02 Feb 2023 00:25:11 GMT

Yeah the warning messages trigger my OCD.. Importing as char first works, but it is painful if there are many such columns.

Re: Data import: how to allow for "NA" values in a numeric variable?

Tom — Thu, 02 Feb 2023 03:38:16 GMT

The best way is to use a proper INFORMAT for those fields. (Note: Do NOT use PROC IMPORT to read CSV files. You have no control over how the variables are named or defined.)

proc format;
invalue na (upcase)
  'NA'=.n
  other = [32.] 
;
run;

data test;
  input @1 good na. @1 bad ;
cards;
1
1.2
na
1E2
NA
.
;

proc print;
run;

OBS     good     bad

 1       1.0      1.0
 2       1.2      1.2
 3        N        .
 4     100.0    100.0
 5        N        .
 6        .        .

You could also just convert all of the NA strings to normal missing value (but beware if one of the fields is supposed to contain chemical compounds, it might convert all of the Sodium references to missing.)

Perhaps best to create a NEW file so you can see what types of fixes were done.

For short lines (<32K bytes) this should work.

data _null_;
  infile 'bad.txt' ;
  file 'good.txt';
  input ;
  _infile_ = tranwrd(cats(',',_infile_,','),',NA,',',.,');
  _infile_ = substrn(_infile_,2,length(_infile_)-2);
  put _infile_;
run;

Do longer lines do it cell by cell.

Re: Data import: how to allow for "NA" values in a numeric variable?

Kurt_Bremser — Thu, 02 Feb 2023 05:57:04 GMT

Concur with @Tom . Use a custom informat to catch expected special missing values. If other values appear, you'll still be notified.