Create map from values of one variable to values of another

osbornejo — Fri, 21 Jan 2022 17:27:05 GMT

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.

Re: Create map from values of one variable to values of another

Reeza — Fri, 21 Jan 2022 17:44:32 GMT

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.

topic Create map from values of one variable to values of another in New SAS User

Create map from values of one variable to values of another

Re: Create map from values of one variable to values of another