DATA Step, Macro, Functions and more

how to get a number out of a text variable

Accepted Solution Solved
Reply
Super Contributor
Posts: 312
Accepted Solution

how to get a number out of a text variable

[ Edited ]

I have a "comment" character variable,  whose values are sentences containing a number.

for example: "Implemented stake limit of 1000.000000 over 31 Days."

 

I want to take out the 1000.000000 and also the 31 out into two numeric variables. Is there a way to do it in SAS?

Actually I do not need to take out the 1, 7 and 31. I can recode into daily, weekly and monthly.

 

here are more examples of the data:

Implemented stake limit of 1000.000000 over 1 Day.
Implemented stake limit of 2500.000000 over 31 Days.
Implemented stake limit of 1000.000000 over 7 Days.
Implemented stake limit of 25000.000000 over 31 Days.
Deleted stake limit
Implemented stake limit of 25000.000000 over 7 Days.
Implemented stake limit of 100.000000 over 1 Day.
Implemented stake limit of 10000.000000 over 1 Day.
Implemented stake limit of 3000.000000 over 1 Day.
Implemented stake limit of 4000.000000 over 7 Days.
Implemented stake limit of 6000.000000 over 31 Days.
Deleted stake limit
Deleted stake limit
Deleted stake limit
Implemented stake limit of 150.000000 over 1 Day.
Implemented stake limit of 300.000000 over 31 Days.
Implemented stake limit of 500.000000 over 31 Days.
Implemented stake limit of 200.000000 over 1 Day.
Implemented stake limit of 700.000000 over 31 Days.
Implemented stake limit of 350.000000 over 1 Day.
Implemented stake limit of 1500.000000 over 31 Days.
Deleted stake limit
Deleted stake limit
Implemented stake limit of 150.000000 over 31 Days.
Implemented stake limit of 799.000000 over 31 Days.
Implemented stake limit of 200.000000 over 1 Day.
Implemented stake limit of 2000.000000 over 31 Days.
Implemented stake limit of 195.000000 over 1 Day.
Implemented stake limit of 190.000000 over 1 Day.
Implemented stake limit of 189.000000 over 1 Day.
Implemented stake limit of 1900.000000 over 31 Days.
Implemented stake limit of 180.000000 over 1 Day.
Implemented stake limit of 120.000000 over 1 Day.
Implemented stake limit of 1000.000000 over 31 Days.
Implemented stake limit of 900.000000 over 31 Days.
Implemented stake limit of 115.000000 over 1 Day.
Implemented stake limit of 114.000000 over 1 Day.
Implemented stake limit of 899.000000 over 31 Days.
Implemented stake limit of 110.000000 over 1 Day.

Accepted Solutions
Solution
‎02-24-2016 04:05 PM
Super User
Posts: 5,096

Re: how to get a number out of a text variable

Here's a reasonable approach:

 

data want;

set have;

length test $ 200 n1 n2 8;

test = compress(existing_string, '-.', 'kds');

n1 = scan(test, 1, ' ');

n2 = scan(test, 2, ' ');

drop test;

run;

 

The COMPRESS function keeps negative signs, decimal points, digits and spaces.  You will get a message about character to numeric conversion, when assigning values to n1 and n2.

View solution in original post


All Replies
Super User
Posts: 17,930

Re: how to get a number out of a text variable


fengyuwuzu wrote:

Is there a way to do it in SAS?

 


More than one Smiley Happy

 

You need to provide more info though. You only post one example, I'm assuming your text won't always have that exact structure?

Super Contributor
Posts: 312

Re: how to get a number out of a text variable

Thanks, I just updated with some more data rows
Super User
Posts: 17,930

Re: how to get a number out of a text variable

I guess it is relatively structured.

PRX is the best way, but I don't know how to write those, I'm sure some one else will propose such a solution.

 

A brute force method is to search for words of/over and substring between them and similar for days.

 

Rough idea of the code would be:

 

x1=find('of', string);

y1=find('over', string);

 

num1=substr(string, x1, y1-x1+1);

 

 

Super Contributor
Posts: 312

Re: how to get a number out of a text variable

Thank you very much, Reeza. This is an excellent idea!
when I have more time, I will play with it.
Solution
‎02-24-2016 04:05 PM
Super User
Posts: 5,096

Re: how to get a number out of a text variable

Here's a reasonable approach:

 

data want;

set have;

length test $ 200 n1 n2 8;

test = compress(existing_string, '-.', 'kds');

n1 = scan(test, 1, ' ');

n2 = scan(test, 2, ' ');

drop test;

run;

 

The COMPRESS function keeps negative signs, decimal points, digits and spaces.  You will get a message about character to numeric conversion, when assigning values to n1 and n2.

Super Contributor
Posts: 312

Re: how to get a number out of a text variable

Thanks. This works!
☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 260 views
  • 3 likes
  • 3 in conversation