I tried to add a dataset for march and I'm not getting results.
Yes i know. Hence the reason i admitted I made a mistake. Just got back to my computer. Let me take a closer look
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 .
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: