How to concatenate multiple rows when they satisfy

[chinna0369]

Hi, 

Below is my data:

if the values are "Y" then I want to concatenate respective value for those variables?

is there any function to do this in a single line?

Thanks,

Adithya

[ballardw · Accepted solution]

---

is there any function to do this in a single line?

Thanks,

Adithya

---

Yes.

Incomplete description though. Do you want a delimiter between your values or do you want text that ends up with the word at the end of one phrase running into the start of the next?

Also it is not exactly clear which of the Y valued variables are associated with which of the Crit1 to crit5. The names are 1) not in the same order as the Crit, and with the names like CRIT1FL4 I cannot tell if that should be used with Crit1 or Crit4, especially because you do not have a CRIT1FL1.

The example below may get you started. I am placing a ; between values

data want;
   set have;
   newvar = catx(';',ifc(var1='Y',crit1,''),ifc(var2='Y',crit2,''), 
       /* continue the obvious pattern until you run out of values*/
       );
run;

HOWEVER, since NONE of you values for Crit1 to Crit4 appear to change you might be better off just assigning custom formats to the Y valued variables.

And just for curiosity sake, how exactly do you intend to use that concatenated variable???

[chinna0369]

Hi, 

Thank you for your quick response!

Sorry for that I am not clear below.

here are my answers to your questions:

1. I want delimeter '; '.

2. crit1fl value is crit1, crit1fl2 value is crit2, crit1fl3 value is crit 3, etc.

as per your post I wrote like below:

col10=catx('; ',ifc(crit1fl2='Y',crit2,''),ifc(crit1fl='Y',crit1,''),ifc(crit1fl3='Y',crit3,''),
ifc(crit1fl4='Y',crit4,''),ifc(crit1fl5='Y',crit5,''));

but I am getting warning as below:

Thank you in advance!

[ballardw]

If you are assigning to a  variable with a defined length shorter than the result of the CATX function you might get that message. It is generally a good idea to specify a LENGTH for a variable before use long enough to hold the longest expected value before actual use in the code.

[chinna0369]

Hi, 

got it thank you so much!

I wrote my code as: "col10=catx('; ',ifc(crit1fl2='N','',crit2),ifc(crit1fl='N','',crit1),ifc(crit1fl3='N','',crit3),
ifc(crit1fl4='N','',crit4),ifc(crit1fl5='N','',crit5));"