coverting a character variable with number and missing values to numeric variables

[Gilles-Protais]

Thank you very much.

It Worked for me now.

[Tom]

This has been asked before.  You could try search for the previous questions and answers.

In general there are two methods.

1) Make version of the text file without the NA strings.  Then use what every method you want to read the converted file.

Example:

filename csv 'my original file.csv';
filename fixed 'my original file fixed.csv';
data _null_;
  infile csv ;
  file fixed;
  input ;
  _infile_=tranwrd(cats(',',_infile_,','),',NA,',',,');
  _infile_=substrn(_infile_,2,length(_infile_)-2);
  put _infile_;
run;

2) Write your own data step to read the data instead of forcing SAS to GUESS how to read the file.

If you write your own data step you get to decide which variables are numeric and which are character.  If you don't do anything else then SAS will write a note when it sees one of those NA strings in a field being read as a numeric value, but the result will be a missing value, which is what you want.

Reading a CSV file is TRIVIAL with a data step.  So there is no need to resort to forcing SAS to GUESS how to read it. Just define the variables. Attach any NEEDED formats or informats.  Then read the variables.

Example:

data want;
  infile csv dsd truncover firstobs=2;
  length id $10 age 8 start_date 8 ;
  informat start_date yymmdd. ;
  format start_date yymmdd10.;
  input id -- start_date ;
run;

To suppress the notes about invalid values you could use the ?? modifier on the INPUT statement.  But note that will suppress ALL of the warnings about invalid values.  So values like FRED will be silently set to missing just like values like NA.

Of you could define a special INFORMAT that will convert the NA strings to missing and then use normal informat for the other values.  

proc format ;
invalue na     'NA','na','Na' = . other = [32.] ;
invalue na_ymd 'NA','na','Na' = . other = [yymmdd10.] ;
run;

Then use the informat in the data step.

data want;
  infile csv dsd truncover firstobs=2;
  length id $10 age 8 start_date 8 ;
  informat start_date na_ymd.  age na.;
  format start_date yymmdd10.;
  input id -- start_date ;
run;