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$
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.
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.