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
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;
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.
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?
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
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?
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
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.
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;
how can i do now ? i don't see that option now..
can you pleasde advise ?
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.