How to take values from columns and add to a single row in SAS

[Newbie_23]

Hello,

I have a data set as shown below

Product_ID    Type      Description

12                       A           Made of wood

12                       B            Made of wood

25                      C           Made of steel

I need to get a dataset like the below, basically the values from Type column is added to the description within paranthesis

Product_ID   Description

12                     Made of wood ( A,B)

[Reeza]

Can the description be different if the type is different? How is that case handled?

[Newbie_23]

No, the description is the driver for the values to be added to the row. Please note that the description is the same for Type A and B.

[PaigeMiller]

Your desired output is a poor arrangement of the data in a data set, and makes further programming more difficult. Leave the data in the data set as is, don't try to re-arrange it. (Unless you want a report that looks something like what you showed, that's different).

[Newbie_23]

Thanks so much Reeza, you guided me in the right direction. 🙂

[Reeza]

data have;
infile cards dlm=',';
informat product_Id $8. type $8. Description $20.;
input Product_ID    Type      Description;
cards;
12, A, Made of wood
12, B, Made of wood
25, C, Made of steel
;;

proc sort data=have;
by product_id description type;
run;

data want;
set have;
length type_cat $20.;
by product_id description type;
retain type_cat;
if first.product_id then call missing(type_cat);
type_cat = catx(", ", type_cat, type);

if last.description then do;
description = catt(description, " (", type_cat, ")");
output;
end;

run;

[Newbie_23]

Hello Reeza,

Thanks for replying. But the type is added to the row only when description is the same between the products.

Any thoughts?

[Reeza]

This is why it's a good idea to show your full output. 

data want;
set have;
length type_cat $20.;
by product_id description type;
retain type_cat;
if first.product_id then call missing(type_cat);
type_cat = catx(", ", type_cat, type);

if last.description and not first.description then do;
description = catt(description, " (", type_cat, ")");
output;
end;
else if last.description then output;

run;

[Newbie_23 · Accepted solution]

data want;
set have;
by product_id description NOTSORTED; /* <-------- */

length types $20.;
retain types;

if first.description then call missing(types);
types = catx(", ", types, type);

if last.description then do;
description = catt(description, " (", types, ")");
output;
end;
keep product_id description;
run;

[Newbie_23]

data have;
infile cards dlm=',';
informat product_Id $8. type $8. Description $20.;
input Product_ID Type Description;
cards;
12, A, Made of wood
12, B, Made of wood
25, C, Made of steel
15, D, Made of iron
12, F, Made of paper
;

proc sort data=have;
by product_id description type;
run;

data want;
set have;
length type_cat $20.;
by product_id description type;
retain type_cat;
if first.product_id then call missing(type_cat);
type_cat = catx(", ", type_cat, type);

if last.description then do;
description = catt(description, " (", type_cat, ")");
output;
end;

run;

I'm expecting output to be like shown below, please help on what am i missing.Please note that the type is added to the description within paranthesis only when description is the same between the products
I'm expecting output to be like shown below
Product_ID description
12 Made of paper(F)
12 Made of Wood(A,B)
25 Made of steel (C)
15 Made of iron ( D)