topic Re: Counting if value is recurring within 7 days in SAS Programming

Counting if value is recurring within 7 days

ywon111 — Thu, 17 Jun 2021 03:31:36 GMT

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

Re: Counting if value is recurring within 7 days

Patrick — Thu, 17 Jun 2021 04:13:19 GMT

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.

Re: Counting if value is recurring within 7 days

ywon111 — Thu, 17 Jun 2021 04:56:56 GMT

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

Re: Counting if value is recurring within 7 days

Patrick — Thu, 17 Jun 2021 05:16:19 GMT

@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"

Re: Counting if value is recurring within 7 days

ywon111 — Thu, 08 Jul 2021 07:36:32 GMT

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