BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
km0927
Obsidian | Level 7

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
VDD
Ammonite | Level 13 VDD
Ammonite | Level 13
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;

View solution in original post

10 REPLIES 10
bobpep212
Quartz | Level 8
data want;
set have;
count+1;
if surgery = 1 then count=0;
run;

Try something like this.

bobpep212
Quartz | Level 8

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
Quartz | Level 8
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
Ammonite | Level 13 VDD
Ammonite | Level 13
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
Ammonite | Level 13 VDD
Ammonite | Level 13
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
Super User
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
Obsidian | Level 7

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
Tourmaline | Level 20

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
Ammonite | Level 13 VDD
Ammonite | Level 13
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
Quartz | Level 8
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!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 10 replies
  • 2851 views
  • 0 likes
  • 5 in conversation