Help using Base SAS procedures

Changing character values depending on how many of that character appear

Accepted Solution Solved
Reply
Super Contributor
Posts: 398
Accepted Solution

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
Respected Advisor
Posts: 3,124

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

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

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
Respected Advisor
Posts: 3,124

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

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

Respected Advisor
Posts: 3,124

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:

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

Super Contributor
Posts: 398

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

Thank you so much for all your help.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 280 views
  • 6 likes
  • 2 in conversation