BookmarkSubscribeRSS Feed
nickspencer
Obsidian | Level 7
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
1 REPLY 1
Ksharp
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 574 views
  • 0 likes
  • 2 in conversation