turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- how to get a number out of a text variable

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-24-2016 03:14 PM - edited 02-24-2016 03:26 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to fengyuwuzu

02-24-2016 03:43 PM

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.

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to fengyuwuzu

02-24-2016 03:18 PM

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?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

02-24-2016 03:21 PM

Thanks, I just updated with some more data rows

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to fengyuwuzu

02-24-2016 03:32 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

02-24-2016 04:06 PM

Thank you very much, Reeza. This is an excellent idea!

when I have more time, I will play with it.

when I have more time, I will play with it.

Solution

02-24-2016
04:05 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to fengyuwuzu

02-24-2016 03:43 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

02-24-2016 04:06 PM

Thanks. This works!