Cumulative sum per group under multiple conditions

[sidello]

Hi, 

I have a data (force) set with the following variables:

• country
• name institution (within country) 
• group (within institution)
• count (number of responses)
• date (date on which responses came in)

I would now like to create two new variables:

• a first variable (cum_group1) where the cumulative sum for group 1 per country on a certain date is displayed.
• a second variable (cum_grou2) where the cumulative sum for group 2 per country on a certain date is displayed.

What I've done so far:

proc sort data = force;
by date country;
run;

data force;
set force;
by date country;
retain cum_group1;
if first.country then cum_group1=0;
cum_group1+count;
run;

So far unsuccessful. Could somebody guide me to a more appropriate way? (or should data structure be different).

Thank you!

[PeterClemmensen]

Can you post your sample data in a message, not in an Excel file?

[PeterClemmensen]

Anyways, I just included 2 groups here for demonstration:

Try this

data force;
input COUNTRY $ NAME $14. GROUP COUNT DATE :anydtdte.;
format DATE ddmmyy10.;
datalines;
AA Institution 1  1 415 01-01-2020
AA Institution 1  2 526 01-01-2020
AA Institution 2  1 300 01-01-2020
AA Institution 2  2 454 01-01-2020
AA Institution 3  1 230 01-01-2020
AA Institution 3  2 433 01-01-2020
AA Institution 4  1 545 01-01-2020
AA Institution 4  2 17  01-01-2020
AA Institution 5  1 140 01-01-2020
AA Institution 5  2 125 01-01-2020
BB Institution 6  1 482 01-01-2020
BB Institution 6  2 532 01-01-2020
BB Institution 7  1 393 01-01-2020
BB Institution 7  2 282 01-01-2020
BB Institution 8  1 127 01-01-2020
BB Institution 8  2 544 01-01-2020
BB Institution 9  1 560 01-01-2020
BB Institution 9  2 145 01-01-2020
BB Institution 10 1 316 01-01-2020
BB Institution 10 2 300 01-01-2020
BB Institution 11 1 208 01-01-2020
;

proc sort data = force;
    by date country;
run;

data force;
    set force;
    by date country;
    if first.country then do;
        cum_group1=0;
        cum_group2=0;
    end;
    if      group = 1 then cum_group1 + count;
    else if group = 2 then cum_group2 + count;
run;

[sidello]

Thank you for your input. 

Please find the full data sample below:

data WORK.SAMPLEDATA;
  infile datalines dsd truncover;
  input COUNTRY:$2. _NAME:$14. GROUP:BEST. COUNT:BEST. DATE:MMDDYY10.;
  format GROUP BEST. COUNT BEST. DATE MMDDYY10.;
  label COUNTRY="COUNTRY" _NAME=" NAME" GROUP="GROUP" COUNT="COUNT" DATE="DATE";
datalines;
AA Institution 1 1 415 01/01/2020
AA Institution 1 2 526 01/01/2020
AA Institution 2 1 300 01/01/2020
AA Institution 2 2 454 01/01/2020
AA Institution 3 1 230 01/01/2020
AA Institution 3 2 433 01/01/2020
AA Institution 4 1 545 01/01/2020
AA Institution 4 2 17 01/01/2020
AA Institution 5 1 140 01/01/2020
AA Institution 5 2 125 01/01/2020
BB Institution 6 1 482 01/01/2020
BB Institution 6 2 532 01/01/2020
BB Institution 7 1 393 01/01/2020
BB Institution 7 2 282 01/01/2020
BB Institution 8 1 127 01/01/2020
BB Institution 8 2 544 01/01/2020
BB Institution 9 1 560 01/01/2020
BB Institution 9 2 145 01/01/2020
BB Institution 10 1 316 01/01/2020
BB Institution 10 2 300 01/01/2020
BB Institution 11 1 208 01/01/2020
CC Institution 11 2 263 01/01/2020
CC Institution 12 1 234 01/01/2020
CC Institution 12 2 547 01/01/2020
CC Institution 13 1 240 01/01/2020
CC Institution 13 2 125 01/01/2020
CC Institution 14 1 53 01/01/2020
CC Institution 14 2 524 01/01/2020
CC Institution 15 1 132 01/01/2020
CC Institution 15 2 494 01/01/2020
CC Institution 16 1 59 01/01/2020
CC Institution 16 2 231 01/01/2020
CC Institution 17 1 161 01/01/2020
DD Institution 17 2 175 01/01/2020
DD Institution 18 1 33 01/01/2020
DD Institution 18 2 464 01/01/2020
DD Institution 19 1 108 01/01/2020
DD Institution 19 2 375 01/01/2020
DD Institution 20 1 355 01/01/2020
DD Institution 20 2 553 01/01/2020
DD Institution 21 1 80 01/01/2020
DD Institution 21 2 79 01/01/2020
DD Institution 22 1 443 01/01/2020
EE Institution 22 2 539 01/01/2020
EE Institution 23 1 363 01/01/2020
EE Institution 23 2 524 01/01/2020
EE Institution 24 1 422 01/01/2020
EE Institution 24 2 186 01/01/2020
FF Institution 25 1 553 01/01/2020
FF Institution 25 2 405 01/01/2020
AA Institution 1 1 313 01/02/2020
AA Institution 1 2 461 01/02/2020
AA Institution 2 1 640 01/02/2020
AA Institution 2 2 462 01/02/2020
AA Institution 3 1 886 01/02/2020
AA Institution 3 2 234 01/02/2020
AA Institution 4 1 663 01/02/2020
AA Institution 4 2 579 01/02/2020
AA Institution 5 1 174 01/02/2020
AA Institution 5 2 541 01/02/2020
BB Institution 6 1 532 01/02/2020
BB Institution 6 2 558 01/02/2020
BB Institution 7 1 611 01/02/2020
BB Institution 7 2 120 01/02/2020
BB Institution 8 1 172 01/02/2020
BB Institution 8 2 773 01/02/2020
BB Institution 9 1 84 01/02/2020
BB Institution 9 2 803 01/02/2020
BB Institution 10 1 858 01/02/2020
BB Institution 10 2 887 01/02/2020
BB Institution 11 1 736 01/02/2020
CC Institution 11 2 396 01/02/2020
CC Institution 12 1 176 01/02/2020
CC Institution 12 2 598 01/02/2020
CC Institution 13 1 852 01/02/2020
CC Institution 13 2 496 01/02/2020
CC Institution 14 1 189 01/02/2020
CC Institution 14 2 88 01/02/2020
CC Institution 15 1 892 01/02/2020
CC Institution 15 2 290 01/02/2020
CC Institution 16 1 736 01/02/2020
CC Institution 16 2 346 01/02/2020
CC Institution 17 1 422 01/02/2020
DD Institution 17 2 352 01/02/2020
DD Institution 18 1 585 01/02/2020
DD Institution 18 2 264 01/02/2020
DD Institution 19 1 646 01/02/2020
DD Institution 19 2 645 01/02/2020
DD Institution 20 1 297 01/02/2020
DD Institution 20 2 626 01/02/2020
DD Institution 21 1 619 01/02/2020
DD Institution 21 2 577 01/02/2020
DD Institution 22 1 232 01/02/2020
EE Institution 22 2 620 01/02/2020
EE Institution 23 1 509 01/02/2020
EE Institution 23 2 383 01/02/2020
EE Institution 24 1 67 01/02/2020
EE Institution 24 2 463 01/02/2020
FF Institution 25 1 820 01/02/2020
FF Institution 25 2 842 01/02/2020
AA Institution 1 1 478 01/03/2020
AA Institution 1 2 494 01/03/2020
AA Institution 2 1 683 01/03/2020
AA Institution 2 2 564 01/03/2020
AA Institution 3 1 360 01/03/2020
AA Institution 3 2 606 01/03/2020
AA Institution 4 1 337 01/03/2020
AA Institution 4 2 839 01/03/2020
AA Institution 5 1 703 01/03/2020
AA Institution 5 2 104 01/03/2020
BB Institution 6 1 853 01/03/2020
BB Institution 6 2 676 01/03/2020
BB Institution 7 1 272 01/03/2020
BB Institution 7 2 350 01/03/2020
BB Institution 8 1 778 01/03/2020
BB Institution 8 2 239 01/03/2020
BB Institution 9 1 692 01/03/2020
BB Institution 9 2 863 01/03/2020
BB Institution 10 1 354 01/03/2020
BB Institution 10 2 286 01/03/2020
BB Institution 11 1 131 01/03/2020
CC Institution 11 2 167 01/03/2020
CC Institution 12 1 270 01/03/2020
CC Institution 12 2 676 01/03/2020
CC Institution 13 1 440 01/03/2020
CC Institution 13 2 208 01/03/2020
CC Institution 14 1 358 01/03/2020
CC Institution 14 2 122 01/03/2020
CC Institution 15 1 509 01/03/2020
CC Institution 15 2 463 01/03/2020
CC Institution 16 1 55 01/03/2020
CC Institution 16 2 474 01/03/2020
CC Institution 17 1 806 01/03/2020
DD Institution 17 2 530 01/03/2020
DD Institution 18 1 810 01/03/2020
DD Institution 18 2 461 01/03/2020
DD Institution 19 1 510 01/03/2020
DD Institution 19 2 255 01/03/2020
DD Institution 20 1 395 01/03/2020
DD Institution 20 2 143 01/03/2020
DD Institution 21 1 896 01/03/2020
DD Institution 21 2 364 01/03/2020
DD Institution 22 1 630 01/03/2020
EE Institution 22 2 385 01/03/2020
EE Institution 23 1 471 01/03/2020
EE Institution 23 2 168 01/03/2020
EE Institution 24 1 522 01/03/2020
EE Institution 24 2 271 01/03/2020
FF Institution 25 1 174 01/03/2020
FF Institution 25 2 724 01/03/2020
;;;;

The cumulative number created per country is good, but I would like to have that number displayed  for all country values on that date. So on the date of the 01/01/2020 there is a different sum for group 1 AA (i.e. 415+300+230+545+140), group 1 BB, group 1 CC etc. (and the same for group 2). And on the second date, a new sum is made. 

 I provide the "as want" data below (input was made manually)

data WORK.SAMPLEDATA_WANT;
infile datalines dsd truncover;
input COUNTRY:$2. _NAME:$14. GROUP:BEST. COUNT:BEST. DATE:MMDDYY10. cum_group1:BEST. cum_group2:BEST.;
format GROUP BEST. COUNT BEST. DATE MMDDYY10. cum_group1 BEST. cum_group2 BEST.;
label COUNTRY="COUNTRY" _NAME=" NAME" GROUP="GROUP" COUNT="COUNT" DATE="DATE" cum_group1="cum_group1" cum_group2="cum_group2";
datalines;
AA Institution 1 1 415 01/01/2020 1630 1555
AA Institution 1 2 526 01/01/2020 1630 1555
AA Institution 2 1 300 01/01/2020 1630 1555
AA Institution 2 2 454 01/01/2020 1630 1555
AA Institution 3 1 230 01/01/2020 1630 1555
AA Institution 3 2 433 01/01/2020 1630 1555
AA Institution 4 1 545 01/01/2020 1630 1555
AA Institution 4 2 17 01/01/2020 1630 1555
AA Institution 5 1 140 01/01/2020 1630 1555
AA Institution 5 2 125 01/01/2020 1630 1555
BB Institution 6 1 482 01/01/2020 2086 1803
BB Institution 6 2 532 01/01/2020 2086 1803
BB Institution 7 1 393 01/01/2020 2086 1803
BB Institution 7 2 282 01/01/2020 2086 1803
BB Institution 8 1 127 01/01/2020 2086 1803
BB Institution 8 2 544 01/01/2020 2086 1803
BB Institution 9 1 560 01/01/2020 2086 1803
BB Institution 9 2 145 01/01/2020 2086 1803
BB Institution 10 1 316 01/01/2020 2086 1803
BB Institution 10 2 300 01/01/2020 2086 1803
BB Institution 11 1 208 01/01/2020 2086 1803
CC Institution 11 2 263 01/01/2020 . .
CC Institution 12 1 234 01/01/2020 . .
CC Institution 12 2 547 01/01/2020 . .
CC Institution 13 1 240 01/01/2020 . .
CC Institution 13 2 125 01/01/2020 . .
CC Institution 14 1 53 01/01/2020 . .
CC Institution 14 2 524 01/01/2020 . .
CC Institution 15 1 132 01/01/2020 . .
CC Institution 15 2 494 01/01/2020 . .
CC Institution 16 1 59 01/01/2020 . .
CC Institution 16 2 231 01/01/2020 . .
CC Institution 17 1 161 01/01/2020 . .
DD Institution 17 2 175 01/01/2020 . .
DD Institution 18 1 33 01/01/2020 . .
DD Institution 18 2 464 01/01/2020 . .
DD Institution 19 1 108 01/01/2020 . .
DD Institution 19 2 375 01/01/2020 . .
DD Institution 20 1 355 01/01/2020 . .
DD Institution 20 2 553 01/01/2020 . .
DD Institution 21 1 80 01/01/2020 . .
DD Institution 21 2 79 01/01/2020 . .
DD Institution 22 1 443 01/01/2020 . .
EE Institution 22 2 539 01/01/2020 . .
EE Institution 23 1 363 01/01/2020 . .
EE Institution 23 2 524 01/01/2020 . .
EE Institution 24 1 422 01/01/2020 . .
EE Institution 24 2 186 01/01/2020 . .
FF Institution 25 1 553 01/01/2020 . .
FF Institution 25 2 405 01/01/2020 . .
AA Institution 1 1 313 01/02/2020 2676 2277
AA Institution 1 2 461 01/02/2020 2676 2277
AA Institution 2 1 640 01/02/2020 2676 2277
AA Institution 2 2 462 01/02/2020 2676 2277
AA Institution 3 1 886 01/02/2020 2676 2277
AA Institution 3 2 234 01/02/2020 2676 2277
AA Institution 4 1 663 01/02/2020 2676 2277
AA Institution 4 2 579 01/02/2020 2676 2277
AA Institution 5 1 174 01/02/2020 2676 2277
AA Institution 5 2 541 01/02/2020 2676 2277
BB Institution 6 1 532 01/02/2020 2993 3141
BB Institution 6 2 558 01/02/2020 2993 3141
BB Institution 7 1 611 01/02/2020 2993 3141
BB Institution 7 2 120 01/02/2020 2993 3141
BB Institution 8 1 172 01/02/2020 2993 3141
BB Institution 8 2 773 01/02/2020 2993 3141
BB Institution 9 1 84 01/02/2020 2993 3141
BB Institution 9 2 803 01/02/2020 2993 3141
BB Institution 10 1 858 01/02/2020 2993 3141
BB Institution 10 2 887 01/02/2020 2993 3141
BB Institution 11 1 736 01/02/2020 2993 3141
CC Institution 11 2 396 01/02/2020 . .
CC Institution 12 1 176 01/02/2020 . .
CC Institution 12 2 598 01/02/2020 . .
CC Institution 13 1 852 01/02/2020 . .
CC Institution 13 2 496 01/02/2020 . .
CC Institution 14 1 189 01/02/2020 . .
CC Institution 14 2 88 01/02/2020 . .
CC Institution 15 1 892 01/02/2020 . .
CC Institution 15 2 290 01/02/2020 . .
CC Institution 16 1 736 01/02/2020 . .
CC Institution 16 2 346 01/02/2020 . .
CC Institution 17 1 422 01/02/2020 . .
DD Institution 17 2 352 01/02/2020 . .
DD Institution 18 1 585 01/02/2020 . .
DD Institution 18 2 264 01/02/2020 . .
DD Institution 19 1 646 01/02/2020 . .
DD Institution 19 2 645 01/02/2020 . .
DD Institution 20 1 297 01/02/2020 . .
DD Institution 20 2 626 01/02/2020 . .
DD Institution 21 1 619 01/02/2020 . .
DD Institution 21 2 577 01/02/2020 . .
DD Institution 22 1 232 01/02/2020 . .
EE Institution 22 2 620 01/02/2020 . .
EE Institution 23 1 509 01/02/2020 . .
EE Institution 23 2 383 01/02/2020 . .
EE Institution 24 1 67 01/02/2020 . .
EE Institution 24 2 463 01/02/2020 . .
FF Institution 25 1 820 01/02/2020 . .
FF Institution 25 2 842 01/02/2020 . .
AA Institution 1 1 478 01/03/2020 . .
AA Institution 1 2 494 01/03/2020 . .
AA Institution 2 1 683 01/03/2020 . .
AA Institution 2 2 564 01/03/2020 . .
AA Institution 3 1 360 01/03/2020 . .
AA Institution 3 2 606 01/03/2020 . .
AA Institution 4 1 337 01/03/2020 . .
AA Institution 4 2 839 01/03/2020 . .
AA Institution 5 1 703 01/03/2020 . .
AA Institution 5 2 104 01/03/2020 . .
BB Institution 6 1 853 01/03/2020 . .
BB Institution 6 2 676 01/03/2020 . .
BB Institution 7 1 272 01/03/2020 . .
BB Institution 7 2 350 01/03/2020 . .
BB Institution 8 1 778 01/03/2020 . .
BB Institution 8 2 239 01/03/2020 . .
BB Institution 9 1 692 01/03/2020 . .
BB Institution 9 2 863 01/03/2020 . .
BB Institution 10 1 354 01/03/2020 . .
BB Institution 10 2 286 01/03/2020 . .
BB Institution 11 1 131 01/03/2020 . .
CC Institution 11 2 167 01/03/2020 . .
CC Institution 12 1 270 01/03/2020 . .
CC Institution 12 2 676 01/03/2020 . .
CC Institution 13 1 440 01/03/2020 . .
CC Institution 13 2 208 01/03/2020 . .
CC Institution 14 1 358 01/03/2020 . .
CC Institution 14 2 122 01/03/2020 . .
CC Institution 15 1 509 01/03/2020 . .
CC Institution 15 2 463 01/03/2020 . .
CC Institution 16 1 55 01/03/2020 . .
CC Institution 16 2 474 01/03/2020 . .
CC Institution 17 1 806 01/03/2020 . .
DD Institution 17 2 530 01/03/2020 . .
DD Institution 18 1 810 01/03/2020 . .
DD Institution 18 2 461 01/03/2020 . .
DD Institution 19 1 510 01/03/2020 . .
DD Institution 19 2 255 01/03/2020 . .
DD Institution 20 1 395 01/03/2020 . .
DD Institution 20 2 143 01/03/2020 . .
DD Institution 21 1 896 01/03/2020 . .
DD Institution 21 2 364 01/03/2020 . .
DD Institution 22 1 630 01/03/2020 . .
EE Institution 22 2 385 01/03/2020 . .
EE Institution 23 1 471 01/03/2020 . .
EE Institution 23 2 168 01/03/2020 . .
EE Institution 24 1 522 01/03/2020 . .
EE Institution 24 2 271 01/03/2020 . .
FF Institution 25 1 174 01/03/2020 . .
FF Institution 25 2 724 01/03/2020 . .
;;;;

[Kurt_Bremser]

When I run your DATA step, only COUNTRY is populated.

[PeterClemmensen]

Ok. Again, I focus on a smaller part of your data, but this should do

data force;
input COUNTRY $ NAME $14. GROUP COUNT DATE :anydtdte.;
format DATE ddmmyy10.;
datalines;
AA Institution 1  1 415 01-01-2020
AA Institution 1  2 526 01-01-2020
AA Institution 2  1 300 01-01-2020
AA Institution 2  2 454 01-01-2020
AA Institution 3  1 230 01-01-2020
AA Institution 3  2 433 01-01-2020
AA Institution 4  1 545 01-01-2020
AA Institution 4  2 17  01-01-2020
AA Institution 5  1 140 01-01-2020
AA Institution 5  2 125 01-01-2020
BB Institution 6  1 482 01-01-2020
BB Institution 6  2 532 01-01-2020
BB Institution 7  1 393 01-01-2020
BB Institution 7  2 282 01-01-2020
BB Institution 8  1 127 01-01-2020
BB Institution 8  2 544 01-01-2020
BB Institution 9  1 560 01-01-2020
BB Institution 9  2 145 01-01-2020
BB Institution 10 1 316 01-01-2020
BB Institution 10 2 300 01-01-2020
BB Institution 11 1 208 01-01-2020
;

proc sort data = force;
    by date country;
run;

data want;

   do until (last.country);
      set force;
      by date country;
      if      group = 1 then cum_group1 + count;
      else if group = 2 then cum_group2 + count;
   end;

   do until (last.country);
      set force;
      by date country;
      output;
   end;

   cum_group1=0;
   cum_group2=0;
run;

Result:

COUNTRY NAME           GROUP COUNT DATE       cum_group1 cum_group2 
AA      Institution 1  1     415   01/01/2020 1630       1555 
AA      Institution 1  2     526   01/01/2020 1630       1555 
AA      Institution 2  1     300   01/01/2020 1630       1555 
AA      Institution 2  2     454   01/01/2020 1630       1555 
AA      Institution 3  1     230   01/01/2020 1630       1555 
AA      Institution 3  2     433   01/01/2020 1630       1555 
AA      Institution 4  1     545   01/01/2020 1630       1555 
AA      Institution 4  2     17    01/01/2020 1630       1555 
AA      Institution 5  1     140   01/01/2020 1630       1555 
AA      Institution 5  2     125   01/01/2020 1630       1555 
BB      Institution 6  1     482   01/01/2020 2086       1803 
BB      Institution 6  2     532   01/01/2020 2086       1803 
BB      Institution 7  1     393   01/01/2020 2086       1803 
BB      Institution 7  2     282   01/01/2020 2086       1803 
BB      Institution 8  1     127   01/01/2020 2086       1803 
BB      Institution 8  2     544   01/01/2020 2086       1803 
BB      Institution 9  1     560   01/01/2020 2086       1803 
BB      Institution 9  2     145   01/01/2020 2086       1803 
BB      Institution 10 1     316   01/01/2020 2086       1803 
BB      Institution 10 2     300   01/01/2020 2086       1803 
BB      Institution 11 1     208   01/01/2020 2086       1803 

[sidello]

Hi @PeterClemmensen ,

Thank you for providing me with your guidance and support. 

The solution listed below almost works perfectly, the only thing that I observe is, if I apply it to my real data: the sum made is correct for the very first date observed and for the last date observed, but for the dates in between, a different sum is made; and I can't reason why or where it would come from. 

I hope this explanation makes sense, I will try to replicate it in the test data. 

[PeterClemmensen]

How is your actual data sorted? You write "by date country", but it seems you want the data to be sorted by Country first, then date? Is that correct?

[sidello]

Hi @PeterClemmensen ,

For me it doesn't really matter if it's by date or by country; both are fine (and ultimately yield the same result). 

My aim is to use the newly created variables in a line chart eventually. 

If I switch both (i.e. first sort by country and then by date) I still observe the same phenomenon; i.e. that the sum made for the first date and the last date are both correct, but the sums for the dates in between are not. 

[sidello]

Hi @PeterClemmensen ,

At first, the sums made for the other dates seemed random, but I was just too quick and didn't notice it immediately: apparently is exactly multiplied by 2 for the dates that are not the first or the last date. 

[mkeintz]

Why not post some actual data subject to the problem, then post the result you get from that data, and then post the result you want.  It might be clearer than "apparently is exactly multiplied by 2 for the dates that are not the first or the last date."

[sidello]

Hi @mkeintz 

This is some of the actual data, but I've simplified it. 

data WORK.DATA1;
  infile datalines dsd truncover;
  input COUNTRY:$2. HOSPITAL_NAME:$14. GROUP:BEST. COUNT:BEST. DATE:MMDDYY10.;
  format GROUP BEST. COUNT BEST. DATE MMDDYY10.;
  label COUNTRY="COUNTRY" HOSPITAL_NAME="HOSPITAL NAME" GROUP="GROUP" COUNT="COUNT" DATE="DATE";
datalines;
AA Institution 1 1 152 11/01/2020
AA Institution 1 2 66 11/01/2020
AA Institution 2 1 . 11/01/2020
AA Institution 2 2 . 11/01/2020
AA Institution 3 1 196 11/01/2020
AA Institution 3 2 64 11/01/2020
AA Institution 4 1 150 11/01/2020
AA Institution 4 2 70 11/01/2020
AA Institution 5 1 . 11/01/2020
AA Institution 5 2 . 11/01/2020
AA Institution 6 1 360 11/01/2020
AA Institution 6 2 127 11/01/2020
AA Institution 7 1 103 11/01/2020
AA Institution 7 2 47 11/01/2020
AA Institution 8 1 150 11/01/2020
AA Institution 8 2 30 11/01/2020
AA Institution 9 1 342 11/01/2020
AA Institution 9 2 202 11/01/2020
AA Institution 10 1 226 11/01/2020
AA Institution 10 2 92 11/01/2020
BB Institution 11 1 317 11/01/2020
BB Institution 11 2 274 11/01/2020
BB Institution 12 1 222 11/01/2020
BB Institution 12 2 109 11/01/2020
BB Institution 13 1 615 11/01/2020
BB Institution 13 2 360 11/01/2020
BB Institution 14 1 508 11/01/2020
BB Institution 14 2 400 11/01/2020
BB Institution 15 1 . 11/01/2020
BB Institution 15 2 . 11/01/2020
BB Institution 16 1 382 11/01/2020
BB Institution 16 2 173 11/01/2020
BB Institution 17 1 . 11/01/2020
BB Institution 17 2 . 11/01/2020
BB Institution 18 1 200 11/01/2020
BB Institution 18 2 200 11/01/2020
AA Institution 1 1 152 11/03/2020
AA Institution 1 2 66 11/03/2020
AA Institution 2 1 . 11/03/2020
AA Institution 2 2 . 11/03/2020
AA Institution 3 1 196 11/03/2020
AA Institution 3 2 64 11/03/2020
AA Institution 4 1 150 11/03/2020
AA Institution 4 2 70 11/03/2020
AA Institution 5 1 . 11/03/2020
AA Institution 5 2 . 11/03/2020
AA Institution 6 1 360 11/03/2020
AA Institution 6 2 127 11/03/2020
AA Institution 7 1 103 11/03/2020
AA Institution 7 2 47 11/03/2020
AA Institution 8 1 150 11/03/2020
AA Institution 8 2 30 11/03/2020
AA Institution 9 1 342 11/03/2020
AA Institution 9 2 202 11/03/2020
AA Institution 10 1 226 11/03/2020
AA Institution 10 2 92 11/03/2020
BB Institution 11 1 317 11/03/2020
BB Institution 11 2 274 11/03/2020
BB Institution 12 1 222 11/03/2020
BB Institution 12 2 109 11/03/2020
BB Institution 13 1 615 11/03/2020
BB Institution 13 2 360 11/03/2020
BB Institution 14 1 508 11/03/2020
BB Institution 14 2 400 11/03/2020
BB Institution 15 1 . 11/03/2020
BB Institution 15 2 . 11/03/2020
BB Institution 16 1 382 11/03/2020
BB Institution 16 2 173 11/03/2020
BB Institution 17 1 . 11/03/2020
BB Institution 17 2 . 11/03/2020
BB Institution 18 1 200 11/03/2020
BB Institution 18 2 200 11/03/2020
AA Institution 1 1 152 11/05/2020
AA Institution 1 2 66 11/05/2020
AA Institution 2 1 . 11/05/2020
AA Institution 2 2 . 11/05/2020
AA Institution 3 1 196 11/05/2020
AA Institution 3 2 64 11/05/2020
AA Institution 4 1 150 11/05/2020
AA Institution 4 2 70 11/05/2020
AA Institution 5 1 . 11/05/2020
AA Institution 5 2 . 11/05/2020
AA Institution 6 1 360 11/05/2020
AA Institution 6 2 127 11/05/2020
AA Institution 7 1 103 11/05/2020
AA Institution 7 2 47 11/05/2020
AA Institution 8 1 150 11/05/2020
AA Institution 8 2 30 11/05/2020
AA Institution 9 1 342 11/05/2020
AA Institution 9 2 202 11/05/2020
AA Institution 10 1 226 11/05/2020
AA Institution 10 2 92 11/05/2020
BB Institution 11 1 317 11/05/2020
BB Institution 11 2 274 11/05/2020
BB Institution 12 1 222 11/05/2020
BB Institution 12 2 109 11/05/2020
BB Institution 13 1 615 11/05/2020
BB Institution 13 2 360 11/05/2020
BB Institution 14 1 508 11/05/2020
BB Institution 14 2 400 11/05/2020
BB Institution 15 1 . 11/05/2020
BB Institution 15 2 . 11/05/2020
BB Institution 16 1 382 11/05/2020
BB Institution 16 2 173 11/05/2020
BB Institution 17 1 . 11/05/2020
BB Institution 17 2 . 11/05/2020
BB Institution 18 1 200 11/05/2020
BB Institution 18 2 200 11/05/2020
AA Institution 1 1 152 11/09/2020
AA Institution 1 2 66 11/09/2020
AA Institution 2 1 . 11/09/2020
AA Institution 2 2 . 11/09/2020
AA Institution 3 1 196 11/09/2020
AA Institution 3 2 64 11/09/2020
AA Institution 4 1 150 11/09/2020
AA Institution 4 2 70 11/09/2020
AA Institution 5 1 . 11/09/2020
AA Institution 5 2 . 11/09/2020
AA Institution 6 1 360 11/09/2020
AA Institution 6 2 127 11/09/2020
AA Institution 7 1 103 11/09/2020
AA Institution 7 2 47 11/09/2020
AA Institution 8 1 150 11/09/2020
AA Institution 8 2 30 11/09/2020
AA Institution 9 1 342 11/09/2020
AA Institution 9 2 202 11/09/2020
AA Institution 10 1 226 11/09/2020
AA Institution 10 2 92 11/09/2020
BB Institution 11 1 317 11/09/2020
BB Institution 11 2 274 11/09/2020
BB Institution 12 1 222 11/09/2020
BB Institution 12 2 109 11/09/2020
BB Institution 13 1 615 11/09/2020
BB Institution 13 2 360 11/09/2020
BB Institution 14 1 508 11/09/2020
BB Institution 14 2 400 11/09/2020
BB Institution 15 1 . 11/09/2020
BB Institution 15 2 . 11/09/2020
BB Institution 16 1 382 11/09/2020
BB Institution 16 2 173 11/09/2020
BB Institution 17 1 . 11/09/2020
BB Institution 17 2 . 11/09/2020
BB Institution 18 1 200 11/09/2020
BB Institution 18 2 200 11/09/2020
AA Institution 1 1 152 11/13/2020
AA Institution 1 2 66 11/13/2020
AA Institution 2 1 . 11/13/2020
AA Institution 2 2 . 11/13/2020
AA Institution 3 1 196 11/13/2020
AA Institution 3 2 64 11/13/2020
AA Institution 4 1 150 11/13/2020
AA Institution 4 2 70 11/13/2020
AA Institution 5 1 . 11/13/2020
AA Institution 5 2 . 11/13/2020
AA Institution 6 1 360 11/13/2020
AA Institution 6 2 127 11/13/2020
AA Institution 7 1 103 11/13/2020
AA Institution 7 2 47 11/13/2020
AA Institution 8 1 150 11/13/2020
AA Institution 8 2 30 11/13/2020
AA Institution 9 1 342 11/13/2020
AA Institution 9 2 202 11/13/2020
AA Institution 10 1 226 11/13/2020
AA Institution 10 2 92 11/13/2020
BB Institution 11 1 317 11/13/2020
BB Institution 11 2 274 11/13/2020
BB Institution 12 1 222 11/13/2020
BB Institution 12 2 109 11/13/2020
BB Institution 13 1 615 11/13/2020
BB Institution 13 2 360 11/13/2020
BB Institution 14 1 508 11/13/2020
BB Institution 14 2 400 11/13/2020
BB Institution 15 1 . 11/13/2020
BB Institution 15 2 . 11/13/2020
BB Institution 16 1 382 11/13/2020
BB Institution 16 2 173 11/13/2020
BB Institution 17 1 . 11/13/2020
BB Institution 17 2 . 11/13/2020
BB Institution 18 1 200 11/13/2020
BB Institution 18 2 200 11/13/2020
AA Institution 1 1 152 11/15/2020
AA Institution 1 2 66 11/15/2020
AA Institution 2 1 . 11/15/2020
AA Institution 2 2 . 11/15/2020
AA Institution 3 1 196 11/15/2020
AA Institution 3 2 64 11/15/2020
AA Institution 4 1 150 11/15/2020
AA Institution 4 2 70 11/15/2020
AA Institution 5 1 . 11/15/2020
AA Institution 5 2 . 11/15/2020
AA Institution 6 1 360 11/15/2020
AA Institution 6 2 127 11/15/2020
AA Institution 7 1 103 11/15/2020
AA Institution 7 2 47 11/15/2020
AA Institution 8 1 150 11/15/2020
AA Institution 8 2 30 11/15/2020
AA Institution 9 1 342 11/15/2020
AA Institution 9 2 202 11/15/2020
AA Institution 10 1 226 11/15/2020
AA Institution 10 2 92 11/15/2020
;;;;

This is the code that I used, with help from @PeterClemmensen

data data2;
	do until (last.COUNTRY);
		set data1;
		by COUNTRY DATE;
		if 			GROUP = 1 	then CUM_COUNT_G1 + COUNT;
		else if 	GROUP = 2 	then CUM_COUNT_G2 + COUNT;
	end;
	do until (last.COUNTRY);
		set data1;
		by COUNTRY DATE;
		output;
	end;
	CUM_COUNT_G1 = 0;
	CUM_COUNT_G2 = 0;
run;

This is the result: 

data WORK.DATA2;
  infile datalines dsd truncover;
  input COUNTRY:$2. HOSPITAL_NAME:$14. GROUP:BEST. COUNT:BEST. DATE:MMDDYY10. F:$1. G:$1. H:$1. I:$1. CUM_COUNT_G1:32. CUM_COUNT_G2:32.;
  format GROUP BEST. COUNT BEST. DATE MMDDYY10.;
  label COUNTRY="COUNTRY" HOSPITAL_NAME="HOSPITAL NAME" GROUP="GROUP" COUNT="COUNT" DATE="DATE" F="F" G="G" H="H" I="I";
datalines;
AA Institution 1 1 152 11/01/2020         11753 4886
AA Institution 1 2 66 11/01/2020         11753 4886
AA Institution 2 1 . 11/01/2020         11753 4886
AA Institution 2 2 . 11/01/2020         11753 4886
AA Institution 3 1 196 11/01/2020         11753 4886
AA Institution 3 2 64 11/01/2020         11753 4886
AA Institution 4 1 150 11/01/2020         11753 4886
AA Institution 4 2 70 11/01/2020         11753 4886
AA Institution 5 1 . 11/01/2020         11753 4886
AA Institution 5 2 . 11/01/2020         11753 4886
AA Institution 6 1 360 11/01/2020         11753 4886
AA Institution 6 2 127 11/01/2020         11753 4886
AA Institution 7 1 103 11/01/2020         11753 4886
AA Institution 7 2 47 11/01/2020         11753 4886
AA Institution 8 1 150 11/01/2020         11753 4886
AA Institution 8 2 30 11/01/2020         11753 4886
AA Institution 9 1 342 11/01/2020         11753 4886
AA Institution 9 2 202 11/01/2020         11753 4886
AA Institution 10 1 226 11/01/2020         11753 4886
AA Institution 10 2 92 11/01/2020         11753 4886
AA Institution 1 1 152 11/03/2020         11753 4886
AA Institution 1 2 66 11/03/2020         11753 4886
AA Institution 2 1 . 11/03/2020         11753 4886
AA Institution 2 2 . 11/03/2020         11753 4886
AA Institution 3 1 196 11/03/2020         11753 4886
AA Institution 3 2 64 11/03/2020         11753 4886
AA Institution 4 1 150 11/03/2020         11753 4886
AA Institution 4 2 70 11/03/2020         11753 4886
AA Institution 5 1 . 11/03/2020         11753 4886
AA Institution 5 2 . 11/03/2020         11753 4886
AA Institution 6 1 360 11/03/2020         11753 4886
AA Institution 6 2 127 11/03/2020         11753 4886
AA Institution 7 1 103 11/03/2020         11753 4886
AA Institution 7 2 47 11/03/2020         11753 4886
AA Institution 8 1 150 11/03/2020         11753 4886
AA Institution 8 2 30 11/03/2020         11753 4886
AA Institution 9 1 342 11/03/2020         11753 4886
AA Institution 9 2 202 11/03/2020         11753 4886
AA Institution 10 1 226 11/03/2020         11753 4886
AA Institution 10 2 92 11/03/2020         11753 4886
AA Institution 1 1 152 11/05/2020         11753 4886
AA Institution 1 2 66 11/05/2020         11753 4886
AA Institution 2 1 . 11/05/2020         11753 4886
AA Institution 2 2 . 11/05/2020         11753 4886
AA Institution 3 1 196 11/05/2020         11753 4886
AA Institution 3 2 64 11/05/2020         11753 4886
AA Institution 4 1 150 11/05/2020         11753 4886
AA Institution 4 2 70 11/05/2020         11753 4886
AA Institution 5 1 . 11/05/2020         11753 4886
AA Institution 5 2 . 11/05/2020         11753 4886
AA Institution 6 1 360 11/05/2020         11753 4886
AA Institution 6 2 127 11/05/2020         11753 4886
AA Institution 7 1 103 11/05/2020         11753 4886
AA Institution 7 2 47 11/05/2020         11753 4886
AA Institution 8 1 150 11/05/2020         11753 4886
AA Institution 8 2 30 11/05/2020         11753 4886
AA Institution 9 1 342 11/05/2020         11753 4886
AA Institution 9 2 202 11/05/2020         11753 4886
AA Institution 10 1 226 11/05/2020         11753 4886
AA Institution 10 2 92 11/05/2020         11753 4886
AA Institution 1 1 152 11/09/2020         11753 4886
AA Institution 1 2 66 11/09/2020         11753 4886
AA Institution 2 1 . 11/09/2020         11753 4886
AA Institution 2 2 . 11/09/2020         11753 4886
AA Institution 3 1 196 11/09/2020         11753 4886
AA Institution 3 2 64 11/09/2020         11753 4886
AA Institution 4 1 150 11/09/2020         11753 4886
AA Institution 4 2 70 11/09/2020         11753 4886
AA Institution 5 1 . 11/09/2020         11753 4886
AA Institution 5 2 . 11/09/2020         11753 4886
AA Institution 6 1 360 11/09/2020         11753 4886
AA Institution 6 2 127 11/09/2020         11753 4886
AA Institution 7 1 103 11/09/2020         11753 4886
AA Institution 7 2 47 11/09/2020         11753 4886
AA Institution 8 1 150 11/09/2020         11753 4886
AA Institution 8 2 30 11/09/2020         11753 4886
AA Institution 9 1 342 11/09/2020         11753 4886
AA Institution 9 2 202 11/09/2020         11753 4886
AA Institution 10 1 226 11/09/2020         11753 4886
AA Institution 10 2 92 11/09/2020         11753 4886
AA Institution 1 1 152 11/13/2020         11753 4886
AA Institution 1 2 66 11/13/2020         11753 4886
AA Institution 2 1 . 11/13/2020         11753 4886
AA Institution 2 2 . 11/13/2020         11753 4886
AA Institution 3 1 196 11/13/2020         11753 4886
AA Institution 3 2 64 11/13/2020         11753 4886
AA Institution 4 1 150 11/13/2020         11753 4886
AA Institution 4 2 70 11/13/2020         11753 4886
AA Institution 5 1 . 11/13/2020         11753 4886
AA Institution 5 2 . 11/13/2020         11753 4886
AA Institution 6 1 360 11/13/2020         11753 4886
AA Institution 6 2 127 11/13/2020         11753 4886
AA Institution 7 1 103 11/13/2020         11753 4886
AA Institution 7 2 47 11/13/2020         11753 4886
AA Institution 8 1 150 11/13/2020         11753 4886
AA Institution 8 2 30 11/13/2020         11753 4886
AA Institution 9 1 342 11/13/2020         11753 4886
AA Institution 9 2 202 11/13/2020         11753 4886
AA Institution 10 1 226 11/13/2020         11753 4886
AA Institution 10 2 92 11/13/2020         11753 4886
AA Institution 1 1 152 11/15/2020         11753 4886
AA Institution 1 2 66 11/15/2020         11753 4886
AA Institution 2 1 . 11/15/2020         11753 4886
AA Institution 2 2 . 11/15/2020         11753 4886
AA Institution 3 1 196 11/15/2020         11753 4886
AA Institution 3 2 64 11/15/2020         11753 4886
AA Institution 4 1 150 11/15/2020         11753 4886
AA Institution 4 2 70 11/15/2020         11753 4886
AA Institution 5 1 . 11/15/2020         11753 4886
AA Institution 5 2 . 11/15/2020         11753 4886
AA Institution 6 1 360 11/15/2020         11753 4886
AA Institution 6 2 127 11/15/2020         11753 4886
AA Institution 7 1 103 11/15/2020         11753 4886
AA Institution 7 2 47 11/15/2020         11753 4886
AA Institution 8 1 150 11/15/2020         11753 4886
AA Institution 8 2 30 11/15/2020         11753 4886
AA Institution 9 1 342 11/15/2020         11753 4886
AA Institution 9 2 202 11/15/2020         11753 4886
AA Institution 10 1 226 11/15/2020         11753 4886
AA Institution 10 2 92 11/15/2020         11753 4886
AA Institution 1 1 152 11/17/2020         11753 4886
AA Institution 1 2 66 11/17/2020         11753 4886
AA Institution 2 1 . 11/17/2020         11753 4886
AA Institution 2 2 . 11/17/2020         11753 4886
AA Institution 3 1 196 11/17/2020         11753 4886
AA Institution 3 2 64 11/17/2020         11753 4886
AA Institution 4 1 150 11/17/2020         11753 4886
AA Institution 4 2 70 11/17/2020         11753 4886
AA Institution 5 1 . 11/17/2020         11753 4886
AA Institution 5 2 . 11/17/2020         11753 4886
AA Institution 6 1 360 11/17/2020         11753 4886
AA Institution 6 2 127 11/17/2020         11753 4886
AA Institution 7 1 103 11/17/2020         11753 4886
AA Institution 7 2 47 11/17/2020         11753 4886
AA Institution 8 1 150 11/17/2020         11753 4886
AA Institution 8 2 30 11/17/2020         11753 4886
AA Institution 9 1 342 11/17/2020         11753 4886
AA Institution 9 2 202 11/17/2020         11753 4886
AA Institution 10 1 226 11/17/2020         11753 4886
AA Institution 10 2 92 11/17/2020         11753 4886
BB Institution 11 1 317 11/01/2020         15708 10612
BB Institution 11 2 274 11/01/2020         15708 10612
BB Institution 12 1 222 11/01/2020         15708 10612
BB Institution 12 2 109 11/01/2020         15708 10612
BB Institution 13 1 615 11/01/2020         15708 10612
BB Institution 13 2 360 11/01/2020         15708 10612
BB Institution 14 1 508 11/01/2020         15708 10612
BB Institution 14 2 400 11/01/2020         15708 10612
BB Institution 15 1 . 11/01/2020         15708 10612
BB Institution 15 2 . 11/01/2020         15708 10612
BB Institution 16 1 382 11/01/2020         15708 10612
BB Institution 16 2 173 11/01/2020         15708 10612
BB Institution 17 1 . 11/01/2020         15708 10612
BB Institution 17 2 . 11/01/2020         15708 10612
BB Institution 18 1 200 11/01/2020         15708 10612
BB Institution 18 2 200 11/01/2020         15708 10612
BB Institution 11 1 317 11/03/2020         15708 10612
BB Institution 11 2 274 11/03/2020         15708 10612
BB Institution 12 1 222 11/03/2020         15708 10612
BB Institution 12 2 109 11/03/2020         15708 10612
BB Institution 13 1 615 11/03/2020         15708 10612
BB Institution 13 2 360 11/03/2020         15708 10612
BB Institution 14 1 508 11/03/2020         15708 10612
BB Institution 14 2 400 11/03/2020         15708 10612
BB Institution 15 1 . 11/03/2020         15708 10612
BB Institution 15 2 . 11/03/2020         15708 10612
BB Institution 16 1 382 11/03/2020         15708 10612
BB Institution 16 2 173 11/03/2020         15708 10612
BB Institution 17 1 . 11/03/2020         15708 10612
BB Institution 17 2 . 11/03/2020         15708 10612
BB Institution 18 1 200 11/03/2020         15708 10612
BB Institution 18 2 200 11/03/2020         15708 10612
BB Institution 11 1 317 11/05/2020         15708 10612
BB Institution 11 2 274 11/05/2020         15708 10612
BB Institution 12 1 222 11/05/2020         15708 10612
BB Institution 12 2 109 11/05/2020         15708 10612
BB Institution 13 1 615 11/05/2020         15708 10612
BB Institution 13 2 360 11/05/2020         15708 10612
BB Institution 14 1 508 11/05/2020         15708 10612
BB Institution 14 2 400 11/05/2020         15708 10612
BB Institution 15 1 . 11/05/2020         15708 10612
BB Institution 15 2 . 11/05/2020         15708 10612
BB Institution 16 1 382 11/05/2020         15708 10612
BB Institution 16 2 173 11/05/2020         15708 10612
BB Institution 17 1 . 11/05/2020         15708 10612
BB Institution 17 2 . 11/05/2020         15708 10612
BB Institution 18 1 200 11/05/2020         15708 10612
BB Institution 18 2 200 11/05/2020         15708 10612
BB Institution 11 1 317 11/09/2020         15708 10612
BB Institution 11 2 274 11/09/2020         15708 10612
BB Institution 12 1 222 11/09/2020         15708 10612
BB Institution 12 2 109 11/09/2020         15708 10612
BB Institution 13 1 615 11/09/2020         15708 10612
BB Institution 13 2 360 11/09/2020         15708 10612
BB Institution 14 1 508 11/09/2020         15708 10612
BB Institution 14 2 400 11/09/2020         15708 10612
BB Institution 15 1 . 11/09/2020         15708 10612
BB Institution 15 2 . 11/09/2020         15708 10612
BB Institution 16 1 382 11/09/2020         15708 10612
BB Institution 16 2 173 11/09/2020         15708 10612
;;;;

Normally the numbers in the variable count increase per data, but to keep it more easy, I've made them the same. 

For AA the result per date in the new variable cum_count_g1 would like to have is 1679 (i.e. the same for every date since the numbers don't increase, in the real data there would come in more results everyday and the count would increase), for AA the result in the second new variable cum_count_g2 I aim to have is 698.

Analogue, the result for BB in cum_count_g1 per date is (should be) 2244; for cum_count_g2 that would be 1516.

Thank you both for your support and feedback!

[mkeintz · Accepted solution]

First, your DATA1 step doesn't work - the lines of data need to be comma-separated.

Also your DATA1 MAY need to be sorted prior to the DATA2 step, since you are using a BY COUNTRY DATE statement.   But if the data are all aggregated such that all hospitals for a given COUNTRY/DATE are contiguous, even if COUNTRY and/or DATE are not in ascending order, then you could skip the sort and use BY COUNTRY DATE NOTSORTED, as here;

data WORK.DATA1;
  infile datalines dsd truncover;

  input COUNTRY:$2. HOSPITAL_NAME:$14. GROUP:BEST. COUNT:BEST. DATE:MMDDYY10.;
  format GROUP BEST. COUNT BEST. DATE MMDDYY10.;
  label COUNTRY="COUNTRY" HOSPITAL_NAME="HOSPITAL NAME" GROUP="GROUP" COUNT="COUNT" DATE="DATE";
datalines;
AA,Institution 1,1,152,11/01/2020
AA,Institution 1,2,66,11/01/2020
AA,Institution 2,1,.,11/01/2020
AA,Institution 2,2,.,11/01/2020
AA,Institution 3,1,196,11/01/2020
AA,Institution 3,2,64,11/01/2020
AA,Institution 4,1,150,11/01/2020
AA,Institution 4,2,70,11/01/2020
AA,Institution 5,1,.,11/01/2020
AA,Institution 5,2,.,11/01/2020
AA,Institution 6,1,360,11/01/2020
AA,Institution 6,2,127,11/01/2020
AA,Institution 7,1,103,11/01/2020
AA,Institution 7,2,47,11/01/2020
AA,Institution 8,1,150,11/01/2020
AA,Institution 8,2,30,11/01/2020
AA,Institution 9,1,342,11/01/2020
AA,Institution 9,2,202,11/01/2020
AA,Institution 10,1,226,11/01/2020
AA,Institution 10,2,92,11/01/2020
BB,Institution 11,1,317,11/01/2020
BB,Institution 11,2,274,11/01/2020
BB,Institution 12,1,222,11/01/2020
BB,Institution 12,2,109,11/01/2020
BB,Institution 13,1,615,11/01/2020
BB,Institution 13,2,360,11/01/2020
BB,Institution 14,1,508,11/01/2020
BB,Institution 14,2,400,11/01/2020
BB,Institution 15,1,.,11/01/2020
BB,Institution 15,2,.,11/01/2020
BB,Institution 16,1,382,11/01/2020
BB,Institution 16,2,173,11/01/2020
BB,Institution 17,1,.,11/01/2020
BB,Institution 17,2,.,11/01/2020
BB,Institution 18,1,200,11/01/2020
BB,Institution 18,2,200,11/01/2020
AA,Institution 1,1,152,11/03/2020
AA,Institution 1,2,66,11/03/2020
AA,Institution 2,1,.,11/03/2020
AA,Institution 2,2,.,11/03/2020
AA,Institution 3,1,196,11/03/2020
AA,Institution 3,2,64,11/03/2020
AA,Institution 4,1,150,11/03/2020
AA,Institution 4,2,70,11/03/2020
AA,Institution 5,1,.,11/03/2020
AA,Institution 5,2,.,11/03/2020
AA,Institution 6,1,360,11/03/2020
AA,Institution 6,2,127,11/03/2020
AA,Institution 7,1,103,11/03/2020
AA,Institution 7,2,47,11/03/2020
AA,Institution 8,1,150,11/03/2020
AA,Institution 8,2,30,11/03/2020
AA,Institution 9,1,342,11/03/2020
AA,Institution 9,2,202,11/03/2020
AA,Institution 10,1,226,11/03/2020
AA,Institution 10,2,92,11/03/2020
BB,Institution 11,1,317,11/03/2020
BB,Institution 11,2,274,11/03/2020
BB,Institution 12,1,222,11/03/2020
BB,Institution 12,2,109,11/03/2020
BB,Institution 13,1,615,11/03/2020
BB,Institution 13,2,360,11/03/2020
BB,Institution 14,1,508,11/03/2020
BB,Institution 14,2,400,11/03/2020
BB,Institution 15,1,.,11/03/2020
BB,Institution 15,2,.,11/03/2020
BB,Institution 16,1,382,11/03/2020
BB,Institution 16,2,173,11/03/2020
BB,Institution 17,1,.,11/03/2020
BB,Institution 17,2,.,11/03/2020
BB,Institution 18,1,200,11/03/2020
BB,Institution 18,2,200,11/03/2020
AA,Institution 1,1,152,11/05/2020
AA,Institution 1,2,66,11/05/2020
AA,Institution 2,1,.,11/05/2020
AA,Institution 2,2,.,11/05/2020
AA,Institution 3,1,196,11/05/2020
AA,Institution 3,2,64,11/05/2020
AA,Institution 4,1,150,11/05/2020
AA,Institution 4,2,70,11/05/2020
AA,Institution 5,1,.,11/05/2020
AA,Institution 5,2,.,11/05/2020
AA,Institution 6,1,360,11/05/2020
AA,Institution 6,2,127,11/05/2020
AA,Institution 7,1,103,11/05/2020
AA,Institution 7,2,47,11/05/2020
AA,Institution 8,1,150,11/05/2020
AA,Institution 8,2,30,11/05/2020
AA,Institution 9,1,342,11/05/2020
AA,Institution 9,2,202,11/05/2020
AA,Institution 10,1,226,11/05/2020
AA,Institution 10,2,92,11/05/2020
BB,Institution 11,1,317,11/05/2020
BB,Institution 11,2,274,11/05/2020
BB,Institution 12,1,222,11/05/2020
BB,Institution 12,2,109,11/05/2020
BB,Institution 13,1,615,11/05/2020
BB,Institution 13,2,360,11/05/2020
BB,Institution 14,1,508,11/05/2020
BB,Institution 14,2,400,11/05/2020
BB,Institution 15,1,.,11/05/2020
BB,Institution 15,2,.,11/05/2020
BB,Institution 16,1,382,11/05/2020
BB,Institution 16,2,173,11/05/2020
BB,Institution 17,1,.,11/05/2020
BB,Institution 17,2,.,11/05/2020
BB,Institution 18,1,200,11/05/2020
BB,Institution 18,2,200,11/05/2020
AA,Institution 1,1,152,11/09/2020
AA,Institution 1,2,66,11/09/2020
AA,Institution 2,1,.,11/09/2020
AA,Institution 2,2,.,11/09/2020
AA,Institution 3,1,196,11/09/2020
AA,Institution 3,2,64,11/09/2020
AA,Institution 4,1,150,11/09/2020
AA,Institution 4,2,70,11/09/2020
AA,Institution 5,1,.,11/09/2020
AA,Institution 5,2,.,11/09/2020
AA,Institution 6,1,360,11/09/2020
AA,Institution 6,2,127,11/09/2020
AA,Institution 7,1,103,11/09/2020
AA,Institution 7,2,47,11/09/2020
AA,Institution 8,1,150,11/09/2020
AA,Institution 8,2,30,11/09/2020
AA,Institution 9,1,342,11/09/2020
AA,Institution 9,2,202,11/09/2020
AA,Institution 10,1,226,11/09/2020
AA,Institution 10,2,92,11/09/2020
BB,Institution 11,1,317,11/09/2020
BB,Institution 11,2,274,11/09/2020
BB,Institution 12,1,222,11/09/2020
BB,Institution 12,2,109,11/09/2020
BB,Institution 13,1,615,11/09/2020
BB,Institution 13,2,360,11/09/2020
BB,Institution 14,1,508,11/09/2020
BB,Institution 14,2,400,11/09/2020
BB,Institution 15,1,.,11/09/2020
BB,Institution 15,2,.,11/09/2020
BB,Institution 16,1,382,11/09/2020
BB,Institution 16,2,173,11/09/2020
BB,Institution 17,1,.,11/09/2020
BB,Institution 17,2,.,11/09/2020
BB,Institution 18,1,200,11/09/2020
BB,Institution 18,2,200,11/09/2020
AA,Institution 1,1,152,11/13/2020
AA,Institution 1,2,66,11/13/2020
AA,Institution 2,1,.,11/13/2020
AA,Institution 2,2,.,11/13/2020
AA,Institution 3,1,196,11/13/2020
AA,Institution 3,2,64,11/13/2020
AA,Institution 4,1,150,11/13/2020
AA,Institution 4,2,70,11/13/2020
AA,Institution 5,1,.,11/13/2020
AA,Institution 5,2,.,11/13/2020
AA,Institution 6,1,360,11/13/2020
AA,Institution 6,2,127,11/13/2020
AA,Institution 7,1,103,11/13/2020
AA,Institution 7,2,47,11/13/2020
AA,Institution 8,1,150,11/13/2020
AA,Institution 8,2,30,11/13/2020
AA,Institution 9,1,342,11/13/2020
AA,Institution 9,2,202,11/13/2020
AA,Institution 10,1,226,11/13/2020
AA,Institution 10,2,92,11/13/2020
BB,Institution 11,1,317,11/13/2020
BB,Institution 11,2,274,11/13/2020
BB,Institution 12,1,222,11/13/2020
BB,Institution 12,2,109,11/13/2020
BB,Institution 13,1,615,11/13/2020
BB,Institution 13,2,360,11/13/2020
BB,Institution 14,1,508,11/13/2020
BB,Institution 14,2,400,11/13/2020
BB,Institution 15,1,.,11/13/2020
BB,Institution 15,2,.,11/13/2020
BB,Institution 16,1,382,11/13/2020
BB,Institution 16,2,173,11/13/2020
BB,Institution 17,1,.,11/13/2020
BB,Institution 17,2,.,11/13/2020
BB,Institution 18,1,200,11/13/2020
BB,Institution 18,2,200,11/13/2020
AA,Institution 1,1,152,11/15/2020
AA,Institution 1,2,66,11/15/2020
AA,Institution 2,1,.,11/15/2020
AA,Institution 2,2,.,11/15/2020
AA,Institution 3,1,196,11/15/2020
AA,Institution 3,2,64,11/15/2020
AA,Institution 4,1,150,11/15/2020
AA,Institution 4,2,70,11/15/2020
AA,Institution 5,1,.,11/15/2020
AA,Institution 5,2,.,11/15/2020
AA,Institution 6,1,360,11/15/2020
AA,Institution 6,2,127,11/15/2020
AA,Institution 7,1,103,11/15/2020
AA,Institution 7,2,47,11/15/2020
AA,Institution 8,1,150,11/15/2020
AA,Institution 8,2,30,11/15/2020
AA,Institution 9,1,342,11/15/2020
AA,Institution 9,2,202,11/15/2020
AA,Institution 10,1,226,11/15/2020
AA,Institution 10,2,92,11/15/2020
run;

data data2;
	do until (last.date);
		set data1;
		by COUNTRY DATE  notsorted;
		if 			GROUP = 1 	then CUM_COUNT_G1 + COUNT;
		else if 	GROUP = 2 	then CUM_COUNT_G2 + COUNT;
	end;

	do until (last.date);
		set data1;
		by COUNTRY DATE notsorted;
		output;
	end;
	CUM_COUNT_G1 = 0;
	CUM_COUNT_G2 = 0;
run;

BTW, once you are using NOTSORTED, then BY COUNTRY DATE NOTSORTED is the same as BY DATE COUNTRY NOTSORTED.

Now, this program is exactly as @PeterClemmensen provided, except he pre-sorted the data by DATE COUNTRY, and therefore used "until (last.country)".  My point here is that the "last." attribute should be applied to whichever variable is the minor sort-key (i.e. the rightmost var), not the major sort-key.

Now, if you do sort the dataset, then you can reduce the DATA2 step coding meaningfully:

proc sort data=data1; 
  by country date; 
run;

data data2;
  set data1 (in=firstpass) data1 (in=secondpass);
  by country date;

  array cum_count_g {2} ;
  if first.date then call missing(of cum_count_g{*});
  if firstpass=1 then cum_count_g{group}+count;

  if secondpass;
run;

Editted note:  @sidello asked about the code above, which I had left unannotated.

But @Kurt_Bremser provided an excelled explanation of the code.  I copy it here, so that it appears in this response, which has been marked as "solution":  Thanks @Kurt_Bremser for doing my due diligence:

set
  data1 (in=firstpass)
  data1 (in=secondpass)
;
by country date;

This is called an "interleave". The datasets are "stacked" on top of each other, but the BY groups are honored.

It means that first all observations of a group are read from the first dataset, then all observations from the second dataset; since, in this case, the same dataset is used, these statements create a double read through each BY group.

array cum_count_g {2};

The array is defined, with an element for each possible value in variable group.

if first.date then call missing(of cum_count_g{*});

At the start of a BY group, the array is cleared.

if firstpass=1 then cum_count_g{group}+count;

As long as observations are read from the "first" dataset, values are cumulated.

if secondpass;

But only when observations are read from the "second" dataset, an output is performed.

[sidello]

Hi @mkeintz and @PeterClemmensen ,

Thank you both for guiding me and your assistance in solving this problem. The last solution with the sorting of the data provides indeed a more parsimonious solution. 

If you would have some more time, would you mind to explain what exactly this piece of code does? I have a rough idea on what the underlying steps are going on, but especially the first part is new to me. 

data data2;
  set data1 (in=firstpass) data1 (in=secondpass);
  by country date;

  array cum_count_g {2} ;
  if first.date then call missing(of cum_count_g{*});
  if firstpass=1 then cum_count_g{group}+count;

  if secondpass;
run;

 Either way, thank you both! 

[Kurt_Bremser]

set
  data1 (in=firstpass)
  data1 (in=secondpass)
;
by country date;

This is called an "interleave". The datasets are "stacked" on top of each other, but the BY groups are honored.

It means that first all observations of a group are read from the first dataset, then all observations from the second dataset; since, in this case, the same dataset is used, these statements create a double read through each BY group.

array cum_count_g {2};

The array is defined, with an element for each possible value in variable group.

if first.date then call missing(of cum_count_g{*});

At the start of a BY group, the array is cleared.

if firstpass=1 then cum_count_g{group}+count;

As long as observations are read from the "first" dataset, values are cumulated.

if secondpass;

But only when observations are read from the "second" dataset, an output is performed.