First person name and Second person name and last person name

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

First person name and Second person name and last person name

I have a requirment like below..may i know if any one have handy code ?

 

 

If First_Name1 is populated only then result = first_name1
First_Name1 and First_Name2 are populated only then result = first_name1 AND first_name2
First_Name1 ,First_Name2 , first_name3 are populated only then result = first_name1 , first_name2 AND first_name3
First_Name1 ,First_Name2 , first_name3,first_name4 are populated only then result = first_name1 , first_name2 ,first_name3 AND first_name4


Accepted Solutions
Solution
‎08-22-2017 01:59 PM
Occasional Contributor
Posts: 13

Re: First person name and Second person name and last person name

Posted in reply to Astounding

thank you so much !!

 

Below is the working one ..

 

data test1;
infile cards dsd missover;
input f1$ f2$ f3$ f4$ ;
cards;
f1, f2, f3, f4
f1, f2, f3,
f1, f2,,
f1, f2, f3, f4
f1,,,
f1, f2, f3,
, f2, , f4
;
run;

data want;
set test1;
result = catx(', ', f1, f2, f3, f4);
if index(result, ',') then do;
last_piece = scan(result, -1, ',');
result_new = catt(substr(result, 1, length(result) - (length(last_piece) + 1) ),' AND',last_piece) ;
end;
if result_new = '' then result_new = catt(result,',');
run;

 

 

View solution in original post


All Replies
Super User
Posts: 21,546

Re: First person name and Second person name and last person name

I think this is unfortunately a bit of manual IF/THEN as you've pseudo coded below. 

 

You can use CATX() to help string the variables together and you can create an array and count the components to determine which rules need to be followed. 

Super User
Posts: 6,008

Re: First person name and Second person name and last person name

This probably comes close enough:

 

result = catx(' ', first_name1, first_name2, first_name3, firstname4);

 

What result would you like if only first_name2 and first_name4 are populated?

Occasional Contributor
Posts: 13

Re: First person name and Second person name and last person name

Posted in reply to Astounding

If only first_name2 and first_name4 are populated then i need first_name 2 and frist_name4

 

Example 1 : 

 

First Name 1: Jon

First Name 2 : Dan

First Name 3 : Tam

Frist Name 4 : Brad

 

Result : Jon,Dan,Tam AND Brad

 

Example 2 : 

 

First Name 1: Jon

First Name 2 : Dan

First Name 3 : Tam

 

 

Result : Jon,Dan AND Tam

 

 

Example 3 : 

 

First Name 1: Jon

First Name 2 : Dan

 

 

 

Result : Jon AND Dan

 

Example 4 : 

 

 

First Name 1: Jon

First Name 2 : blank

First Name 3 : blank

Frist Name 4 : blank

 

Result : Jon

 

 

Example 5: 

 

First Name 1: blank

First Name 2 : Adam

First Name 3 : blank

Frist Name 4 : Brad

 

Result : Adam AND Brad

Super User
Posts: 6,008

Re: First person name and Second person name and last person name

[ Edited ]

So do you want commas and the word AND as part of the result?

 

If so, does it matter if you have a comma before the word AND?

Occasional Contributor
Posts: 13

Re: First person name and Second person name and last person name

Posted in reply to Astounding

Yes I do want commas and the word AND as part of the result.

 

I think that's fine comma before the word AND , I can remove later

Super User
Posts: 6,008

Re: First person name and Second person name and last person name

OK, this looks reasonable then.

 

data want;

set have;

result = catx(', ', first_name1, first_name2, first_name3, first_name4);

if index(result, ',') then do;

   last_piece = scan(result, -1, ',');

   result = substr(result, 1, length(result) - (length(last_piece) + 2) ) || ' AND ' || last_piece);

end;

 

It's untested, so you'll need to see how close this gets to what you want.

Solution
‎08-22-2017 01:59 PM
Occasional Contributor
Posts: 13

Re: First person name and Second person name and last person name

Posted in reply to Astounding

thank you so much !!

 

Below is the working one ..

 

data test1;
infile cards dsd missover;
input f1$ f2$ f3$ f4$ ;
cards;
f1, f2, f3, f4
f1, f2, f3,
f1, f2,,
f1, f2, f3, f4
f1,,,
f1, f2, f3,
, f2, , f4
;
run;

data want;
set test1;
result = catx(', ', f1, f2, f3, f4);
if index(result, ',') then do;
last_piece = scan(result, -1, ',');
result_new = catt(substr(result, 1, length(result) - (length(last_piece) + 1) ),' AND',last_piece) ;
end;
if result_new = '' then result_new = catt(result,',');
run;

 

 

Super User
Posts: 21,546

Re: First person name and Second person name and last person name

You really should mark someone else's answer as the full solution, not your own post.
Occasional Contributor
Posts: 13

Re: First person name and Second person name and last person name

how can i do now ? i don't see that option now..

can you pleasde advise ?

Contributor
Posts: 41

Re: First person name and Second person name and last person name

Hi

Your solution works very well, but if something can be reduced to a single expression instead of several statements, it can also be used in proc sql in a select statement. Here the catx function is used as input to the prxchange function, where the last comma is translated to AND: 


proc sql;
   create table want2 as
   select prxchange('s/(.*),/$1 AND/',-1,catx(', ',f1,f2,f3,f4)) as result
   from test1;
quit;

 

This is of some importance with e.g. Data Integration Studio, because it can be used in an Extract or Join Transformation insted of an extra userwritten transformation.

Trusted Advisor
Posts: 1,683

Re: First person name and Second person name and last person name

Posted in reply to Astounding

Assuming all names are strings either empty (Blanks space) or strings that do not include any space 

then :

data have;
  length name1-name5 $8;
  name1 = 'aaa';
  name2 = 'bbbbb';
  name3 = '';
  name4 = 'dddd';
  name5 = '  ';
run;
data test;
 set have;
  result = translate(compbl(catx(' ',name1,name2,name3,name4,name5)),',' , ' ');
  last_comma = findc(result,',','B');
  if last_comma > 0 then 
     result = substr(result,1,last_comma -1)||' and '||substr(result,last_comma +1);
  drop name1-name5;
run;
☑ This topic is solved.

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

Discussion stats
  • 11 replies
  • 241 views
  • 4 likes
  • 5 in conversation