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)
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).
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;
Hello Reeza,
Thanks for replying. But the type is added to the row only when description is the same between the products.
Any thoughts?
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;
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)
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.
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.