DATA Step, Macro, Functions and more

Problem with converting character to numeric

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

Problem with converting character to numeric

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?


Accepted Solutions
Solution
‎11-14-2016 04:21 AM
Super User
Posts: 10,023

Re: Problem with converting character to numeric

Posted in reply to chris2377
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*/
.............................

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Problem with converting character to numeric

Posted in reply to chris2377

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;

Contributor
Posts: 60

Re: Problem with converting character to numeric

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

Respected Advisor
Posts: 4,920

Re: Problem with converting character to numeric

Posted in reply to chris2377

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.

PG
Solution
‎11-14-2016 04:21 AM
Super User
Posts: 10,023

Re: Problem with converting character to numeric

Posted in reply to chris2377
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*/
.............................

Contributor
Posts: 60

Re: Problem with converting character to numeric

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 390 views
  • 1 like
  • 4 in conversation