## First person name and Second person name and last person name

Solved
Occasional Contributor
Posts: 16

# 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: 16

## 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;

All Replies
Super User
Posts: 23,343

## 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,642

## 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: 16

## 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

Super User
Posts: 6,642

## 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: 16

## 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,642

## 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: 16

## 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: 23,343

## 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: 16

## 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: 47

## 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.

Posts: 1,831

## 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
• 306 views
• 4 likes
• 5 in conversation