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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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