Hi,
I'm looking for help in figuring out the logic to parse a text string.
The variable contains a string with purchase type, dollar value, and conversions, for example:
ID PurchaseType
1 CPA ($2.50 x 20)
2 CPAA ($13.00 x 10)
3 CPC ($0.10 x 100)
etc.
I'm looking to prse the string by purchase type and value, for example
ID Type Value
1 CPA 2.50
2 CPAA 13.00
3 CPC 0.10
etc.
Thanks for your help.
KD
try:
data have;
id=1;
PurchaseType='CPA ($0.50 x 20)';
run;
data want;
set have;
length type $8;
type=scan(PurchaseType,1);
value=compress(scan(PurchaseType,1,'x'),'.','kd');
keep id type value;
proc print;run;
try:
data have;
id=1;
PurchaseType='CPA ($0.50 x 20)';
run;
data want;
set have;
length type $8;
type=scan(PurchaseType,1);
value=compress(scan(PurchaseType,1,'x'),'.','kd');
keep id type value;
proc print;run;
data have;
informat PurchaseType $50.;
input id 1 PurchaseType 3-50;
cards;
1 CPA ($2.50 x 20)
2 CPAA ($13.00 x 10)
3 CPC ($0.10 x 100)
;
data want;
set have;
format value 6.2;
Type=scan(PurchaseType,1);
value=input(scan(PurchaseType,2,"("),dollar6.);
run;
Art,
It is unfair. You always come up with better solution:smileycry:.
Linlin: Not true! You have offered better solutions than me on a number of occasions.
OK, here is a challenge to Art and Linlin:
Are you able to get the second cluster of numbers (20,10,100) without using PRX functions?
Haikuo
BTW, here is one using PRX:
data want;
set have;
retain _p;
_p=prxparse("/\s\d+\)/");
call prxsubstr(_p,PurchaseType,_st, _len);
value=substr(PurchaseType,_st+1,_len-2);
drop _:;
run;
data have;
informat PurchaseType $50.;
input id 1 PurchaseType 3-50;
cards;
1 CPA ($2.50 x 20)
2 CPAA ($13.00 x 10)
3 CPC ($0.10 x 100)
;
data want;
set have;
format value 6.2;
Type=scan(PurchaseType,1);
value=input(scan(PurchaseType,2,"("),dollar6.);
amount=input(scan(PurchaseType,-1),8.);
run;
Well, like they just said, you are the best!
SAS does come with a very powerful set of character functions. Here is to use regular expression:
data have;
informat PurchaseType $50.;
input id PurchaseType 3-50;
cards;
1 CPA ($2.50 x 20)
2 CPAA ($13.00 x 10)
3 CPC ($0.10 x 100)
;
data want;
set have;
retain _p;
_p=prxparse("/\$\d+\.\d+ /");
call prxsubstr(_p,PurchaseType,_st, _len);
value=substr(PurchaseType,_st+1,_len-1);
drop _:;
run;
proc print;run;
Haikuo
SCAN() is also very powerful .
data have; informat PurchaseType $50.; input id PurchaseType 3-50; cards; 1 CPA ($2.50 x 20) 2 CPAA ($13.00 x 10) 3 CPC ($0.10 x 100) ; run; data want; set have; type=scan(PurchaseType,1, ,'ka'); value=scan(PurchaseType,1,',.','kd'); run;
Ksharp
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.