Obsidian | Level 7

## cumulative sum by group on different conditions

hello

I have been searching the solution a while, but I couldn't find solutions in SAS in communities.

i want to make cumulative sum by group on different conditions.

the following data is raw data. it's about patient's medical utilization.

id date surgery_YN

1 2002/01/28 0
1 2002/02/15 0
1 2002/04/06 0
1 2002/06/07 1
1 2002/08/10 0
1 2003/01/12 0
2 2003/01/15 0
2 2003/02/10 1
2 2003/03/10 0
2 2003/04/08 0

i want calculate how many times patient visited clinics before he had surgery. so i want make data like this.

id date surgery_YN count

1 2002/01/28 0 1
1 2002/02/15 0 2
1 2002/04/06 0 3
1 2002/06/07 1 0
1 2002/08/10 0 0
1 2003/01/12 0 0
2 2003/01/15 0 1
2 2003/02/10 0 2
2 2003/03/10 1 0
2 2003/04/08 0 0

i used 'retain' code and i can make count '0' after surgery. but i don't know how to make cumulative sum before surgery. I wonder if anyone can help me a little bit. Thank you very much for your help!

1 ACCEPTED SOLUTION

Accepted Solutions
Ammonite | Level 13

## Re: cumulative sum by group on different conditions

data have;
input id date \$10. surgery_YN;
cards;
1 2002/01/28 0
1 2002/02/15 0
1 2002/04/06 0
1 2002/06/07 1
1 2002/08/10 0
1 2003/01/12 0
2 2003/01/15 0
2 2003/02/10 1
2 2003/03/10 0
2 2003/04/08 0
;
proc sort data=have;
by id date;
run;
data want;
set have;
by Id date;
if first.id then
do;
count = 0;
end;
if surgery_yn = 0 and had_surgery = 0 then count+1;
else if surgery_yn = 1 then
do;
count=0 ;
end;
run;
10 REPLIES 10
Quartz | Level 8

## Re: cumulative sum by group on different conditions

data want;
set have;
count+1;
if surgery = 1 then count=0;
run;

Try something like this.

Quartz | Level 8

## Re: cumulative sum by group on different conditions

Disregard...I see what you're looking for is a little different. Try this.

set have;
by id;
count+1;
if surgery = 1 then do;
count=0;
end;
run;
Quartz | Level 8

## Re: cumulative sum by group on different conditions

Also, if you post in the future, make sure to put the post in the correct community group. You will get a faster response. Something like this would be better in the SAS Programming community.
Ammonite | Level 13

## Re: cumulative sum by group on different conditions

data have;
input id date \$10. surgery_YN;
cards;
1 2002/01/28 0
1 2002/02/15 0
1 2002/04/06 0
1 2002/06/07 1
1 2002/08/10 0
1 2003/01/12 0
2 2003/01/15 0
2 2003/02/10 1
2 2003/03/10 0
2 2003/04/08 0
;
proc sort data=have;
by id date;
run;
data want;
set have;
by Id date;
if first.id then
do;
count = 0;
end;
if surgery_yn = 0 and had_surgery = 0 then count+1;
else if surgery_yn = 1 then
do;
count=0 ;
end;
run;
Ammonite | Level 13

## Re: cumulative sum by group on different conditions

ID 2 record 2 in the want data is incorrect because the incoming data doesn't support the result to add +1 to the count.
Super User

## Re: cumulative sum by group on different conditions

data have;
input id date \$10. surgery_YN;
cards;
1 2002/01/28 0
1 2002/02/15 0
1 2002/04/06 0
1 2002/06/07 1
1 2002/08/10 0
1 2003/01/12 0
2 2003/01/15 0
2 2003/02/10 1
2 2003/03/10 0
2 2003/04/08 0
;
data temp;
set have;
by id surgery_YN notsorted;
if first.id then n=0;
n+first.surgery_YN;
run;
data want;
set temp;
by n notsorted;
if first.n then count=0;
count+1;
if n ne 1 then count=0;
drop n;
run;

Just for fun, second way:

data want;
set have;
by id;
retain found;
if first.id then do;found=0;count=0;end;
count+1;
if  surgery_YN=1 then found=1;
if found then count=0;
drop found;
run;

Obsidian | Level 7

## Re: cumulative sum by group on different conditions

to bobpep212, it worked out cumulative before surgery, but it didn't seperated by group. thank you for advice and correcting community group.

to VDD, I Intentionally make 'visiting data(billing data)' brief, and i think i made some error. but it worked out right. i really appreciate that.

for Ksharp, I tried VDD's code first. and he or she posted before your code. sorry for not trying your code.

Tourmaline | Level 20

## Re: cumulative sum by group on different conditions

Sorry too late ,but my contrib

data have;
input id date : yymmdd10. surgery_YN;
format date yymmdd10.;
cards;
1 2002/01/28 0
1 2002/02/15 0
1 2002/04/06 0
1 2002/06/07 1
1 2002/08/10 0
1 2003/01/12 0
2 2003/01/15 0
2 2003/02/10 0
2 2003/03/10 1
2 2003/04/08 0
;

data want;
set have;
by id date;
if first.id then do;f=0;count=0;end;
f+surgery_YN;
if f<1 then count+1;else count=0;
drop f;
run;

Ammonite | Level 13

## Re: cumulative sum by group on different conditions

if the data supports that an ID could have 2 or more surgeries that had no records between the surgeries where surgery_YN = 0, you could change the had_surgery =1 to had_surgery +1.
this would give you a count of sequential surgeries the patient had on the final surgery record for the span of the sequenced surgery records.
Quartz | Level 8

## Re: cumulative sum by group on different conditions

You're welcome. Glad you got it to work. Yeah, I realized my first post wasn't what you were looking for so I posted a second version that matched what you had in your want output. But you have lots of options that work here!
Discussion stats
• 10 replies
• 2851 views
• 0 likes
• 5 in conversation