BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
fengyuwuzu
Pyrite | Level 9

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.
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

6 REPLIES 6
Reeza
Super User

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

fengyuwuzu
Pyrite | Level 9
Thanks, I just updated with some more data rows
Reeza
Super User

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

 

 

fengyuwuzu
Pyrite | Level 9
Thank you very much, Reeza. This is an excellent idea!
when I have more time, I will play with it.
Astounding
PROC Star

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.

fengyuwuzu
Pyrite | Level 9
Thanks. This works!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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