Problem with converting character to numeric

[chris2377]

Dear all,

I have data in a txt file (see attached).

First, I read them to SAS:

data have;
	infile 'mypath\sample.txt'  dlm='09'x dsd lrecl=4096 truncover firstobs=2 termstr=LF;
	input Mark Name	:$20. Country :$20.	Type :$20. Id  var1 :$20.	var2 :$20.	var3 :$20.	var4 :$20.	var5 :$20.
		var6 :$20.	var7 :$20.	var8 :$20.	var9 :$20.	var10 :$20.	var11 :$20.;
run;

Now, I want to convert var11 to numeric:

data want;
	set have;
	new=compress(translate(var11,"", '"n.a.", ')); /* remove all unnecessary characters*/
	new1=input(new, 12.); /* convert it to numeric*/
	new2=new*1; /* alternative way*/
	keep var11 new:;
run;

The problem is that it does not convert the variable. However, when I use var2 instead of var11, everything is fine

data want1;
	set have;
	new=compress(translate(var2,"", '"n.a.", ')); /* remove all unnecessary characters, here I use var2*/
	new1=input(new, 12.); /* convert it to numeric*/
	new2=new*1; /* alternative way*/
	keep var2 new:;
run;

Why converting var11 does not work?

[ballardw]

Once a variable is created is has a data type, numeric of character and will not change.

If I am reading a file then I would make sure that the resultant data type when first read is the desired type. instead  of Var11 :$20.

try Var11 : Best20.;

Other wise one of the many-times-repeated-approaches on this forum is:

data want;

   set have (rename=(var11=oldvar11);

   var11 = input(oldvar11,best12.);

   drop oldvar11;

run;

[chris2377]

@ballardw Thank you for the answer. I would prefer to read the variable as numeric in the first place, but the problem is that there are 'n.a.'s throughout the file, so when I specify the numeric informat I get all missing values. Is there any way to directly read such variables as numeric?

@Ksharp Thank you for the answer. With the translate function I get rid of a thousand separator. Decimals points are separated with a dot in my file. 

[PGStats]

You should revise the documentation of the translate function. Your use will get rid of decimal points.  

You can either read the var11 field with the proper informat, as suggested by @ballardw or do

data want;
	set have(rename=(var11=strVar11));
	var11 = input(strVar11, ?? 20.); /* convert to numeric*/
	drop str:;
run;

Note that 20. and BEST20. are alias informat names.

[Ksharp · Accepted solution]

Maybe you have other non-digit characters.

data want;
	set have;
	new=compress(var11, , 'kd')); /* remove all unnecessary characters*/
	new1=input(new, 12.); /* convert it to numeric*/
.............................

[chris2377]

Perfect. It worked. I wasn't aware of this extra arguments of the compress function. Thanks