Update of variables

[WilliamB]

Hello,

I have two tables, one that corresponds to the inventory and the second to the dictionary.

The problem is that missing values in my first table, names are missing.

I would like to have a sql program if possible to complete the missing values.

Thanks for your help.

Table n°1				Table n°2	dictionary
ID	Name	Sell			ID	Name
124	rabbit	14			145	dog
136		15			178	cat
1348	auk	17			164	bird
145		18			1758	monkey
178	cat	13			124	rabbit
164		17			136	fish
1758	monkey	19			1348	auk
					197	walrus
					444	giraffe
					797	tiger

And get this

ID	Name	Sell
124	rabbit	14
136	fish	15
1348	auk	17
145	dog	18
178	cat	13
164	bird	17
1758	monkey	19

[Kurt_Bremser · Accepted solution]

SQL:

proc sql;
create table want as
select
  a.ID,
  coalesce(a.name,b.name) as name,
  a.sell
from table1 a left join table2 b
on a.id = b.id;
quit;

Data step:

data want;
merge
  table1 (in=a)
  tableb (in=b rename=(name=_name))
;
by id;
if a;
if b and name = ' ' then name = _name;
drop _name;
run;

Tables must be sorted by id for the data step merge.

[Ksharp]

CODE NOT TESTED.

proc sql;

select  a.id, coalescec(a.name,b.name) as name, a.sell 

 from table1 as a left join table2 as b

  on a.id=b.id ;

quit;