topic Re: First person name and Second person name and last person name in SAS Programming

First person name and Second person name and last person name

mmkr — Tue, 22 Aug 2017 16:31:36 GMT

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

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

Reeza — Tue, 22 Aug 2017 16:48:26 GMT

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.

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

Astounding — Tue, 22 Aug 2017 16:54:12 GMT

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?

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

mmkr — Tue, 22 Aug 2017 17:06:07 GMT

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

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

Astounding — Tue, 22 Aug 2017 17:09:09 GMT

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?

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

mmkr — Tue, 22 Aug 2017 17:10:58 GMT

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

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

Astounding — Tue, 22 Aug 2017 17:38:22 GMT

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.

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

mmkr — Tue, 22 Aug 2017 17:59:16 GMT

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;

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

Reeza — Tue, 22 Aug 2017 18:15:06 GMT

You really should mark someone else's answer as the full solution, not your own post.

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

mmkr — Tue, 22 Aug 2017 18:25:23 GMT

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

can you pleasde advise ?

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

Shmuel — Tue, 22 Aug 2017 18:28:23 GMT

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;

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

ErikLund_Jensen — Fri, 25 Aug 2017 16:58:58 GMT

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.