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 |
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.
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.
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;
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.
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.