topic Re: Problem with converting character to numeric in SAS Programming

Problem with converting character to numeric

chris2377 — Thu, 10 Nov 2016 16:44:59 GMT

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?

Re: Problem with converting character to numeric

ballardw — Thu, 10 Nov 2016 18:48:59 GMT

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;

Re: Problem with converting character to numeric

PGStats — Thu, 10 Nov 2016 20:39:00 GMT

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.

Re: Problem with converting character to numeric

Ksharp — Fri, 11 Nov 2016 10:58:09 GMT

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*/
.............................

Re: Problem with converting character to numeric

chris2377 — Mon, 14 Nov 2016 09:23:15 GMT

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

Re: Problem with converting character to numeric

chris2377 — Mon, 14 Nov 2016 09:30:25 GMT

@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.