BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
WilliamB
Obsidian | Level 7

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°2dictionary 
       
       
       
IDNameSell  IDName
124rabbit14  145dog
136 15  178cat
1348auk17  164bird
145 18  1758monkey
178cat13  124rabbit
164 17  136fish
1758monkey19  1348auk
     197walrus
     444giraffe
     797tiger

 

 

 

And get this

 

 

IDNameSell
124rabbit14
136fish15
1348auk17
145dog18
178cat13
164bird17
1758monkey19
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

2 REPLIES 2
Kurt_Bremser
Super User

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
Super User

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;

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