I need help to extract START_DATE and END_DATE at case level using a QUEUENAME and ACTIONCODE
Requirement: when QUEUENAME=' Approve Credit App' and ACTIONCODE ='Rework' then START_DATE=outdate and when very next QUEUENAME=' Approve Credit App' (dont need to consider actioncode) then END_DATE =INDATE
FOR RECORDS 6 & 7 QUEUENAME=' Approve Credit App' and ACTIONCODE ='Rework', here START_DATE=outdate 6th record and END_DATE =7th record AND for
7th record where QUEUENAME=' Approve Credit App' and ACTIONCODE ='Rework' START_DATE=outdate 7th record and
END_DATE =INDATE OF 8th record AND for
CASENUMBER QUEUENAME ACTIONCODE INDATE OUTDATE
00000000001 Approve Credit App Rework 08Mar2012 14:41:19.473 08Mar2012 18:22:02.477
00000000001 Approve Credit App Approve 13Mar2012 15:10:00.767 13Mar201215:39:07.83
00000000001 Approve Credit App 27Mar2012 18:53:19.463 29Mar2012 9:12:00.577
00000000001 Approve Credit App Rework 29Mar2012 10:13:35.383 29Mar2012 16:59:33.7
00000000001 Approve Credit App Approve 30Mar2012 11:25:56.843 30Mar2012 11:49:33.530
00000000001 Approve Credit App Rework 04Apr2012 14:41:19.473 06Apr2012 18:22:02.477
00000000001 Approve Credit App Rework 07Apr2012 15:10:00.767 13Apr2012 15:39:07.83
00000000001 Approve Credit App COMPLETED 14Apr2012 18:53:19.463 15Apr2012 9:12:00.577
Please help with possible condtions to solve this.
Thanks in Advaince,
Arjin.
if I know what is your mean.
data have;
input CASENUMBER : $40. QUEUENAME & $40. ( ACTIONCODE INDATE OUTDATE) ( :$40.);
cards;
00000000001 Approve Credit App Rework 08Mar2012 14:41:19.473 08Mar2012 18:22:02.477
00000000001 Approve Credit App Approve 13Mar2012 15:10:00.767 13Mar201215:39:07.83
00000000001 Approve Credit App . 27Mar2012 18:53:19.463 29Mar2012 9:12:00.577
00000000001 Approve Credit App Rework 29Mar2012 10:13:35.383 29Mar2012 16:59:33.7
00000000001 Approve Credit App Approve 30Mar2012 11:25:56.843 30Mar2012 11:49:33.530
00000000001 Approve Credit App Rework 04Apr2012 14:41:19.473 06Apr2012 18:22:02.477
00000000001 Approve Credit App Rework 07Apr2012 15:10:00.767 13Apr2012 15:39:07.83
00000000001 Approve Credit App COMPLETED 14Apr2012 18:53:19.463 15Apr2012 9:12:00.577
;
run;
data want;
merge have have(firstobs=2 keep= QUEUENAME INDATE
rename=(QUEUENAME=_q INDATE=_i));
if QUEUENAME=_q and QUEUENAME='Approve Credit App' and ACTIONCODE ='Rework' then do;
START_DATE=outdate;
END_DATE =_i;
end;
drop _q _i;
run;
if I know what is your mean.
data have;
input CASENUMBER : $40. QUEUENAME & $40. ( ACTIONCODE INDATE OUTDATE) ( :$40.);
cards;
00000000001 Approve Credit App Rework 08Mar2012 14:41:19.473 08Mar2012 18:22:02.477
00000000001 Approve Credit App Approve 13Mar2012 15:10:00.767 13Mar201215:39:07.83
00000000001 Approve Credit App . 27Mar2012 18:53:19.463 29Mar2012 9:12:00.577
00000000001 Approve Credit App Rework 29Mar2012 10:13:35.383 29Mar2012 16:59:33.7
00000000001 Approve Credit App Approve 30Mar2012 11:25:56.843 30Mar2012 11:49:33.530
00000000001 Approve Credit App Rework 04Apr2012 14:41:19.473 06Apr2012 18:22:02.477
00000000001 Approve Credit App Rework 07Apr2012 15:10:00.767 13Apr2012 15:39:07.83
00000000001 Approve Credit App COMPLETED 14Apr2012 18:53:19.463 15Apr2012 9:12:00.577
;
run;
data want;
merge have have(firstobs=2 keep= QUEUENAME INDATE
rename=(QUEUENAME=_q INDATE=_i));
if QUEUENAME=_q and QUEUENAME='Approve Credit App' and ACTIONCODE ='Rework' then do;
START_DATE=outdate;
END_DATE =_i;
end;
drop _q _i;
run;
Then please have the courtesy and mark Ksharp's post as the solution.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.