Substitute value of column with other table.

[Aditi24]

I have two tables, Table 1 containing below data:-

Employee ID| designation          | Salary
==========================================
0x3546789 | Software Engineer| 20000$
John           | Manager               | 40000$
0x762381   | Analyst                  | 30000$
Albert         | DBA                       | 50000$

and a reference table which contains the mapping of hexadecimal IDs to their corresponding values.

Table 2:-
Employee ID| Name
==================
0x3546789   | Keith
0x5423178   | Charles
0x762381     | Andrew

I want to resolve hexadecimal Employee ID to the names so that my final table looks like:-

Employee ID| Designation           | Salary
==========================================
Keith            | Software Engineer| 20000$
John            | Manager               | 40000$
Andrew        | Analyst                  | 30000$
Albert           | DBA                      | 50000$

[Astounding]

One easy way:  create a format.

data namelist;

set table_2 end=done;

retain fmtname '$name';

start=employee_id;

label=name;

output;

if done;

hlo='O';

label='NOT FOUND';

output;

run;

proc format cntlin=namelist;

run;

Then you can apply the format to Table_1:

data want;

set table_1;

test_name = put(name, $name.);

if test_name ne 'NOT FOUND' then name = test_name;

drop test_name;

run;

The code is untested, so minor tweaking might be necessary.

[gamotte]

Hello,

A solution with proc sql :

proc sql noprint;
	CREATE TABLE WANT AS
	SELECT COALESCE(h2.Name,h1.ID) AS Name, h1.designation, h1.Salary
	FROM have1 h1
	LEFT JOIN have2 h2
	ON h2.ID=h1.ID;
quit;

[PeterClemmensen]

Like this?

data table1;
length Employee_ID $20 designation $30;
input Employee_ID$ designation$ Salary; 
datalines;
0x3546789 Software_Engineer 20000
John Manager 40000
0x762381 Analyst 30000
Albert DBA 50000
;

data table2;
length Employee_ID $20.;
input Employee_ID$ Name$;
datalines;
0x3546789 Keith
0x5423178 Charles
0x762381 Andrew
;

data want(drop = Name rc);
	length Name $20.;
	if _N_ = 1 then do;
		declare hash h(dataset:'table2');
		h.defineKey('Employee_ID');
		h.defineData('Name');
		h.defineDone();
	end;

	set table1;
	rc = h.find();
	
	if rc=0 then Employee_ID = Name;
run;