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 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!
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.
Ready to level-up your skills? Choose your own adventure.