Date:         Thu, 11 Mar 2004 13:49:38 +0200
Reply-To:     Arto Raiskio <arto-nospam@RAISKIO.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Arto Raiskio <arto-nospam@RAISKIO.COM>
Subject:      Re: how to use sql coalesce with time

here's a solution, in this sample, xx=2 or xx=1 depending on occurrence in
data a

data a;input record1 $10. xcode $ timestamp time.;datalines;
123456770 A 07:27:00
123456780 A 08:10:00
123456790 A 08:11:00
123456770 A 14:01:00   /* 2nd time, over 6hr later */
;
run;
data b;input record1 $10. ycode $ tstamp time.;datalines;
123456770 AJ 07:28:22  /* 1 min after data a */
123456770 AK 07:29:51  /* 5 min after data a */
123456780 MA 08:15:00  /* 5  min after data a */
123456780 MB 08:19:00  /* 4  min after data a */
123456790 MA 08:20:01  /* 10 min after data a */
123456790 MB 08:22:01  /* 11 min after data a */
123456780 MC 09:29:00  /* over 1 hour after data a */
123456770 PJ 14:02:00  /* 1 min after data a 2nd time */
123456770 PJ 14:15:00  /* 14 min after data a 2nd time */
;
run;

proc sort data=a;by record1;
data a;set;by record1; if first.record1 then xx=0;
if ~first.record1 or ~last.record1 then xx+1;else xx=1;
run;
proc sort data=b;by record1;

%let maxcodes = 13;
%let cutoff = '00:15:00't;
%let cutneg = '-00:00:01't;
data b_concatenated_transpose;

  codeindex = 1;
  do until (last.record1);
    merge a(where=(xx=2)) b; /* xx=1 xx=2 xx=3 etc */
    by record1;
    format elapsedtime timestamp tstamp time.;
    length codes $%eval((&maxcodes-1)*3+2);

    elapsedtime = tstamp - timestamp;
    if elapsedtime > &cutoff and codeindex > 1 then do;
  IF XX=2 THEN DO;
       output;put "RESET " (_all_)(=) ;
      codes = '';
      codeindex = 1;
  END;
    end;

    if codeindex = 1 then tstamp0 = tstamp;
    if elapsedtime < &cutneg then;
  else
      substr(codes,codeindex,2) = ycode;
      * put elapsedtime= ycode=;
    codeindex+3;
  end;

  if codeindex > 1 and xx=2 then do;
    output;put "INDEX>1" (_all_)(=);
  end;
  keep record1 codes tstamp0;
  format tstamp0 time8.;
run;