Full Merge

[Ronein]

Hello

I want to perform full merge of two data sets.

I want to define the rule-

If customer  exist in one table then take Y information from this table.

IF customer exit in both tables then do as following-

IF Y value is not null in both tables then take it from table aa

IF Y value is  null in one  table  then take Y value from the table where it is not null

what is the way to do it via data step?

what is the way to do it via proc sql?

data aa;
input CustID y;
cards;
111 .
222 20
444 50
555 70
123 35
;
run;
data bb;
input CustID y;
cards;
111 15
222 20
333 35
444 .
666 80
123 27
;
run;
proc sort data=aa; by CustID;Run;
proc sort data=bb; by CustID;Run;
data want;
merge aa bb;
by CustID;
Run;

[Kurt_Bremser]

What is the relationship between those datasets?

One-to-one (as shown in your example)?

One-to-many/many-to-one?

Many-to-many?

[Ronein]

One to one

[Kurt_Bremser]

Then all you need is a DATA step. Expand your MERGE statement like this.

merge
  aa
  bb (rename=(y=y_b))
;

and use the COALESCE function to set y.

In SQL, also use COALESCE (both for aa.custid,bb.custid and aa.y,bb.y).

[Tom]

Sound like you want to use UPDATE and not MERGE.

List the dataset you want to "WIN" last.

data aa;
  input CustID y;
cards;
111 .
123 35
222 20
444 50
555 70
;
data bb;
  input CustID y;
cards;
111 15
123 27
222 20
333 35
444 .
666 80
;

data want ;
  update bb aa;
  by custid;
run;

proc print;
run;

Note that for both the datasets need to be sorted by the BY variable(s).

[dxiao2017]

Hi @Ronein , I think this is a good question. I myself feel it is not so easy to figure out the different usage of different conditions for each of them: set, merge, proc sql join/except/corr/all/coalesce😀 I spend some time and have not figure out which condition I should use which one😀

[LinusH]

If the tables are already sorted by Custid, use @Kurt_Bremsers suggestion. Otherwise SQL FULL JOIN is a simple query.

proc sql;
   create table want as
   select coalesce(aa.CustId, bb.Custid) as CustId, 
          coalesce(aa.Y, bb.Y) as Y
   from aa
   full join bb
   on aa.CustId = bb.CustId
   ;
quit;