Question on adding values from colums and appending to a row based on a common value

Newbie_23 — Sat, 29 Jul 2023 14:07:46 GMT

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 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, ")");
    output;
  end;
  keep product_id description;
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

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)

Re: Question on adding values from colums and appending to a row based on a common value

mkeintz — Sat, 29 Jul 2023 17:06:24 GMT

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:

                                     product_
                              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;
run;

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

topic Question on adding values from colums and appending to a row based on a common value in SAS Programming

Question on adding values from colums and appending to a row based on a common value

Re: Question on adding values from colums and appending to a row based on a common value