BookmarkSubscribeRSS Feed
Obsidian | Level 7
data have;
infile cards dlm=',';
informat product_Id $8. type $8. Description $20.;
input Product_ID    Type      Description;
12, A, Made of wood
12, B, Made of wood
25, C, Made of steel
15, D, Made of iron
12, F, Made of wood
28, G, Made of paper
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, ")");
  keep product_id description;
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
My output should look like below
Product_ID description 12 Made of paper(G) 12 Made of Wood(A,B,F)
15 Made of iron ( D) 25 Made of steel (C)


Jade | Level 19

First thank you for providing both a working DATA step with sample data, and your program code.  I appended a proc print to it, producing:


                              Obs       Id           Description

                               1        12       Made of wood (A, B)
                               2        25       Made of steel (C)
                               3        15       Made of iron (D)
                               4        12       Made of wood (F)
                               5        28       Made of paper (G)

You wanted all the "Made of wood" items together (for a given product_id) , but because they are not consecutive obs in dataset HAVE, your code doesn't group them.  This is because the basic behavior of the SET statement in a DATA step is sequential data processing.


So change the sequence of data, by means of a PROC SORT.  Something like


proc sort data=have out=need;
  by product_id description;

Then use NEED in your sas code, instead of HAVE.

The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets




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
  • 1 reply
  • 2 in conversation