How do I compute a running count based on time interval (e.g., the last 12 hours)

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

How do I compute a running count based on time interval (e.g., the last 12 hours)

Hi!

 

Let's say I wanted to keep track of how many times participants have smoked the last 12 hours, and then use that variable as a predictor of, say, their current mood. That is, in this first step I want to produce a count variable in SAS that keeps a running count of smoking scores, but only include observations that occurred 12 hours or less preceding and including the current observation.

 

I made a small sample file that include the necessary variables and I manually computed a 12 hour running count variable just to demonstrate what I want SAS to do.

 

unix= time in unix window (12 hours=43200 unix units)

id= participants (I want to reset count for each participant)

obs = observations (in which participants were prompted about their smoking)

smoked = (whether they smoked=1, or not =0)

running12h_count = A manually computed sum of all smoked observations 12 hours back up until the current observation, [what I want SAS to compute for me]).

 

data smoking;

input unix id obs smoked running12h_count ;
datalines;
1255429500 1 1 1 1
1255437060 1 2 0 1
1255447800 1 3 1 2
1255457160 1 4 0 2
1255466700 1 5 0 2
1255510500 1 6 0 0
1255519500 1 7 0 0
1255528560 1 8 1 1
1255537800 1 9 1 2
1255547460 1 10 1 3
1255606680 2 1 0 0
1255608720 2 2 1 1
1255619400 2 3 1 2
1255629480 2 4 0 2
1255659260 2 5 0 0
;
run;

 

Any suggestions are deeply appreciated!

 

I am using SAS 9.4.


Accepted Solutions
Solution
‎11-11-2016 09:27 AM
Super User
Posts: 789

Re: How do I compute a running count based on time interval (e.g., the last 12 hours)

I believe the 15th value of running12h_count is erroneous in your example (should be 1, but you have 0)  It has a time value of 1255659260, which yields a cutoff of  1255616060.  The last three records make the cutoff.

 

Here's a data step solution which is likely to be faster that the SQL for large datasets.  As it builds a history for an id, it keeps a running total for the moving windows by adding the incoming record, and subtracting old records no longer in the window:

 

data want (drop=_:);
  set have;
  by id;
  retain rtotal 0;

  array _U{100} _temporary_; /*History of up to 100 time points for a single ID*/
  array _S{100} _temporary_; /*History of SMOKED dummies*/

  /* Add to history for this ID*/
  _U{obs}=unix;
  _S{obs}=smoked;

  _cut=unix-43200; /*!2 hour cutoff time value*/
  rtotal=ifn(obs=1,smoked,rtotal+smoked);

  retain _cobs;  /*Oldest OBS within moving window*/

  if obs=1 then _cobs=1;
  else if _U{_cobs}<_cut then do _cobs=_cobs by 1 while(_U{_cobs}<_cut);
    rtotal=rtotal-_S{_cobs};  /*subtract dummies no longer in the window*/
  end;
run;

View solution in original post


All Replies
Respected Advisor
Posts: 4,606

Re: How do I compute a running count based on time interval (e.g., the last 12 hours)

I suggest a self join:

 

proc sql;
create table smoking12 as
select 
    a.unix format=12.0,
    a.id,
    a.obs,
    a.smoked, 
    sum(b.smoked) as smoking12
from 
    smoking as a left join 
    smoking as b 
        on  a.id=b.id and 
            b.unix between a.unix - 43200 and a.unix
group by a.unix, a.id, a.obs, a.smoked;
select * from smoking12;
quit; 
PG
New Contributor
Posts: 4

Re: How do I compute a running count based on time interval (e.g., the last 12 hours)

Thanks PG for quick help! This worked perfectly fine!
Grand Advisor
Posts: 10,210

Re: How do I compute a running count based on time interval (e.g., the last 12 hours)

Some things to consider going forward. If you deal with times or dates you really want to consider using SAS date, datetime and time valued variables. There are a number of functions SAS provides for dealing with incrementing and returning intervals between values.

 

You 'unix' is actually number of seconds from something. If you know what that base (value=0) is we could have the actual date and time of the observation.

New Contributor
Posts: 4

Re: How do I compute a running count based on time interval (e.g., the last 12 hours)

The sample unix data was counted from 1970/1/1 If I recall correctly. I actually transformed the scores from date/time because I expected it would be easier to compute the running count from unix score. But thanks for the heads up!
Solution
‎11-11-2016 09:27 AM
Super User
Posts: 789

Re: How do I compute a running count based on time interval (e.g., the last 12 hours)

I believe the 15th value of running12h_count is erroneous in your example (should be 1, but you have 0)  It has a time value of 1255659260, which yields a cutoff of  1255616060.  The last three records make the cutoff.

 

Here's a data step solution which is likely to be faster that the SQL for large datasets.  As it builds a history for an id, it keeps a running total for the moving windows by adding the incoming record, and subtracting old records no longer in the window:

 

data want (drop=_:);
  set have;
  by id;
  retain rtotal 0;

  array _U{100} _temporary_; /*History of up to 100 time points for a single ID*/
  array _S{100} _temporary_; /*History of SMOKED dummies*/

  /* Add to history for this ID*/
  _U{obs}=unix;
  _S{obs}=smoked;

  _cut=unix-43200; /*!2 hour cutoff time value*/
  rtotal=ifn(obs=1,smoked,rtotal+smoked);

  retain _cobs;  /*Oldest OBS within moving window*/

  if obs=1 then _cobs=1;
  else if _U{_cobs}<_cut then do _cobs=_cobs by 1 while(_U{_cobs}<_cut);
    rtotal=rtotal-_S{_cobs};  /*subtract dummies no longer in the window*/
  end;
run;
New Contributor
Posts: 4

Re: How do I compute a running count based on time interval (e.g., the last 12 hours)

Great! Thanks mkeintz! this worked fine and will be very useful when I move to larger datasets. It gave the same results as the solution proposed by PG. Thanks for the correction on row 15 too!

Best!
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 225 views
  • 3 likes
  • 4 in conversation