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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.