topic Re: Use Lookup table in SAS to replace values in original table in New SAS User

Use Lookup table in SAS to replace values in original table

SAS_New_User1 — Thu, 21 Jan 2021 16:38:20 GMT

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

Re: Use Lookup table in SAS to replace values in original table

ballardw — Thu, 21 Jan 2021 16:47:07 GMT

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.

Re: Use Lookup table in SAS to replace values in original table

SAS_New_User1 — Thu, 21 Jan 2021 16:48:11 GMT

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

Re: Use Lookup table in SAS to replace values in original table

ballardw — Thu, 21 Jan 2021 17:02:20 GMT

@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.

Re: Use Lookup table in SAS to replace values in original table

Reeza — Thu, 21 Jan 2021 17:57:01 GMT

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.

Re: Use Lookup table in SAS to replace values in original table

Tom — Thu, 21 Jan 2021 17:27:22 GMT

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