SAS- Grouping consecutive dates and finding the count for each group

Question

Just trying to find the most efficient method in doing this. If you have a look at the date column below, you will find two consecutive groups where the last date of the first group being 2018-01-04 and having a total count of 4 and the second group's last date 2018-01-13 with a total count of 4 as well.

My end goal is to get the latest group or latest number of consecutive days.

EDITED: MY want table would be to show for every distinct account id to display the total count of last seen consecutive days.

For example. account id: 100012345 total_count_consec_days: 400

account id: is unique

total_count_consec_days: latest group of consecutive days for that accountid.

---Date---

2018-01-01

2018-01-02

2018-01-03

2018-01-04

2018-01-10

2018-01-11

2018-01-12

2018-01-13

I am dealing with 4000 unique accounts and they have an average of total 500 dates for each of them. If there are no efficient methods then I am happy to accept any assistance that will get me my end result. PLease help!!

Thanks!

You mention accounts, but there is no ACCOUNT variable. Is that part of your logic? Please show what code you have tried, and describe your efficiency concerns. Are the data sorted? A DATA step solution might be more straight forward that SQL. You can use LAG() or DIF() to look for gaps. Even if you have to sort it, 2M records shouldn't be that bad to sort, unless you've got a LOT of variables. — Quentin, Aug 08 '18 at 02:04
Accounts is just part of the logic. My problem is just trying to look for a way to identify those date gaps. So the lag() and DIF() can do that? — cc_hey, Aug 08 '18 at 02:08
Yep so ive sorted my table to be by account level and then by date. Sorry I might need you to ask you to dumb it down in terms of how to use the diff() and lag() functions. — cc_hey, Aug 08 '18 at 02:16
Please edit your question to show the example data you HAVE including account number. It sounds like you want to count consecutive dates within an account? So account number is part of the logic. Also show the WANT dataset you want to create. Also show the code you are trying. That will help folks help you. — Quentin, Aug 08 '18 at 02:20
Sorry Quentin. I made it more specific in terms of what my table to look like. Hope this gets rid of confusions. — cc_hey, Aug 08 '18 at 02:29

score 0 · Accepted Answer · answered Aug 08 '18 at 03:31

Presuming the data is sorted by accountid and date, a simple DOW loop over the group can discover and output the features of the most recent 'run' of consecutive dates.

Sample data

500 accounts with a couple hundred dates, starting ~3 years ago, output randomly. Every 42 accounts the case of a long run ending well in the past is forced.

data have(keep=accountid date);
  do accountid = 1 to 500;
    stopdate = .;
    do date = today()-1000 to today();
      if mod(accountid,42) = 0 then do;
        if missing(stopdate) then stopdate = today()-100 * ranuni(123);
        if today()-500 < date < stopdate then output;
      end;
      else
      if ranuni(123) > 0.15 and date < today() - accountid/10 then
        output;
    end;
  end;
  format date stopdate yymmdd10.;
run;

Sample code

For each account detect and output features of the most recent 'run' of consecutive dates

data want (keep=accountid last_:);
  do _n_ = 1 by 1 until (last.accountid);
    set have;
    by accountid date;

    if dif(date) > 1 or _n_ = 1 then do; start_n = _n_; start_date = date; end;
  end;

  last_run_length = _n_ - start_n + 1;
  last_run_start_date = start_date;
  last_run_end_date = date;

  format last_run_start_date last_run_end_date yymmdd10.;
run;

HI richard I think it worked thank you so much! I just want to get a clearer understanding though... correct me if im wrong is the 'do _n_ = 1 by 1 until (last.accountid) a loop? _n_ being the counter? — cc_hey, Aug 08 '18 at 04:45
Yes, I have repurposed the automatic `_n_` variable to be a counter for *row sequence (from 1) within group*. Automatic variables are implicitly dropped. When a gap is detected a new consecutive 'run' has begun and thus the sequence number at that time is saved for computing the run_length at the end of the group (last.accountid). — Richard, Aug 08 '18 at 05:11

SAS- Grouping consecutive dates and finding the count for each group

1 Answers1