Summary Column for Text Data

[Cheesiepoof05]

I'm using SAS Enterprise guide and switching over from Hyperion Interactive Reporting.  I have two data columns that I'm trying to create a summary column of all the unique responses from another column.  In the example below I am trying to create the "Colors_By_Animal" column where all of the unique options of colors per animal are rolled together and separated by commas in a third column.  Duplicate colors per animal are only shown once and blanks are ignored.  Either option of adding the column in with all the data or creating a summary table with just one line per animal would work for me.  Any ideas?  Please and Thank you.

[Reeza · Accepted solution]

Here are two different methods outlined for solving this type of problem. 

https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a

data have;
    infile cards dlm='09'x;
    input OrgID Product $   States $;
    cards;
1   football    DC
1   football    VA
1   football    MD
2   football    CA
3   football    NV
3   football    CA
;
run;

*Sort - required for both options;
proc sort data=have;
    by orgID;
run;

**********************************************************************;
*Use RETAIN and BY group processing to combine the information;
**********************************************************************;
data want_option1;
    set have;
    by orgID;
    length combined $100.;
    retain combined;

    if first.orgID then
        combined=states;
    else
        combined=catx(', ', combined, states);

    if last.orgID then
        output;
run;

**********************************************************************;
*Transpose it to a wide format and then combine into a single field;
**********************************************************************;
proc transpose data=have out=wide prefix=state_;
    by orgID;
    var states;
run;

data want_option2;
    set wide;
    length combined $100.;
    combined=catx(', ', of state_:);
run;

FYI - if you provide images and people want to help answer your question with code that is tested, they would have to type out your data manually. Instead provide data at minimum as text, preferably as a data step if possible.

---

@Cheesiepoof05 wrote:

I'm using SAS Enterprise guide and switching over from Hyperion Interactive Reporting.  I have two data columns that I'm trying to create a summary column of all the unique responses from another column.  In the example below I am trying to create the "Colors_By_Animal" column where all of the unique options of colors per animal are rolled together and separated by commas in a third column.  Duplicate colors per animal are only shown once and blanks are ignored.  Either option of adding the column in with all the data or creating a summary table with just one line per animal would work for me.  Any ideas?  Please and Thank you.

---

[Cheesiepoof05]

Thank you for the quick solution. It's working exactly as I need. Sorry about not including the data properly. This was my first question and I thought it was be cleaner for people to see with the formatting but now I know for the future. Thanks again!

[Cheesiepoof05]

I'm using option 1 and just realized I'm getting duplicates if there was more than one of the same color. IE for bears, I'm getting two instances of black in the list.

Bears Black, Black, Brown, Red

[Reeza]

You can add a step before combining to remove duplicates:

proc sort data=have out=noduplicates nodupkey;
by animal individual_color;
run;

Or another method:

*Sort - required for both options;
proc sort data=have;
    by orgID states;
run;

**********************************************************************;
*Use RETAIN and BY group processing to combine the information;
**********************************************************************;
data want_option1;
    set have;
    by orgID;
    length combined $100.;
    retain combined;

    if first.orgID then
        combined=states;
    else if first.states then 
         combined=catx(', ', combined, states);

    if last.orgID then
        output;
run;

[Cheesiepoof05]

Thank you