CODEWITHSUNDEEP
joinsasmatchlookup
user159193
user159193

Reputation: 21

SAS how to efficiently look up and match

I have a table A of 36 columns of names. There's a name-id xwalk file, let's call it x. I need to add to table A 36 columns of IDs, using the xwalk file x.

Actual data/table sizes are provided here for efficiency consideration if needed: my table A has 340 rows, names are all texts. xwalk table x has 700+ rows, ids are all numeric. each time when a column in A of names is to be matched to IDs, xwalk can be subsetted to a smaller number of potential pools. Size of such subsets range from: 9 to 140.

I suppose I can do a left join 36 times, but it seems there should be a more efficient way to do it. any suggestions?

for simplicity, example data code for table A has 4 columns.

data A;
 input  name1 $1-5  name2 $7-10 name3 $12-15 name4 $17-20;
 datalines;
Harry Mary John Kate
Lee   Sam  Joel Sky
;
run;

data x;
input name $1-5 id;
datalines;
Harry 1
Marry 2
John  3
Kate  4
Sam   5
Sky   7
Joel  12
Lee  99
;
run;


data want;
input name1 $1-5  name2 $7-10 name3 $12-15 name4 $17-20 id1 id2 id3 id4;
datalines;
Harry Mary John Kate 1 2 3 4
Lee   Sam  Joel Sky 99 5 12 7
;
run;

Upvotes: 0

Views: 118

Answers (1)

Ken Edmonds
Ken Edmonds

Reputation: 121

Here's a hash object/array version:

data want;
    if _n_ = 1 then do;
        if 0 then set x;
        declare hash x(dataset:"x");
        x.DefineKey("name");
        x.DefineData("id");
        x.DefineDone();
    end;
    
    set a;
    array nms{4} $ name1-name4;
    array ids{4} id1-id4;

    do i = 1 to dim(nms);
        if x.find(key:nms{i}) = %sysrc(_sok) then ids{i} = id;
        else id = .;
    end;
    
    drop i id name;
run;

Upvotes: 3

Related Questions