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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.