topic Re: Full Merge in SAS Programming

Full Merge

Ronein — Sat, 27 Sep 2025 13:02:45 GMT

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;

Re: Full Merge

Kurt_Bremser — Sat, 27 Sep 2025 13:02:20 GMT

What is the relationship between those datasets?

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

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

Many-to-many?

Re: Full Merge

Ronein — Sat, 27 Sep 2025 14:04:11 GMT

One to one

Re: Full Merge

Kurt_Bremser — Sat, 27 Sep 2025 14:28:11 GMT

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

Re: Full Merge

Tom — Sun, 28 Sep 2025 01:40:31 GMT

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

Re: Full Merge

dxiao2017 — Mon, 29 Sep 2025 07:30:52 GMT

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😀

Re: Full Merge

LinusH — Mon, 29 Sep 2025 08:51:09 GMT

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;