DATA Step, Macro, Functions and more

Update of variables

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Update of variables

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

Accepted Solutions
Solution
‎04-24-2018 09:52 AM
Super User
Posts: 10,280

Re: Update of variables

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
‎04-24-2018 09:52 AM
Super User
Posts: 10,280

Re: Update of variables

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,787

Re: Update of variables

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 91 views
  • 0 likes
  • 3 in conversation