Counting if value is recurring within 7 days

[ywon111]

I have below data and trying to add a column 'count if <7 days'

I want to count if specific ID is reoccurring within 7 days of the last one. 

Thanks in advance 🙂

ID	Date	Count if < 7 days
123	1/04/2021	0
123	5/04/2021	1
123	9/04/2021	1
123	30/04/2021	0

[Patrick · Accepted solution]

Here one way how to go about this.

data have;
  infile datalines truncover;
  input id date:ddmmyy10. result_expected;
  format date date9.;
  datalines;
123 1/04/2021 0
123 5/04/2021 1
123 9/04/2021 1
123 30/04/2021 0
;

data want;
  set have;
  by id date;
  result_calculated= date-lag(date)<7 and not first.id;
run;

 The logical expression "date-lag(date)<7 and not first.id" will return 1 if TRUE and 0 if FALSE.

[ywon111]

Thanks for the solution.
Is it possible to get working days <7 ?

[Patrick]

---

@ywon111 wrote:
Thanks for the solution.
Is it possible to get working days <7 ?

---

You don't have actual dates so you wouldn't know on which day a user started (weekday, week-end, holiday). Given that you're looking for <7 working days which will always include a week-end I guess best you could do is change the expression to: "date-lag(date)<9 and not first.id"

[ywon111]

Is there a way to add into the codes to exclude certain dates?

E.g. don't count if date = 1/04/21

datalines;
123 1/04/2021 0
123 5/04/2021 0
123 9/04/2021 1
123 30/04/2021 0