Summation/counting over overlapping values or dates with group by over id's in sql

This is a surprisingly tricky problem as every row has to be compared to every other row for the same id to check for overlaps and if there are multiple overlaps you have to be very careful not to double-count them.

Here's a hash-based solution - the idea is to build up a hash containing all of the individual days a member has stayed as you go along, then count the number of items in it at the end:

data have;
input id  Entered  left;
cards;
 1    7071   7077
 1    7071   7077
 1    7075   7079
 1    7077   7083
 1    7077   7083
 1    7078   7085
 1    7087   7089
 1    7095   7099
 2    8921   8923
 2    8935   8940
 ;
 run;

data want;

length day 8;
if _n_ = 1 then do;
  declare hash h();
  rc = h.definekey('day');
  rc = h.definedone();
end;

do until(last.id);
  set have;
  by id;
  do day = entered to left - 1;
    rc = h.add();
  end;
end;

total_days = h.num_items;
rc = h.clear();
keep id total_days;

run;

This should be fairly light on memory as it only has to load the days for 1 id at a time.

The output from id 1 is 20, not 18 - here's a breakdown of the new days added row-by-row that I generated by adding a bit of debugging logic. If this is wrong, please indicate where:

_N_=1
7071 7072 7073 7074 7075 7076
_N_=2
No new days
_N_=3
7077 7078
_N_=4
7079 7080 7081 7082
_N_=5
No new days
_N_=6
7083 7084
_N_=7
7087 7088
_N_=8
7095 7096 7097 7098
_N_=1
8921 8922
_N_=2
8935 8936 8937 8938 8939

If you want to add only days for rows matching a particular condition, you can pick those using a where clause on the set statement, e.g.

  set have(where = (var1 in ('value1', 'value2', ...)));