## Changing character values depending on how many of that character appear

Solved
Super Contributor
Posts: 405

# Changing character values depending on how many of that character appear

Hello,

I'm working with a data table that has a variable that has names separated by '\\'.  I do not know how many names there are per row.  There could be 1 up to 10 names.

What I need to do is if there is 1 name to not do anything.  If it's 2 names I have to put the word "and" between the names.  If it's more than 2 I have to put a comma between the names except for the last occurrences where I have to have "and".

ex.

William

Bob\\Joe\\Fred

Freddy\\Steve\\Jimmy\Cindy

Wilma\\David

results

William

Bob, Joe and Fred

Freddy, Steve, Jimmy and Cindy

Wilma and Dave.

This is the code I'm working with.  It seems like it can be done in a much cleaner way.

```f1 = 'People: ' ;

if length(trim(left(People))) > 1 then do ;
if count(People, '\\') = 0 then f2 = f1 || ' ' || People ;
else if count(People, '\\') = 1 then do ;
People_1 = tranwrd(People, '\\', ' and ') ;
f2 = catx(' ', f1, People_1) ;
end ;
else if count(People, '\\') > 1 then do ;
lastDelim = find (People, '\\', -400) ;
if lastDelim ne . then People_ = substr(People, 1, lastDelim-1) || ' and ' || substr(People, lastDelim+2) ;
else People_ = '' ;
People_2 = tranwrd(People_, '\\', ',') ;
f2 = catx(' ', f1, People_2) ;
end ;
end ;
```

Any help would be appreciated.

thank you

Accepted Solutions
Solution
‎04-10-2013 04:36 PM
Posts: 3,167

## Re: Changing character values depending on how many of that character appear

In that case, we may need to switch some order:

data have;

input var \$50.;

cards;

William

Bob\\Joe\\Fred

Freddy\\Steve\\Jimmy\\Cindy

Wilma\\David

canton, oh\\las vegas, nv\\orlando, fl

;

data want;

set have;

_var=prxchange("s/\\\\([^\\]+ *\$)/ and \1/o", -1, var);

new_var=tranwrd(_var,'\\',',');

drop _var;

run;

Haikuo

All Replies
Posts: 3,167

## Re: Changing character values depending on how many of that character appear

Not sure if this is 'cleaner', and I am sure someone who speak fluent Perl will come up with better solution.

data have;

input var \$50.;

cards;

William

Bob\\Joe\\Fred

Freddy\\Steve\\Jimmy\\Cindy

Wilma\\David

;

data want;

set have;

_var=tranwrd(var,'\\',',');

new_var=prxchange("s/,(\w+ *\$)/ and \1/o", -1, _var);

drop _var;

run;

Haikuo

Super Contributor
Posts: 405

## Re: Changing character values depending on how many of that character appear

Hai.kuo,

Thank you so much for your reply.  I have a follow up question.  I have multiple columns with this sort of variable but some have a comma already within the data.  What would be the best way to do that in that scenario?

ex.

canton, oh\\las vegas, nv\\orlando, fl

canton, oh, las vegas, nv, and orlando, fl

Thank you

Solution
‎04-10-2013 04:36 PM
Posts: 3,167

## Re: Changing character values depending on how many of that character appear

In that case, we may need to switch some order:

data have;

input var \$50.;

cards;

William

Bob\\Joe\\Fred

Freddy\\Steve\\Jimmy\\Cindy

Wilma\\David

canton, oh\\las vegas, nv\\orlando, fl

;

data want;

set have;

_var=prxchange("s/\\\\([^\\]+ *\$)/ and \1/o", -1, var);

new_var=tranwrd(_var,'\\',',');

drop _var;

run;

Haikuo

Super Contributor
Posts: 405

## Re: Changing character values depending on how many of that character appear

Thank you so much Haikuo.

Do you know of a good tool to learn perl reg expressions better (book, website,etc.) ?

Thanks again

Posts: 3,167

## Re: Changing character values depending on how many of that character appear

I am probably not the right person for the question, as I know how far away I am from a PRX expert. There are loads of learning materials online, but studying them only makes me feel that I know nothing about nothing. So I started by reading this great paper by Ron Cody:

http://www2.sas.com/proceedings/sugi29/265-29.pdf

And I use this:

and this:

SAS(R) 9.3 Functions and CALL Routines: Reference

as my lookup link. That is all I got.

HTH,

Haikuo

Super Contributor
Posts: 405

## Re: Changing character values depending on how many of that character appear

Thank you so much for all your help.

🔒 This topic is solved and locked.