Problems with converting character to number

[Phil_NZ]

Hi SAS Users,

Today when I tried to convert the data from character to numeric, I face this warning, I am wondering how it affects my results, and whether you have any other way to convert the data to numeric in this case?

I also attached my dataset in this post as well:

DATA sheet2_outx;
set sheet2_out;
s22=s2*1;
drop s2;
run;

Type=134495 Year=1990 s2=NA s22=. _ERROR_=1 _N_=131
NOTE: Invalid numeric data, s2='NA' , at line 49 column 5.
Type=134495 Year=1991 s2=NA s22=. _ERROR_=1 _N_=132
NOTE: Invalid numeric data, s2='NA' , at line 49 column 5.
Type=134495 Year=1992 s2=NA s22=. _ERROR_=1 _N_=133
NOTE: Invalid numeric data, s2='NA' , at line 49 column 5.
Type=134495 Year=1993 s2=NA s22=. _ERROR_=1 _N_=134
NOTE: Invalid numeric data, s2='NA' , at line 49 column 5.
Type=134625 Year=1988 s2=NA s22=. _ERROR_=1 _N_=161
NOTE: Invalid numeric data, s2='NA' , at line 49 column 5.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.
Type=134625 Year=1989 s2=NA s22=. _ERROR_=1 _N_=162

My dataset is:

Type    Year    s2
131566	1988	$$ER: E100,NO WORLDSCOPE DATA FOR THIS CODE
131566	1989	
131566	1990	
131566	1991	
131566	1992	
131566	1993	
131566	1994	
131566	1995	
131566	1996	
131566	1997	
131566	1998	
131566	1999	
131566	2000	
131566	2001	
131566	2002	
131566	2003	
131566	2004	
131566	2005	
131566	2006	
131566	2007	
131566	2008	
131566	2009	
131566	2010	
131566	2011	
131566	2012	
131566	2013	
131566	2014	
131566	2015	
131566	2016	
131566	2017	
131566	2018	
131566	2019	
131879	1988	NA
131879	1989	NA
131879	1990	NA
131879	1991	NA
131879	1992	NA
131879	1993	NA
131879	1994	NA
131879	1995	NA
131879	1996	NA
131879	1997	3642572
131879	1998	3843093
131879	1999	2518942

what I want is

Type    Year    s22
131566	1988	.
131566	1989	.
131566	1990	.
131566	1991	.
131566	1992	.
131566	1993	.
131566	1994	.
131566	1995	.
131566	1996	.
131566	1997	.
131566	1998	.
131566	1999	.
131566	2000	.
131566	2001	.
131566	2002	.
131566	2003	.
131566	2004	.
131566	2005	.
131566	2006	.
131566	2007	.
131566	2008	.
131566	2009	.
131566	2010	.
131566	2011	.
131566	2012	.
131566	2013	.
131566	2014	.
131566	2015	.
131566	2016	.
131566	2017	.
131566	2018	.
131566	2019	.
131879	1988	.
131879	1989	.
131879	1990	.
131879	1991	.
131879	1992	.
131879	1993	.
131879	1994	.
131879	1995	.
131879	1996	.
131879	1997	3642572
131879	1998	3843093
131879	1999	2518942

Many thanks and warmest regards.

[novinosrin · Accepted solution]

Instead of 

s22=s2*1;

 use

if notdigit(s2)=0 then s22=s2*1;

or just use the below

s22=input(s2,?? 32.);

[Phil_NZ]

And when I change the code to normal, the same problem also popping up

DATA sheet2_outx;
set sheet2_out;
s22=input(s2,8.);
run;

NOTE: Invalid argument to function INPUT at line 49 column 5.
Type=134495 Year=1992 s2=NA s22=. _ERROR_=1 _N_=133
NOTE: Invalid argument to function INPUT at line 49 column 5.
Type=134495 Year=1993 s2=NA s22=. _ERROR_=1 _N_=134
NOTE: Invalid argument to function INPUT at line 49 column 5.
Type=134625 Year=1988 s2=NA s22=. _ERROR_=1 _N_=161
NOTE: Invalid argument to function INPUT at line 49 column 5.
WARNING: Limit set by ERRORS= option reached.  Further errors of this type will not be printed.
Type=134625 Year=1989 s2=NA s22=. _ERROR_=1 _N_=162

[ballardw]

One would question how the source data was brought into SAS. Typically having values that should be numeric with character values like "NA" indicates a process that relies on Proc Import or one of the wizards that uses Import. Which means that every file read has guesses made by the program to read values and means that data inconsistencies are going to happen.

If you are reading multiple files that should have the same content, i.e. variable names, types and formats, it is usually worth the effort to use a data step to read file. Then you have control of such things. One advantage of writing a data step is you could either use the ?? in an input statement to suppress warnings about invalid data (which may mean you miss something else ) or create custom informats that will treat the text value "NA" as desired and still have the ability to get invalid warnings for values like "Error" or "Overflow" or what ever. Custom formats also allow to provide special missing values like .N that tell people the original text value was "NA" if needed but still be excluded from numeric computations.

Proc format;
invalue myna 
'NA' = .N
;

data example;
   input x :Myna.;
datalines;
1
123.4567
NA
45
Error
;

proc print data=example;
   sum x;
run;
proc freq data=example;
   tables x;
run;

Reads data, assigns a special missing to the expected appearance of NA without any note in the log but does provide a warning about the "Error" appearing in the field, shows that the missing values are not included in addition and behavior of treating the special missing as missing for other purposes.