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
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
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
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
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
Thank you so much Haikuo.
Do you know of a good tool to learn perl reg expressions better (book, website,etc.) ?
Thanks again
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
Thank you so much for all your help.
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.
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.