Create map from values of one variable to values of another

[osbornejo]

I have a large table of information that goes something like this:

Person	Code
Abby	A123
Bob	A123
Chris	B456
Abby	B456
Deb	C789
Bob	B456
...	...

I want to map each person to their codes.  For instance:

Person	Codes
Abby	A123, B456
Bob	A123, B456
Chris	B456
Deb	C789
...	...

In essence, I want to associate each person with a list of their codes.  I'm just not sure how to do so - what data structure to use and what procedures to do it with.

[Reeza · Accepted solution]

There are two typical ways to do this, one is using a data step. 

First sort by the Person/Code then use RETAIN and concatenate the values together. When it reaches the last row, then output.

Second is to transpose it to a wide format and then use CATX() to combine all the terms. It doesn't matter if the number of records differ between the Person, the CATX() function will account for that. 

Here are fully code examples for both that you can run and test:

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;

---

@osbornejo wrote:

I have a large table of information that goes something like this:

Person	Code
Abby	A123
Bob	A123
Chris	B456
Abby	B456
Deb	C789
Bob	B456
...	...

I want to map each person to their codes.  For instance:

Person	Codes
Abby	A123, B456
Bob	A123, B456
Chris	B456
Deb	C789
...	...

In essence, I want to associate each person with a list of their codes.  I'm just not sure how to do so - what data structure to use and what procedures to do it with.

---