BookmarkSubscribeRSS Feed
xyxu
Quartz | Level 8

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?

6 REPLIES 6
SASKiwi
PROC Star

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.

xyxu
Quartz | Level 8
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.
SASKiwi
PROC Star

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.

xyxu
Quartz | Level 8
Yeah the warning messages trigger my OCD.. Importing as char first works, but it is painful if there are many such columns.
Tom
Super User Tom
Super User

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.

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 2149 views
  • 0 likes
  • 4 in conversation