CODEWITHSUNDEEP
sas
nikhila reddy
nikhila reddy

Reputation: 63

usage of update and merge in SAS

I have 2 datasets like below.

dataset ab;
input m;
cards;
   102
   103
   104
run;

dataset ac;
input m;
cards;
102
102 
103
103
104
104
104
run;

when i wrote the below statement,

data a;
merge ab ac;
by m;
run;

I got the output as 102 102 103 103 104 104 104

but when i wrote update statement,

data b;
update ab ac;
by m;
run;

i got output as 102 103 104.

Can you please explain me what has happened in the update statement.

Thanks in Advance,
Nikhila

Upvotes: 1

Views: 58

Answers (2)

data _null_
data _null_

Reputation: 9109

data ab;
   input m @@;
   cards;
101 102 103 104
;;;;
   run;
data ac;
   input m @@;
   cards;
102 102  103 103 104 104 104
;;;;
   run;

data b;
   update ab ac(in=in1);
   by m;
   if first.m then tCount=0;
   tCount + in1;
   run;
proc print;
   run;

enter image description here

Upvotes: 0

Reeza
Reeza

Reputation: 21294

Update applies the transactions 1 by 1. The master table is required to have Unique BY values which is true. The transaction table has multiples, but doesn't have any new values so they are not added.

If the transaction had a BY value not in the table it would add it.

With an UPDATE and BY the following may help:

  1. BY value is in transaction dataset AND master - >records in master are updates with values from transaction. If there are multiple records in the transaction table for a BY group they're each applied in order. There will only be one record in the Master table with the value from the last match in the transaction table.
  2. BY value in transaction, not in master -> Record is added to master table
  3. BY value is not in transaction, is in Master -> record in master remains unchanged.

This would be easier to see if you add a second variable to your test datasets that are unique.

Upvotes: 2

Related Questions