How to calculate time series cumulative count?

Reply
Occasional Contributor
Posts: 15

How to calculate time series cumulative count?

Hello everyone,

Given a data set:

Day     Flag

  1          0

  2          1

  3          1

  4          0

  5          1

  6          0

  7          0

...

For each day, I would like to count how many flag=1 in previous three days.

For example, on day 5, previous three days are day 2, 3, and 4. Therefore, the number of flag=1 is 2 (on day 2 and 3); on day 7, the number is 1 ( day 5 only).

In other words, the final data set should look like this:

Day     Flag   Number

  1          0          0

  2          1          0

  3          1          1

  4          0          2

  5          1          2

  6          0          2

  7          0          1

...

How can I create the final table? Thank you for your assistance.

Best regards,

Tammy

Super User
Posts: 5,434

Re: How to calculate time series cumulative count?

Posted in reply to TammyGoSAS

You could probably make use of the lag() function.

Data never sleeps
Valued Guide
Posts: 2,177

Re: How to calculate time series cumulative count?

Posted in reply to TammyGoSAS

I offer the basic

count = sum( lag(flag)=1

          , lag2(flag)=1

          , lag3(flag)=1 );

 

Or more sophisticated

Data newdata ;

Set olddata ;

array tmp(0:2) _temporary_;

Tmp(mod(_n_,3)) = flag ;

Number = sum( of tmp(*) );

run;

Occasional Contributor
Posts: 15

Re: How to calculate time series cumulative count?

Thank you so much!!!

Ask a Question
Discussion stats
  • 3 replies
  • 309 views
  • 0 likes
  • 3 in conversation