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!
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;
data want;
set have;
count+1;
if surgery = 1 then count=0;
run;
Try something like this.
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;
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;
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;
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.