Updating column in merged dataset based on comparison with old dataset

[OLUGBOJI]

I tried to add a dataset for march and I'm not getting results.

[novinosrin]

Yes i know. Hence the reason i admitted I made a mistake. Just got back to my computer. Let me take a closer look

[novinosrin]

Hi @OLUGBOJI 

data jan;
infile cards truncover;
input id className $ startdate  :ddmmyy10.       enddate :ddmmyy10. ;
format startdate  ddmmyy10.       enddate ddmmyy10. ;
cards;
1   A               01/01/2019
1   B               01/01/2019
2   A               01/01/2019
3   B               01/01/2019
;

data feb;
infile cards truncover;
input id    className $  startdate  :ddmmyy10.     enddate  :ddmmyy10. ;
format startdate  ddmmyy10.       enddate ddmmyy10. ;
cards;
1       A               01/02/2019
2       A                01/02/2019
3       B                01/02/2019
;

data mar;
infile cards truncover;
input id    className $  startdate  :ddmmyy10.     enddate  :ddmmyy10. ;
format startdate  ddmmyy10.       enddate ddmmyy10. ;
cards;
1   A             01/03/2019
1   B               01/03/2019
3   B            01/03/2019
;

/*Look up Jan to feb*/
data jan_to_feb ;
if _n_=1 then do;
   dcl hash H (dataset:'feb',multidata:'y') ;/*replace subsequent months like march here*/
   h.definekey  ("id","classname") ;
   h.definedata ("startdate", "enddate") ;
   h.definedone () ;
   dcl hiter hh('h');
end;
set jan end=lr;/*jan is like your old full load*/
by id classname;
if last.classname then 
do;
	rc=h.check();
	if rc=0 then do;
		output;
		do rc= h.find() by 0 while(rc=0);
			output;
			rc=h.find_next();
		end;
		end;
	else do; enddate=startdate;output;end;
end;
else output;
drop rc;
run;

RESULTS:

id className startdate enddate 
1 A 01/01/2019 . 
1 A 01/02/2019 . 
1 B 01/01/2019 01/01/2019 
2 A 01/01/2019 . 
2 A 01/02/2019 . 
3 B 01/01/2019 . 
3 B 01/02/2019 . 

Now moving to the 2nd look up the previous result with March

/*Look up Janfeb(the output of previous
to march*/
data jan_to_feb___to_mar ;
if _n_=1 then do;
   dcl hash H (dataset:'mar',multidata:'y') ;/*replace subsequent months like march here*/
   h.definekey  ("id","classname") ;
   h.definedata ("startdate", "enddate") ;
   h.definedone () ;
   dcl hiter hh('h');
end;
set jan_to_feb end=lr;/*jan_to_feb is like your old full load*/
by id classname;
if last.classname then 
do;
	rc=h.check();
	if rc=0 then do;
		output;
		do rc= h.find() by 0 while(rc=0);
			output;
			rc=h.find_next();
		end;
		end;
	else do; enddate=startdate;output;end;
end;
else output;
drop rc;
run;

RESULTS:

id className startdate enddate 
1 A 01/01/2019 . 
1 A 01/02/2019 . 
1 A 01/03/2019 . 
1 B 01/01/2019 01/01/2019 
1 B 01/03/2019 . 
2 A 01/01/2019 . 
2 A 01/02/2019 01/02/2019 
3 B 01/01/2019 . 
3 B 01/02/2019 . 
3 B 01/03/2019 . 

[tomrvincent]

Here's how I approached it:

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_JAN AS
SELECT t1.id,
t1.className,
t1.startdate,
t2.id AS id1,
t2.className AS className1,
t2.startdate AS startdate1,
t3.id AS id2,
t3.className AS className2,
t3.startdate AS startdate2,
/* enddate */
(case when t2.startdate is missing then t1.startdate when t3.startdate is missing then t2.startdate end)
FORMAT=date9. AS enddate
FROM WORK.JAN t1
LEFT JOIN WORK.FEB t2 ON (t1.id = t2.id) AND (t1.className = t2.className)
LEFT JOIN WORK.MAR t3 ON (t2.id = t3.id) AND (t2.className = t3.className);
QUIT;