BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Newbie_23
Obsidian | Level 7

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)

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Newbie_23
Obsidian | Level 7
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;

View solution in original post

9 REPLIES 9
Reeza
Super User
Can the description be different if the type is different? How is that case handled?
Newbie_23
Obsidian | Level 7
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
Diamond | Level 26

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

--
Paige Miller
Newbie_23
Obsidian | Level 7
Thanks so much Reeza, you guided me in the right direction. 🙂
Reeza
Super User
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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7
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
Obsidian | Level 7

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)

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 1418 views
  • 0 likes
  • 3 in conversation