topic Re: Concatenate with Some Missing Vars in SAS Programming

Concatenate with Some Missing Vars

altijani — Fri, 12 Oct 2018 18:57:55 GMT

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

Re: Concatenate with Some Missing Vars

sidpesar — Fri, 12 Oct 2018 19:07:48 GMT

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;

Re: Concatenate with Some Missing Vars

novinosrin — Fri, 12 Oct 2018 19:07:34 GMT

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

Re: Concatenate with Some Missing Vars

ChrisHemedinger — Fri, 12 Oct 2018 19:09:23 GMT

See: How to concatenate values in SAS.

You should be able to do something like:

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

Re: Concatenate with Some Missing Vars

altijani — Fri, 12 Oct 2018 19:15:17 GMT

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.

Re: Concatenate with Some Missing Vars

novinosrin — Fri, 12 Oct 2018 19:33:53 GMT


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;

Re: Concatenate with Some Missing Vars

ballardw — Fri, 12 Oct 2018 19:35:37 GMT

@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.

Re: Concatenate with Some Missing Vars

V_27 — Fri, 12 Oct 2018 19:42:29 GMT

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

Hi @altijani,

Try this sas code.

Re: Concatenate with Some Missing Vars

altijani — Mon, 15 Oct 2018 13:42:30 GMT

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;