Date:         Wed, 27 Oct 2004 10:26:01 -0700
Reply-To:     James Green <jamesgreen55@YAHOO.CA>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         James Green <jamesgreen55@YAHOO.CA>
Organization: http://groups.google.com
Subject:      Help! Macro / SAS or SQL? Loop?
Content-Type: text/plain; charset=ISO-8859-1

Hi Folks;

I have a dilema; Based on the sample below I need to calculate how old
a Help Desk Ticket is based upon 4 variables which are on 4 separate
activities.

The Open and Resolved Dates are fixed for each activity at the ticket
level however ;  each activitity could have a suspended time.

(A suspend occurs when the Agent is waiting for a response from a
customer and cannot continue until the customer replies).

My Issue is this, if I calculate the Suspension time for each activity
and sum them up to the case level, I will end up double counting
suspension times which have overlapping dates timespans. (I need to
find out how long the ticket has been open (Age), and worked on by the
Help desk not counting the times the customer casued the delay).

ie

in the sample below; the 1st ticket [123456] would have a total
suspension time of 27 Days however; the case itself is only 19 Days
old..

(Age=Resolved_Date-Open_Date(19)-Total Suspended Time(27) so the
result is -8 which of course does not make sense..) The Answer Should
be 2.

1st record does not overlap (Count=3), 2nd And Third records do
overlap, so you can only count 2 of the days from the Second Record,
and 12 from the second. (Total Suspend Time=3+2+12=17. 19-17=2). The
Help desk therefore only spent 2 days working on the problem, the
remaining time was in the customers hands.

How would I address this ? any ideas? Is this a macro or SQL?

I need to factor in the following;

Possibility that suspension ends beyond the Resolved Date. (if this
occurs change suspension end to resolved_date).

Possibility that there are no suspensions at all.

Possibility that the Suspension End is missing. (if this occurs, use
current date).

Possibility that the Case has no Resolved_Date (Ticket is Open, use
current date to calculate Age).


data xx;
input Case_Num $1-6
          Status $8-13
      Open_Date : mmddyy.
      Resolved_Date : mmddyy.
      Suspension_Start : mmddyy.
      Suspension_End : mmddyy.
          ;
          format
                Open_Date mmddyy.
                Resolved_Date mmddyy.
                Suspension_Start mmddyy.
                Suspension_End mmddyy.
          ;
cards;
123456 Closed 9/1/04    9/20/04 9/2/04  9/5/04
123456 Closed 9/1/04    9/20/04 9/6/04  9/18/04
123456 Closed 9/1/04    9/20/04 9/8/04  .
789101 Closed 8/5/04    8/27/04 8/6/04  8/15/04
789101 Closed 8/5/04    8/27/04 8/6/04  8/20/04
567891 Closed 10/5/04   10/26/04        10/8/04 10/15/04
567891 Closed 10/5/04   10/26/04        10/10/04        10/16/04
567891 Closed 10/5/04   10/26/04        10/12/04        10/20/04
567891 Closed 10/5/04   10/26/04        10/12/04        10/20/04
567891 Closed 10/5/04   10/26/04        .       .
456789 Closed 10/10/04  10/25/04        10/10/04        10/11/04
456789 Closed 10/10/04  10/25/04        10/10/04        10/20/04
456789 Closed 10/10/04  10/25/04        10/12/04        10/28/04
456789 Closed 10/10/04  10/25/04        10/14/04        10/17/04
run;