Converting character age variable to numeric with an invalid value

[Novice_]

Hi, I have the data in excel as below:

when I import it with guessingrows the age variable is converted to character. How do I convert it back to numeric with the value of 70+ becoming just a numeric 70? Thanks. 

[ballardw · Accepted solution]

Did you import from a text file format such as CSV or Excel/spreadsheet?

If the file is spreadsheet, do a SAVE AS to create CSV. Import that. It will still have problems but you can copy the code from the log that proc import generated. Clean up a bit to remove line numbers or such.

Create a custom informat to read 70+ as 70:

Proc format;
invalue mynum
'70+' = 70
other = [8.]
;
run;

run the format code. In the saved generated data step change the informat to the custom informat such as Mynum. created in proc format. If you haven't used Proc Format much be advised that you cannot name the format or informat with a name ending in digits.

This approach may be best in the long run if you are going to read multiple similar data steps as you save the data step code and change the infile to the next file and the data set name.

OR something like this (which could use a similar informat with an Input function call) with if then else code to create a different variable with the numeric value:

data want;
   set have;
   if oldvar='70+' then newvar=70;
   else newvar = input(oldvar, 8.);
run;

where oldvar is the name of the variable imported as character and newvar is the name of a new variable that will be numeric.

[Novice_]

Thanks a lot. It works perfectly 😊