topic sas, perl regular expressions, variable creation in SAS Programming

sas, perl regular expressions, variable creation

jdub — Wed, 26 Jun 2013 19:46:03 GMT

I am new to using perl regular expressions. I have a variable, call it area, whose observations correspond to a 32 character string. Within each observation there is a six digit numeric string contained in parenthesis. I want to create a new variable, call it space, whose observations correspond to the the six digit numeric string from the original variable titled area. Below is my first pass at coding. With the sql approach, I am getting an error that says ' Function PRXPARSE requires at most 1 argument(s)'. Could someone help me out to correctly code the creation of the variable titled space?

proc sql;

create table test as

select a.*, prxparse("/$\d\d\d\d\d\d$/" a.area) as space

from data1 a;

quit;

I also tried:

data test;

if _n_=1 then perl=prxparse("/$\d\d\d\d\d\d$\");

retain perl;

set data1;

space=prxmatch(perl,area);

run;

Re: sas, perl regular expressions, variable creation

Reeza — Wed, 26 Jun 2013 20:16:14 GMT

Can you post sample data and sample results.

Personally I'd use the scan function instead.

Scan(space, 2, "()");

Re: sas, perl regular expressions, variable creation

PGStats — Wed, 26 Jun 2013 20:24:31 GMT

This will match a number of any number of digits between parentheses within your string. If you want exclusively numbers of 6 digits, replace the + in the pattern with {6}

data test;

area = "abcd(123456)gdre";

prx = prxparse("/$(\d+)$/o");

if prxmatch(prx, area) then space = prxposn(prx,1,area);

put (_all_) (=);

run;

Re: sas, perl regular expressions, variable creation

Tom — Wed, 26 Jun 2013 20:28:31 GMT

PRXCHANGE() makes more sense to me for this operation.

You can probably get a better regex from someone else, but this seems to work.

data test;

area='there are 123456 counties';

space=prxchange("s/.*(\d\d\d\d\d\d).*/$1/",-1,area);

put (_all_) (=);

run;

Re: sas, perl regular expressions, variable creation

PGStats — Wed, 26 Jun 2013 20:51:05 GMT

More efficient than regular expressions indeed. All depends on how regular your data is :

data _null_;

length space area $50;

input area&;

space = Scan(area, 2, "()");

put (_all_) (+2 =);

datalines;

abcd(123456)gdre

(123456) It was there!

abcd(I'm not a number!) but I am (123456)

(Eh!) No number here!

;

space=123456 area=abcd(123456)gdre

space=It was there! area=(123456) It was there!

space=I'm not a number! area=abcd(I'm not a number!) but I am (123456)

space=No number here! area=(Eh!) No number here!

Re: sas, perl regular expressions, variable creation

FriedEgg — Thu, 27 Jun 2013 14:21:07 GMT

I agree that prxchange is a simpler operation to use in this case, however, when a pattern match is not satisfied it will return the originating input string. Since you are looking for a number, we also may as well create the variable with the proper data type. This help to solve the issue with prxchange returning the input string. If you want to use scan then the data needs more prep to account for all the possibilities.

data _null_;

input text & $50. ;

* prxchange ;

prx1 = input( prxchange( 's/.*$(\d{6})$.*/$1/' , -1 , text ) , ?? best. ) ;

* prxmatch/prxposn ;

if _n_ = 1 then pid = prxparse( '/$(\d{6})$/o' ) ;

retain pid ;

if prxmatch( pid , text ) then prx2 = input( prxposn( pid , 1 , text ) , ?? best. ) ;

* scan ;

scan = input( scan( compress( text , '()' , 'kd' ) , 2 , '()' , 'm' ) , ?? best. ) ;

if missing(scan) then scan = input( scan( cat( ' ' , compress( text , '()' , 'kd' ) , ' ' ) , 2 , '()' ) , ?? best. ) ;

if ^missing(scan) then do;

if scan ne mod( scan , 10**6 ) then scan = . ;

end;

put (_all_) (/=) ;

cards;

abcd(123456)gdre

(123456) It was there!

abcd(I'm not a number!) but I am (123456)

(Eh!) No number here!

7 numbers (1234567) there...

;

text=abcd(123456)gdre

prx1=123456

pid=1

prx2=123456

scan=123456

text=(123456) It was there!

prx1=123456

pid=1

prx2=123456

scan=123456

text=abcd(I'm not a number!) but I am (123456)

prx1=123456

pid=1

prx2=123456

scan=123456

text=(Eh!) No number here!

prx1=.

pid=1

prx2=.

scan=.

text=7 numbers (1234567) there...

prx1=.

pid=1

prx2=.

scan=.