turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Running total, but in cycles with roll over

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-05-2014 01:18 PM

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:

Person | Event Number | Total I Want |
---|---|---|

A | 0 | 0 |

A | 75 | 75 |

A | 79 | 79 |

A | 150 | 150 |

A | 255 | 255 |

A | 4 | 259 |

A | 20 | 275 |

A | 80 | 335 |

A | 230 | 485 |

A | 255 | 510 |

A | 36 | 546 |

B | 0 | 0 |

B | 15 | 15 |

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to bailunrui

02-05-2014 02:26 PM

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;

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to bailunrui

02-05-2014 01:29 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

02-05-2014 02:05 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to bailunrui

02-05-2014 02:26 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

02-05-2014 02:53 PM

That totally worked, thank you!!