Conditional extraction of dates

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Conditional extraction of dates

 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.


Accepted Solutions
Solution
‎01-22-2016 10:02 AM
Grand Advisor
Posts: 9,593

Re: Conditional extraction of dates

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;

View solution in original post


All Replies
Solution
‎01-22-2016 10:02 AM
Grand Advisor
Posts: 9,593

Re: Conditional extraction of dates

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;
Occasional Contributor
Posts: 5

Re: Conditional extraction of dates

Wow keshan, this is exactly what i need, thanks a ton.
Esteemed Advisor
Posts: 6,698

Re: Conditional extraction of dates

Then please have the courtesy and mark Ksharp's post as the solution.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 341 views
  • 1 like
  • 3 in conversation