cumulative sum by group on different conditions

[km0927]

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!

[bobpep212]

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

Try something like this.

[bobpep212]

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

data want (drop=had_surgery);
set have;
retain had_surgery;
by id;
count+1;
if surgery = 1 then do;
 count=0;
 had_surgery=1;
 end;
if had_surgery=1 then count=0;
if last.id then had_surgery=0;
run;

[bobpep212]

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.

[VDD · Accepted solution]

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;
	retain had_surgery;
	set have;
	by Id date;
	if first.id then
		do;
			had_surgery = 0;
			count = 0;
		end;
	if surgery_yn = 0 and had_surgery = 0 then count+1;
	else if surgery_yn = 1 then
		do;
			had_surgery = 1;
			count=0 ;
		end;
run;

[VDD]

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.

[Ksharp]

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;

[km0927]

thank you for all advice.

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.

[novinosrin]

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;

[VDD]

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.

[bobpep212]

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!