Concatenate with Some Missing Vars

[altijani]

Greetings Community,

I have the following data:

Customer	Item1	Item2	Item3
John	Shoes
David	Bags	Shirts
Natasha	Shoes	Shirts	Bags

I need to concatenate the items in one var as follows. Please note for some customers it can go up to 10 items:

Customer	Items
John	Shoes
David	Bags, Shirts
Natasha	Shoes, Shirts, Bags

Thank you

[sidpesar]

proc sql;

 create table want as select customer , cats(col1,col2,col3..) as concat_items from have;

quit;

to automate:

proc sql noprint;
select name
into :name_col separated by ','
from dictionary.columns
where upcase(memname) = 'have'
and upcase(name) like 'item%' ;
quit ;

proc sql;
create table want as select customer , cats(&name_col.) as concat_items from have;
quit;

[novinosrin]

data have;
infile cards truncover;
input Customer :$10.	(Item1	Item2	Item3) ($);
cards;
John	Shoes	 	 
David	Bags	Shirts	 
Natasha	Shoes	Shirts	Bags
;

data want;
set have;
length concat $50;
concat=catx(',',of item:);
run;

Proc sql does not support variable lists, nor array processing etc. These kind of operations are best performed in a datastep, plus I think you need catx instead of cats with delim ',' in between 

[ChrisHemedinger]

See: How to concatenate values in SAS.

You should be able to do something like:

  newValue = catx(', ', of item:);

[altijani]

Thank you for your suggested code.
I tried these, and my issue the trailing commas.

In case of Natasha, all is fine.
In case of David, there are 2 trailing commas
In case of Smith, there is a 1 trailing comma

Got my point?

That what I would like to solve.

[ballardw]

---

@altijani wrote:

Thank you for your suggested code.
I tried these, and my issue the trailing commas.

In case of Natasha, all is fine.
In case of David, there are 2 trailing commas
In case of Smith, there is a 1 trailing comma

Got my point?

That what I would like to solve.

---

Show the code you used. There were several solutions suggested and without the actual code you used it is hard to recommend additional pieces.

[altijani]

Here is the code I am trying to use:

proc sql;

CREATE TABLE WANT AS

SELECT DISTINCT

Customer,

Item1,

Item2,

Item3,

(CATX (" ", Item1, ", ", Item2, ", ", Item3)) AS Items

FROM Have;

[novinosrin]

data have;
infile cards truncover;
input Customer :$10.	(Item1	Item2	Item3) ($);
cards;
John	Shoes	 	 
David	Bags	Shirts	 
Natasha	Shoes	Shirts	Bags
;
data want;
set have;
array t(*) $20 item:;
length concat $50;
concat = peekc(addr (t[1]),20);
run;

[V_27]

data want;
   set have;
   Item_combined=strip(tranwrd(catx(", ",Item1, Item2, Item3),"",""));
   drop Item1-Item3;
run;

Hi @altijani,

Try this sas code.