topic Re: how do I trim the piece to the right of the last underscore? in SAS Programming

how do I trim the piece to the right of the last underscore?

ZRick — Sat, 30 Mar 2013 00:54:15 GMT

I just want: "IR_MX_GOVT_BFV_MXN" , or trim off the piece to the right of the last underscore, including the underscore.

How do I do that?

data t;

crv='IR_MX_GOVT_BFV_MXN_7Y';

run;

Re: how do I trim the piece to the right of the last underscore?

Tom — Sat, 30 Mar 2013 00:58:46 GMT

I usually use SAS functions like SCAN and SUBSTR, but you can probably get someone to show you a regular expression syntax.

data t;

crv='IR_MX_GOVT_BFV_MXN_7Y';

if index(crv,'_') then want = substr(crv,1,length(crv)-length(scan(crv,-1,'_'))-1);

put (crv want) (=/);

run;

Re: how do I trim the piece to the right of the last underscore?

ZRick — Sat, 30 Mar 2013 01:05:06 GMT

Thanks so much, Tom.

Do I need to use trim(left(var)) to trim off the blanks?

Also, why do you put "if index(crv,'_') then" as the condition logic?

Re: how do I trim the piece to the right of the last underscore?

Tom — Sat, 30 Mar 2013 01:20:59 GMT

TRIM() is meaningless in this situation as LENGTH() and SAS assignment to character variables will ignore and append spaces, respectively.

I added the IF because I was worried about what it would do when there were no underscores in the value. Try it and see what happens.

Re: how do I trim the piece to the right of the last underscore?

Haikuo — Sat, 30 Mar 2013 14:34:47 GMT

Another approach is to "call scan":

data t;

crv='IR_MX_GOVT_BFV_MXN_7Y';

call scan(crv,-1,_p,_l,'_');

want=substr(crv,1,_p-2);

put (crv want) (=/);

drop _:;

run;

Haikuo

Re: how do I trim the piece to the right of the last underscore?

DBailey — Sat, 30 Mar 2013 15:40:22 GMT

I'm not at my computer w/ sas to work out the syntax, but assuming the number of "-"s is variable, then you can use the reverse function, find the first "_", and take everything after it...then reverse it again.

Re: how do I trim the piece to the right of the last underscore?

data_null__ — Sat, 30 Mar 2013 15:48:55 GMT

DBailey wrote:

Instead of fiddling with reverse just search from the right. My question is what should be assigned when there are no underscores.

data _null_; 
   do crv='IR_MX_GOVT_BFV_MXN_7Y','IRMXGOVTBFVXN7Y'; 
      want2= substrN(crv,1,find(crv,'_',-length(crv))-1);
      put (crv want:) (=/);
      call missing(of want:);
      end; 
   run;

Re: how do I trim the piece to the right of the last underscore?

PGStats — Sat, 30 Mar 2013 16:03:52 GMT

You can also kill a fly with a bazooka :

data t;

crv='IR_MX_GOVT_BFV_MXN_7Y';

want = prxchange("s/(.*)_[^_]*$/\1/", 1, crv);

put (crv want) (=/);

run;

It also works as required when there is no underscore. - PG

Re: how do I trim the piece to the right of the last underscore?

data_null__ — Sat, 30 Mar 2013 16:46:02 GMT

It also works as required when there is no underscore. - PG

How do you know?

Re: how do I trim the piece to the right of the last underscore?

PGStats — Sat, 30 Mar 2013 19:17:56 GMT

From first principles. I also tested...

14   data t;
15   crv='IR_MX_GOVT_BFV_MXN_7Y';
16   want = prxchange("s/(.*)_[^_]*$/\1/",1,crv);
17   put (crv want) (=/);
18   crv='IRMXGOVTBFVMXN7Y';
19   want = prxchange("s/(.*)_[^_]*$/\1/",1,crv);
20   put (crv want) (=/);
21   run;

crv=IR_MX_GOVT_BFV_MXN_7Y
want=IR_MX_GOVT_BFV_MXN

crv=IRMXGOVTBFVMXN7Y
want=IRMXGOVTBFVMXN7Y

... looks ok to me.

Re: how do I trim the piece to the right of the last underscore?

ScottBass — Sat, 30 Mar 2013 23:45:15 GMT

ZRick wrote:

I just want: "IR_MX_GOVT_BFV_MXN" , or trim off the piece to the right of the last underscore, including the underscore.

How do I do that?

data t;

crv='IR_MX_GOVT_BFV_MXN_7Y';

run;

Another regular expression solution:

data t;
crv='IR_MX_GOVT_BFV_MXN_7Y';
* crv='IRMXGOVTBFVMXN7Y';
new1a=prxchange("s/^(.*)_(.*)$/\1/o",-1,crv);
new1b=prxchange("s/^(.*)_(.*)$/\2/o",-1,crv);
new2a=prxchange("s/^(.*?)_(.*)$/\1/o",-1,crv);
new2b=prxchange("s/^(.*?)_(.*)$/\2/o",-1,crv);
run;

I'll explain the regular expression:

s = "substitute"

/ / / = delimiter for "from", "to"

^ = beginning of text

( ) = capture buffer, numbered in order. So, there are two capture buffers.

. = any character

* = zero or more occurrences.

$ = end of text. Note: the padding of SAS variables with spaces are significant. With a different regular expression, you might need "trim(crv)"

\1 = the text captured in the first capture buffer

o = compile the regular expression once, and retain the regex across data step iterations. Irrelevant in this example, but often used to increase performance.

-1 = apply the substitution to all occurrences of the regular expression in the source text (doesn't apply to this regex, since it encompasses the entire string).

Ok, so in words, the first regular expression says "from the beginning of the text, find all characters ***, an underscore, then all characters until the end of the text, and substitute the text captured in the first capture buffer".

*** A key concept here is "greedy" vs. "non-greedy" matching (Google "regular expressions greedy matching"). By default, the matching is "greedy". So, (.*) matches as many occurrences as it can, while still satisfying the rest of the regular expression _(.*)$ (underscore, any characters, end of line).

In the second regular expression, the ? metacharacter means "non-greedy" matching. So, (.*?) means match the minimum occurrences, while still satisfying the rest of the regular expression (which is a "greedy" match).

I've saved the results of capture buffer #2 so you can see the results of the greedy vs. non-greedy matching.

Uncomment the second crv setting so show that crv will be returned unchanged if it doesn't match the regular expression.

Hope this helps,

Scott

Re: how do I trim the piece to the right of the last underscore?

ScottBass — Sun, 31 Mar 2013 00:01:31 GMT

P.S.: I've just read PGStats' solution more closely, and mine isn't that much different than PGStats' solution. Use whichever one works for you. But do wrap your head around "greedy" vs. "non-greedy" - it applies to both solutions.

Re: how do I trim the piece to the right of the last underscore?

Patrick — Sun, 31 Mar 2013 05:20:53 GMT

If you just want to replace a substring with a NULL string then why not formulate the RegEx accordingly?

data test;
infile datalines truncover;
input in_string:$30.;
length out_string $30.;
out_string=prxchange('s/_+[^_]*$//o',1,in_string);
datalines;
IR_MX_GOVT_BFV_MXN_7Y
IR_MX_GOVT_BFV_MXN____7Y
IRMXGOVTBFVMXN7Y
IR_MX_GOVTBFVMXN_7Y
IR_MX_GOVTBFVMXN_
;
run;

Re: how do I trim the piece to the right of the last underscore?

data_null__ — Sun, 31 Mar 2013 13:57:49 GMT

There was never a mention of what to assign when there is no underscore in the string. That's your assumption.

Re: how do I trim the piece to the right of the last underscore?

Tom — Sun, 31 Mar 2013 14:20:39 GMT

Because it uses a change (substitute) command when the pattern is not matched nothing is changed.

Re: how do I trim the piece to the right of the last underscore?

PGStats — Sun, 31 Mar 2013 14:27:32 GMT

Indeed, my assumption was : no underscore -> nothing to trim. - PG