Date:         Wed, 17 Oct 2001 14:50:46 -0400
Reply-To:     Jay Weedon <jweedon@EARTHLINK.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jay Weedon <jweedon@EARTHLINK.NET>
Organization: http://extra.newsguy.com
Subject:      Re: DATA - splitting a record into multiple records
Content-Type: text/plain; charset=us-ascii

On 17 Oct 01 01:26:54 GMT, adixon@DOH.HEALTH.NSW.GOV.AU (Andrew Dixon)
wrote:

>Hi all,
>Any tips on how to get from this form of sick-leave SAS dataset
>
>ID   Startdate   Enddate    Hours ... (other variables)
>01 "10/01/00"  "11/01/00"   16
>01 "16/06/00"  "16/06/00"     8
>02 "01/01/00"  "01/01/00"     4
>etc
>
>to this form of sickleave SAS dataset
>
>ID   Startdate   Enddate    Hours ... (other variables)
>01 "10/01/00"  "10/01/00"     8
>01 "11/01/00"  "11/01/00"     8
>01 "16/06/00"  "16/06/00"     8
>02 "01/01/00"  "01/01/00"     4
>etc
>?
>ie I need to split the records up so that each record represents a day when leave was taken rather than the leave period.
>I suspect that getting the Hours correct will be tricky (it's not always a multiple of 8, and sometimes the dates span a weekend etc), so if someone can just get me started on splitting up the records I'll fiddle with the Hours later.

I guess there's some ambiguity if the number of days is >1 and the
number of hours is not 8 times the number of days; you won't
necessarily know which hours belong to which days. The following
solution assumes 8 for the first day and so on, with the residual
being on the last day. The dates I've converted to SAS dates: If you
don't like it you can change the code in line 4 to

startdate_=put(day,ddmmyy8.); enddate_=startdate_;

I allow for weekends but ignore holidays - my excuses are

(1) I don't remember the precise dates of Australia Day, ANZAC Day, or
National Drongo Week;

(2) I've mislaid my Book of Common Prayer that I'd need to consult for
the algorithm that computes the date of Easter :-)

data new;
  set old;
  do day=input(startdate,ddmmyy8.) to input(enddate,ddmmyy8.);
    if weekday(day)^in (1,7) then do;
      startdate_=day; enddate_=day;
      hours_=min(hours,8); hours=hours-hours_; output;
    end;
  end;
  format startdate_ enddate_ ddmmyy8.;
  drop day hours startdate enddate;
run;

JW