DATA Step, Macro, Functions and more

SAS Teradata join

Reply
Contributor
Posts: 21

SAS Teradata join

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
Super User
Posts: 10,850

Re: SAS Teradata join

Posted in reply to nickspencer
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;
Ask a Question
Discussion stats
  • 1 reply
  • 88 views
  • 0 likes
  • 2 in conversation