DATA Step, Macro, Functions and more

Running total, but in cycles with roll over

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

Running total, but in cycles with roll over

So, the data that I'm analyzing is set up in a strange way. For each person's device, there can only be 255 event numbers recorded before it rolls over, but this can happen several times. An event 0 is when the device is turned on. After a 255, there is usually a number greater than 0, indicating a roll over (unless by pure chance the device was turned off after exactly 255 cycles and turned back on again). Here is what my data looks like:

PersonEvent NumberTotal I Want
A00
A7575
A7979
A150150
A255255
A4259
A20275
A80335
A230485
A255510
A36546
B00
B1515

The algorithm to do this is just eluding me. I've tried retaining and lagging and all I'm getting is nowhere. I was able to code through the first roll over, but when it hit another one, my code failed. When I try to think recursively, my mind turns to mush.

Can anyone help me? Thanks in advance.

Lauren

Corrected addition mistake. Message was edited by: Lauren Parlett


Accepted Solutions
Solution
‎02-05-2014 02:26 PM
PROC Star
Posts: 7,363

Re: Running total, but in cycles with roll over

The following may do what you want:

data want (drop=base last);

  set have;

  by person;

  if first.person then base=0;

  last=lag(event_number);

  if not first.person and event_number lt last then base+255;

  total=sum(event_number,base);

run;

View solution in original post


All Replies
PROC Star
Posts: 7,363

Re: Running total, but in cycles with roll over

Can you provide a better explanation of what goes into the total?  It appears that it is simply the event number until event number is less than the previous event number and then, for some, appears like the total is the event number added onto a cummulative sum.  But that doesn't appear to hold for your example.  Please explain.

Contributor
Posts: 23

Re: Running total, but in cycles with roll over

Up until 255, it is just the event number.

After 255, it is the event number + 255.

If 255 comes up again, it is the event number + 255 + 255.

Solution
‎02-05-2014 02:26 PM
PROC Star
Posts: 7,363

Re: Running total, but in cycles with roll over

The following may do what you want:

data want (drop=base last);

  set have;

  by person;

  if first.person then base=0;

  last=lag(event_number);

  if not first.person and event_number lt last then base+255;

  total=sum(event_number,base);

run;

Contributor
Posts: 23

Re: Running total, but in cycles with roll over

That totally worked, thank you!!

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 218 views
  • 0 likes
  • 2 in conversation