topic Substitute value of column with other table. in SAS Programming

Substitute value of column with other table.

Aditi24 — Fri, 09 Jun 2017 11:13:22 GMT

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

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:-

Re: Substitute value of column with other table.

Astounding — Fri, 09 Jun 2017 11:24:27 GMT

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.

Re: Substitute value of column with other table.

gamotte — Fri, 09 Jun 2017 11:58:43 GMT

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;

Re: Substitute value of column with other table.

PeterClemmensen — Fri, 09 Jun 2017 12:32:18 GMT

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;