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

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

1 ACCEPTED SOLUTION

Accepted Solutions
mmkr
Obsidian | Level 7

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

11 REPLIES 11
Reeza
Super User

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. 

Astounding
PROC Star

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?

mmkr
Obsidian | Level 7

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

Astounding
PROC Star

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?

mmkr
Obsidian | Level 7

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

Astounding
PROC Star

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.

mmkr
Obsidian | Level 7

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;

 

 

Reeza
Super User
You really should mark someone else's answer as the full solution, not your own post.
mmkr
Obsidian | Level 7

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

can you pleasde advise ?

ErikLund_Jensen
Rhodochrosite | Level 12

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.

Shmuel
Garnet | Level 18

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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 11 replies
  • 1013 views
  • 4 likes
  • 5 in conversation