topic Text Parsing in SAS Programming

Text Parsing

Danglytics — Thu, 08 Nov 2012 18:04:32 GMT

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.

Re: Text Parsing

Linlin — Thu, 08 Nov 2012 18:21:54 GMT

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;

Re: Text Parsing

art297 — Thu, 08 Nov 2012 18:37:07 GMT

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;

Re: Text Parsing

Linlin — Thu, 08 Nov 2012 18:48:21 GMT

Art,

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

Re: Text Parsing

art297 — Thu, 08 Nov 2012 19:18:28 GMT

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

Re: Text Parsing

Haikuo — Thu, 08 Nov 2012 19:19:57 GMT

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

Re: Text Parsing

Haikuo — Thu, 08 Nov 2012 19:23:20 GMT

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;

Re: Text Parsing

art297 — Thu, 08 Nov 2012 19:58:09 GMT

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

Re: Text Parsing

Haikuo — Thu, 08 Nov 2012 20:00:35 GMT

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

Re: Text Parsing

Ksharp — Fri, 09 Nov 2012 03:19:11 GMT

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