Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading