Date:         Fri, 3 Apr 1998 16:23:43 -0500
Reply-To:     "Kowalczyk, Andrew" <AKowalczyk@NT.DMA.STATE.MA.US>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         "Kowalczyk, Andrew" <AKowalczyk@NT.DMA.STATE.MA.US>
Subject:      Re: Difficult problem with dates
Comments: To: Victor Gastanaga <vgastan@RCF.USC.EDU>
Content-Type: text/plain

This is untested, but would be my first crack at this:
DATA poi poa;
  set po;
  select (upcase(po));
    when ('I') output poi;
    when ('A') output poa;
    end;
   run;
 proc sql;
  create table res1 as
    select
       coalesce(poi.id,poa.id) as id,
       coalesce(poi.po, poa.po) as event,
       coalesce(poi.from, poa.from) as from,
       coalesce(poi.to, poa.to) as to,
       sum(cost) as cost
     from poi full join poa
        on poa.from between poi.from and poi.to
         and poa.to between poi.from and poi.to
      group by calculated id, calculated event, calculated from,
calculated to ;
 
> ----------
> From:         Victor Gastanaga[SMTP:vgastan@RCF.USC.EDU]
> Sent:         Friday, April 03, 1998 1:23 AM
> Subject:      Difficult problem with dates
>
> Hello group,
>
> This is a nice (but hard!) problem I just came across.
> This is my data set:
>
> ID  PO  FROM        TO       COST
> 1   i   01/01/95  01/05/95   90
> 1   a   01/02/95  01/02/95   80
> 1   a   03/05/95  03/05/95   30
> 1   i   03/20/95  04/01/95   95
> 2   a   01/15/95  01/15/95   44
> 2   a   02/25/95  02/25/95   40
> etc.
>
> ID and PO are character, FROM and TO are dates, COST is numeric.
> Records
> with PO="i" take several days (meaning TO > FROM), while records with
> PO="a" usually take only one day (meaning FROM=TO).
>
> This what I want to do.  If a PO="a" event takes place within the
> FROM and TO dates of a PO="i" event, then I want to combine them into
> a
> single "event", where the COST is added.  In other words, I don't want
> to
> have single records for PO="a" events that take place at the same time
> as a long PO="i" event.  However, PO="a" events that occur when no
> PO="i"
> event takes place, are kept as spearate records.
>
> This is the outcome I want for the example above:
>
> ID  EVENT   FROM       TO        COST
> 1    i      01/01/95  01/05/95   170
> 1    a      03/05/95  03/05/95   30
> 1    a      03/20/95  04/01/95   95
> 2    a      01/15/95  01/15/95   44
> 2    a      02/25/95  02/25/95   40
>
> Notice that all PO="i" and PO="a" events that take place between 01/01
> and
> 01/05 (for ID=1) are combined into a single line with cost $170.
>
> Any suggestions? Thanks in advance.
>
> Victor
>