topic Re: How to transpose & combine a text field with multiple values for each ID? in SAS Programming

How to transpose & combine a text field with multiple values for each ID?

pfdjadsfdsafas — Thu, 06 Jun 2019 20:50:16 GMT

Here is what I have:

ID Category
1 Dog
1 Cat
2 Horse
3 Dog
3 Horse
3 Cat

And what I need is something along the lines of this:

ID Category
1 Dog + Cat
2 Horse
3 Dog + Cat + Horse

I can think of some very roundabout ways to do this but there has to be a simple quick way. Thanks!

Re: How to transpose & combine a text field with multiple values for each ID?

Tom — Thu, 06 Jun 2019 21:03:40 GMT

It is pretty trivial. Note that you will need to make a NEW variable to hold the list. You will probably need to make it longer than the original variable.

data have;
  input ID Category $20.;
cards;
1 Dog
1 Cat
2 Horse
3 Dog
3 Horse
3 Cat
;

data want ;
  do until (last.id);
    set have;
    by id;
    length new_category $200 ;
    new_category=catx(' + ',new_category,category);
  end;
  drop category;
run;

Obs    ID    new_category

 1      1    Dog + Cat
 2      2    Horse
 3      3    Dog + Horse + Cat