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. |
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 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?
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);
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.