BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jerry898969
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

6 REPLIES 6
Haikuo
Onyx | Level 15

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

jerry898969
Pyrite | Level 9

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

Haikuo
Onyx | Level 15

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

jerry898969
Pyrite | Level 9

Thank you so much Haikuo.

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

Thanks again

Haikuo
Onyx | Level 15

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

jerry898969
Pyrite | Level 9

Thank you so much for all your help.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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