Use Lookup table in SAS to replace values in original table

[SAS_New_User1]

I have 2 datasets 

Dataset country

1  USA               Blue          32

2  UK.               Red           15

3  Australia       Green        52

5  Africa            Yellow        44

Dataset animal

1     15        Deer

2     52        Kangaroo

3     44        Lion

4     32       Bison

How do I use this lookup table to get the below output in the country dataset

Dataset country

1  USA               Blue          Bison

2  UK.               Red           Deer

3  Australia       Green        Kangaroo

5  Africa            Yellow        Lion

[ballardw]

Do have an actual RULE for how these get combined.

What is that first column of numbers you show?

What are the names of the variables?

This might be a simple Data step merge or Proc SQL join on the variable that is showing values 15, 32, 52 and 44.

Maybe. Depends on if there are duplicates of the values and what that might do to the result.

[SAS_New_User1]

No rule, just a simple table. The first column is the unique identifier.

[ballardw]

---

@SAS_New_User1 wrote:
No rule, just a simple table. The first column is the unique identifier.

---

Hard to believe there is no rule involved at all. Otherwise the result you show is just of about 24 possible outcomes. So why is the result you show the one that is wanted? That is the rule (s) needed.

Variable names? So we can talk about how to use any specific values.

Variable types? If a value in one data set is numeric and character in the other then additional steps will be needed.

[Reeza]

Your rule is merge by unique identifier.

proc sort data=t1; by ID;
proc sort data=t2; by ID;
data want;
merge t1 t2 (keep = ID colour);
by ID;
run;

---

@SAS_New_User1 wrote:
No rule, just a simple table. The first column is the unique identifier.

---

[Tom]

So you want to change 32 into Bison?  Looks like you just want to make a format and not actually change the original dataset at all.  Example:

data country ;
  input id name :$20. color :$10. code ;
cards;
1  USA               Blue          32
2  UK.               Red           15
3  Australia       Green        52
5  Africa            Yellow        44
;

data animal ;
  input id code animal $20. ;
cards;
1     15        Deer
2     52        Kangaroo
3     44        Lion
4     32       Bison
;

data fmt ;
set animal ;
fmtname='ANIMAL';
rename code=start animal=label;
run;

proc format cntlin=fmt ;
run;

proc print data=country;
  format code animal. ;
run;

Results:

Obs    id    name         color     code

 1      1    USA          Blue      Bison
 2      2    UK.          Red       Deer
 3      3    Australia    Green     Kangaroo
 4      5    Africa       Yellow    Lion