Wednesday, July 23, 2008

Treatment for overlaping timestamps in the dimensions with the history

/********************************************************
in datasets with historical records (update type 2 )
the validity of the entity has the beging and the end timestamps.
If the timestamps overlap with eachother for the same entity, this will
duplicate records when you join the dimension with a fact table.
This program will mark all overlaping time stamps.
Replace my names with yours.
Example of the overlaping timestamps:
NUMKLT DATING DATEND
234 01JAN2006 31DEC2008
234 12DEC2006 .
********************************************************/
data srclib.wgvbnd;
set srclib.wgvbnd; * data where the historical records are saved;
* datend - end datestamp;
* dating - begin date stamp;
* NUMKLT - the entity - client number;

by numklt dating;
retain prev_end;
if first.numklt then do;
prev_end=datend;
N=0;
end;
else if prev_end>dating then weird=1;else weird=.;
N+1;
drop prev_end;
run;

No comments: