SAS Teradata join

nickspencer — Fri, 11 May 2018 13:31:19 GMT

I have a SAS data set as follows.
RecordNo ID1 ID2 ID3
001 A001 A112 A511
002 A005 A234 A345
003 A002 A123 A112
004 A003 A004 A234

I need to create another dataset from the above dataset by replacing ID1, ID2, ID3 by name1, name2, name3 resp. But the lookup for id and name is in teradata table like below
Id Name
A001 Rick
A002 David
A003 Ron
A004 Pat
A005 Tom

So I need to use this teradata table to create the data set loke below.
RecordNo Name1 Name2 Name3
001 Rick Sam Nick
002 Tom Geoff Kate

What can be the best or efficient way to get those values from the teradata table? Any help is appreciated

Re: SAS Teradata join

Ksharp — Fri, 11 May 2018 13:55:46 GMT

data a;
input (RecordNo ID1 ID2 ID3) ($);
cards;
001 A001 A112 A511
002 A005 A234 A345
003 A002 A123 A112
004 A003 A004 A234
;
run;

data b;
input (Id Name) ($);
cards;
A001 Rick
A002 David
A003 Ron
A004 Pat
A005 Tom
;
run;

data want;
 if _n_=1 then do;
  if 0 then set b;
  declare hash h(dataset:'b');
  h.definekey('id');
  h.definedata('name');
  h.definedone();
 end;
 set a;
 array x{*} $ 32 name1-name3;
 array y{*} $ 32 id1-id3;

 do i=1 to dim(x);
   call missing(name);
   id=y{i};
   rc=h.find();
   x{i}=name;
 end;

 drop i id name rc;
 run;

topic Re: SAS Teradata join in SAS Programming

SAS Teradata join

Re: SAS Teradata join