topic cumulative sum with by grouping condition in SAS Programming

cumulative sum with by grouping condition

skavli — Tue, 07 Apr 2020 10:47:11 GMT

Hi y'all i hope you are all safe and coping the lockdown,

I know this topic had been discussed way times than it should be, but i really tried and fetched over the solutions for a few times before coming to you.

I'm working on a simple covid-19 data set containing these variables :

Date - Cases - Deaths - Country - Population - TotalCases

My problem is that i want to sum the number of cases by Country but i obtain very weird results.

First I sorted my data by Country and Date (by default order (ascending))

proc sort data=covid.data2;
by Country Date;
run;

Then I told sas to consider to start from the first case in cases and assign it to totalcases the sum until the last one is reached for each country :

data covid.data2;
set covid.data2;
by Country;
if First.cases then TotalCases = first.cases;
TotalCases + cases;
run;

However the results are absolutely not the sum of each case daily by country ! I can't seem to find the error, can you help ?

here is a screen of the output (an example of afghanistan ) :

Re: cumulative sum with by grouping condition

PaigeMiller — Tue, 07 Apr 2020 10:53:52 GMT

data covid.data2;
set covid.data2;
by Country;
if first.country then TotalCases = 0;
TotalCases + cases; 
run;

You want first.country, not first.case. The first time a country appears in the data set, the cumulative count is set to zero.

Re: cumulative sum with by grouping condition

Kurt_Bremser — Tue, 07 Apr 2020 11:10:12 GMT

first.cases does not exist; first.country is an automatic boolean variable that can only have the values 0 (false) or 1 (true).

You want

data covid.data3;
/* do not use the input dataset name here, unless you like destroyed datasets
that need to be re-created if something happens in the data step */
set covid.data2;
by country;
if first.country
then TotalCases = cases;
else TotalCases + cases;
run;