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?
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.
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.
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.
Concur with @Tom . Use a custom informat to catch expected special missing values. If other values appear, you'll still be notified.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.