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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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;

View solution in original post

9 REPLIES 9
Linlin
Lapis Lazuli | Level 10

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;

art297
Opal | Level 21

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;

Linlin
Lapis Lazuli | Level 10

Art,

It is unfair. You always come up with better solution:smileycry:.

art297
Opal | Level 21

Linlin:  Not true!  You have offered better solutions than me on a number of occasions.

Haikuo
Onyx | Level 15

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? Smiley Wink

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;

art297
Opal | Level 21

:  too easy of a challenge!

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;

Haikuo
Onyx | Level 15

Well, like they just said, you are the best!

Haikuo
Onyx | Level 15

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

Ksharp
Super User

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 9 replies
  • 1791 views
  • 1 like
  • 5 in conversation