Text Parsing

[Danglytics]

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

[Linlin · Accepted solution]

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]

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]

Art,

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

[art297]

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

[Haikuo]

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;

[art297]

:  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]

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

[Haikuo]

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]

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