/* Richard A. DeVenezia
 * www.devenezia.com
 * 3/18/2004
 *
 * This material was originally posted to SAS-L on March 9, 2004
 * in thread "Using hashes to merge data", which itself arose out
 * of "how to use sql coalesce with time"
 */

/* Base data:
 * Table A records the initial state of data uniquely keyed.
 * Table B records subsequent activity states corresponding to
 * keys in table A.
 *
 * Selection desired:
 * For each key compute the time elapsed between the A initial
 * state and B activity state.  Additionally, break elapsed
 * time into two categories that should be collapsed to contain
 * a the category and a concatenation of one aspect of the table B
 * state data.
 */


/*
 * Table A - a set of initial states, uniquely keyed
 */

data a;
  do count = 1 to 1000;
    key = 10 * (int(1e7 * ranuni(0)) + 1e8);
    timestamp = int(86400 * ranuni(0));
    output;
  end;
  stop;
  keep key timestamp;
run;

/*
 * Table B - a set of activity states categorically identified
 *           with code
 */

data b;
  do count = 1 to 10000;
    x = 1 + int(1000*ranuni(0));
    set a point=x;
    tstamp = timestamp + abs(int(3600*rannor(0)));
    code = byte (26*ranuni(0)+65) || byte (26*ranuni(0)+65);
    output;
  end;
  stop;
  keep key tstamp code;
run;

/*
 * Merge A and B, concatenating code based on elapsed time categorization
 */

data _null_;

  length key timestamp 8;

  declare hash hA (dataset:'a');
  hA.defineKey  ('key');
  hA.defineData ('key');
  hA.defineData ('timestamp');
  hA.defineDone ();
  call missing (key, timestamp);

  length tstamp 8 code $2;

  declare hash hB (dataset:'b', ordered:'a');
  hB.defineKey ('key', 'tstamp');
  hB.defineData ('key');
  hB.defineData ('tstamp');
  hB.defineData ('code');
  hB.defineDone ();
  call missing (tstamp, code);

  length tstamp0 8 codes $40 class $1;

  declare hash hClump (ordered:'a');
  hClump.defineKey ('key', 'class');
  hClump.defineData ('key');
  hClump.defineData ('class');
  hClump.defineData ('codes');
  hClump.defineData ('tstamp0');
  hClump.defineDone ();
  call missing (tstamp0, codes, class);

  declare hiter hi ('hB');

  if hi.first() = 0 then
  do until (hi.next() ne 0);

    if ha.find() ne 0 then continue;

    elapsed = tstamp - timestamp;
    if elapsed < 0 then
      class = '*';
    else
    if elapsed <= '0:15:00't then
      class='<';
    else
      class = '>';

    if hClump.find () = 0 then do;
      codes = trim(codes) || ' ' || code;
      if tstamp < tstamp0 then tstamp0 = tstamp;
      rcclump = hClump.replace();
    end;
    else do;
      codes = code;
      tstamp0 = tstamp;
      rcclump = hClump.add();
    end;
  end;

  hClump.output (dataset:'Clumps');

  stop;
run;

ods listing close;
ods html file="%sysfunc(pathname(WORK))\clumps.html";

proc report nowindows data=clumps(obs=100);
  format tstamp0 time8.;
  format key 12.;
  define key / order;
  compute key;
    if key ne '' then call define (_row_, 'style', 'style={background=cxeeffee}');
  endcomp;
run;

ods html close;