topic Re: Changing character values depending on how many of that character appear in SAS Procedures

Changing character values depending on how many of that character appear

jerry898969 — Wed, 10 Apr 2013 17:58:26 GMT

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

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

Haikuo — Wed, 10 Apr 2013 19:45:20 GMT

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

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

jerry898969 — Wed, 10 Apr 2013 20:01:31 GMT

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

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

Haikuo — Wed, 10 Apr 2013 20:36:16 GMT

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

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

jerry898969 — Thu, 11 Apr 2013 13:09:39 GMT

Thank you so much Haikuo.

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

Thanks again

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

Haikuo — Thu, 11 Apr 2013 13:43:18 GMT

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:

SAS(R) 9.3 Functions and CALL Routines: Reference

and this:

SAS(R) 9.3 Functions and CALL Routines: Reference

as my lookup link. That is all I got.

HTH,

Haikuo

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

jerry898969 — Thu, 11 Apr 2013 19:42:59 GMT

Thank you so much for all your help.