Search code examples
group-bysasreverse-engineeringcumulative-sumdatastep

Reverse cumulative sum, by group in SAS


I have a variable that is reported daily and cumulatively in which I want raw counts for. I found code for doing this here, however the data I have are grouped. When I use "by" the group within the datastep, it still doesn't appear to recognize to start over with the new group, and therefor gives me a negative number for the first instance in the proceeding group.

This is the dummy code for what I am doing:

data have;
    input group $ date :yymmdd10. count_cumu;
    format date ddmmyy10.;
    datalines;
A 2022-03-01 2
A 2022-03-02 8
A 2022-03-03 16
A 2022-03-04 22
B 2022-03-1 3
B 2022-03-2 8
B 2022-03-3 20
B 2022-03-4 26
run;

proc sort data=have;
    by group date;
run;

data want;
    set have;
    count_raw = coalesce(dif(count_cumu), count_cumu);
    by group;
run;

Which outputs this:

output have

But I want the output to look like this (no negative for first instance in proceeding groups):

output want

Thank you for any advice or code you may be able to share!


Solution

  • data want;
       set have;
       by group;
       count_raw = coalesce(dif(count_cumu), count_cumu);
       if first.group then count_raw = count_cumu;
       run;