BookmarkSubscribeRSS Feed
Aditi24
Obsidian | Level 7

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$

3 REPLIES 3
Astounding
PROC Star

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
Rhodochrosite | Level 12

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
Tourmaline | Level 20

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;

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
  • 3 replies
  • 843 views
  • 2 likes
  • 4 in conversation