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.
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.