topic Calculate cumulative sum by column in SAS Programming

Calculate cumulative sum by column

ANKH1 — Thu, 17 Aug 2023 17:02:00 GMT

Hi, there is the following dataset where each ageX column indicates if there was an incident or not.

data dsin;
input ID age1 age2 age3 age4 age5 age6;
datalines;
1 1 0 0 1 0 0
2 0 1 1 0 0 0
3 0 0 0 1 0 0
4 0 0 0 0 0 0
5 1 0 0 0 1 0
6 0 0 1 0 0 0
;
run;

We would like to have the cumulative number of people who had an incident by age. The tricky part is that once a person has an incident and it is accounted for, if this person has incidents after that, it still counts only as 1. Let's say for ID1:it presents an incident at age1, and age4 as well. But this person can only be accounted once. The below table is the output we are looking for:

Age	age1	age2	age3	age4	age5	age6
Cumulative	2	3	4	5	5	5

Re: Calculate cumulative sum by column

Reeza — Thu, 17 Aug 2023 17:37:18 GMT

Should be a faster way than this, but I'm sleep deprived today.

data dsin;
input ID age1 age2 age3 age4 age5 age6;
datalines;
1 1 0 0 1 0 0
2 0 1 1 0 0 0
3 0 0 0 1 0 0
4 0 0 0 0 0 0
5 1 0 0 0 1 0
6 0 0 1 0 0 0
;
run;

data _dsin / view=_dsin;
set dsin;
array age age1-age6;
flag=0;
do i=1 to dim(age);
if age(i) = 1 and flag=0 then flag=1;
else if age(i) = 1 and flag=1 then age(i) = 0;
end;
keep id age:;
run;

proc means data=_dsin noprint;
output out=agg1 sum=;
run;

data want;
set agg1;
array age age1-age6;

do i=2 to dim(age);
age(i) = age(i) + age(i-1);
end;
keep age:;
run;

Re: Calculate cumulative sum by column

ANKH1 — Fri, 18 Aug 2023 00:45:26 GMT

Hi! Thank you so much for your response. It worked perfectly. But I encountered that some of the datasets will have more than 1 incident for the same ID in the same age variable. Is it possible to account for this in the code? That is, the ID will only be accounted once, even though it reported 2 or more incidents for the same ID.

Re: Calculate cumulative sum by column

Ksharp — Fri, 18 Aug 2023 11:37:53 GMT

data dsin;
input ID age1 age2 age3 age4 age5 age6;
datalines;
1 1 0 0 1 0 0
2 0 1 1 0 0 0
3 0 0 0 1 0 0
4 0 0 0 0 0 0
5 1 0 0 0 1 0
6 0 0 1 0 0 0
;
run;

proc sql;
create table want as
select 'Cumulative' as Age ,
 sum(age1) as age1,
 sum(max(age1,age2)) as age2,
 sum(max(age1,age2,age3)) as age3,
 sum(max(age1,age2,age3,age4)) as age4,
 sum(max(age1,age2,age3,age4,age5)) as age5,
 sum(max(age1,age2,age3,age4,age5,age6)) as age6
 from dsin ;
quit;

Re: Calculate cumulative sum by column

mkeintz — Fri, 18 Aug 2023 13:13:21 GMT

data dsin;
input ID age1 age2 age3 age4 age5 age6;
datalines;
1 1 0 0 1 0 0
2 0 1 1 0 0 0
3 0 0 0 1 0 0
4 0 0 0 0 0 0
5 1 0 0 0 1 0
6 0 0 1 0 0 0
run;

data want (keep=cumcount:);
  set dsin end=end_of_data;
  array age {6};
  array cumcount {6} (6*0);

  if whichn(1,of age{*})>0 then do _n_=whichn(1,of age{*}) to dim(age);
    cumcount{_n_}+1;
  end;

  if end_of_data;
run;

Re: Calculate cumulative sum by column

Tom — Fri, 18 Aug 2023 13:26:39 GMT

It probably would be simpler not to have that wide structure to start with.

data have;
  input ID @;
  do age=1 to 6;
     input incident  @;
     Cumulative=max(incident ,Cumulative);
     output;
  end;
datalines;
1 1 0 0 1 0 0
2 0 1 1 0 0 0
3 0 0 0 1 0 0
4 0 0 0 0 0 0
5 1 0 0 0 1 0
6 0 0 1 0 0 0
;

Then getting the sum is easy.

proc means nway sum;
  class age;
  var cumulative;
run;

Re: Calculate cumulative sum by column

Reeza — Fri, 18 Aug 2023 14:56:28 GMT

That would imply a different structure than above as you have only 1 variable per age, or that it's not just 0/1?

Re: Calculate cumulative sum by column

ANKH1 — Fri, 18 Aug 2023 15:15:41 GMT

Yes, these numbers 0,1,2 come from a frequency table.

Re: Calculate cumulative sum by column

Reeza — Fri, 18 Aug 2023 15:37:04 GMT

data dsin;
input ID age1 age2 age3 age4 age5 age6;
datalines;
1 1 0 0 1 0 0
2 0 1 1 0 0 0
3 0 0 0 1 0 0
4 0 0 0 0 0 0
5 1 0 0 0 1 0
6 0 0 1 0 0 0
;
run;

data _dsin / view=_dsin;
set dsin;
array age age1-age6;
flag=0;
do i=1 to dim(age);
if age(i) >= 1 and flag=0 then do; age(i)=1; flag=1; end
else if age(i) >= 1 and flag=1 then age(i) = 0;
end;
keep id age:;
run;

proc means data=_dsin noprint;
output out=agg1 sum=;
run;

data want;
set agg1;
array age age1-age6;

do i=2 to dim(age);
age(i) = age(i) + age(i-1);
end;
keep age:;
run;

That modification should work 🙂