I came up with this not very elegant solution, but it seems to work. better solutions or comments on this one are highly appreciated. proc sql;
* Added rows;
create table added as
select b.ID
, b.date1 as valid_from
, '31dec9999'd as valid_to
, b.amount
, "A" as changetype
from have2 b
left outer join have1 a
on a.ID = b.ID
where a.ID = ''
;
* not changed rows;
create table noChange as
select a.ID
, a.date1 as valid_from
, '31dec9999'd as valid_to
, a.amount
, "N" as changetype
from have1 a
left outer join have2 b
on a.ID = b.ID
where (b.ID = '')
or (a.amount=b.amount)
;
* modified rows;
create table modified_before as
select a.ID
, a.date1 as valid_from
, b.date1 as valid_to
, a.amount
, "M" as changetype
from have1 a
inner join have2 b
on a.ID = b.ID
where a.amount^=b.amount;
create table modified_after as
select b.ID
, b.date1 as valid_from
, '31dec9999'd as valid_to
, b.amount
, "M" as changetype
from have1 a
inner join have2 b
on a.ID = b.ID
where a.amount^=b.amount;
quit;
data test4;
set added noChange modified_before modified_after;
run;
... View more