topic Re: how to get a number out of a text variable in SAS Programming

how to get a number out of a text variable

fengyuwuzu — Wed, 24 Feb 2016 20:26:32 GMT

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&colon;

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

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

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.

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

Reeza — Wed, 24 Feb 2016 20:18:27 GMT

@fengyuwuzu wrote:

Is there a way to do it in SAS?

More than one 🙂

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

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

fengyuwuzu — Wed, 24 Feb 2016 20:21:17 GMT

Thanks, I just updated with some more data rows

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

Reeza — Wed, 24 Feb 2016 20:32:05 GMT

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);

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

Astounding — Wed, 24 Feb 2016 20:43:41 GMT

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.

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

fengyuwuzu — Wed, 24 Feb 2016 21:06:09 GMT

Thanks. This works!

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

fengyuwuzu — Wed, 24 Feb 2016 21:06:39 GMT

Thank you very much, Reeza. This is an excellent idea!
when I have more time, I will play with it.