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

Hello! 

 

I am working on a project, and to my understanding, I need a way to sum values down a column, by an id group: however my variable needs to sum only values from visits that were achieved from dates above it. 

 

For example, say I am a doctor, with data on all of the visits from patients. If a patient needs stitches, I total the number of stitches needed for that visit. 

 

ID visit_order num_stitches total_stitches (to this point)
A 1 5 5
A 2 . 5
A 3 3 8
B 1 2 2
B 2 4 6
B 3 . 6
B 4 12 18
C 1 . .
C 2 . .
D 1 .  
D 2 2 2
D 3 . 2
and so on       

 

How can I write code to get the total_stitches column to work? I have tried various uses of the lag function, but can not get any to work. Thank you so much for your time!

 

Here is my example data step, in case that may be of use. 

data have; 
	input ID $ visit_order num_stitches; 
	DATALINES; 
A	1	5	
A	2	.	
A	3	3	
B	1	2	
B	2	4	
B	3	.	
B	4	12	
C	1	.	
C	2	.	
D	1	.	
D	2	2	
D	3	.	
; 
run; 


data want; input ID $ visit_order num_stitches total_stitches; DATALINES; A 1 5 5 A 2 . 5 A 3 3 8 B 1 2 2 B 2 4 6 B 3 . 6 B 4 12 18 C 1 . . C 2 . . D 1 . . D 2 2 2 D 3 . 2 ; run;

Thank you again for any insight! I greatly appreciate it! 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Just use a SUM statement.

data have; 
	input ID $ visit_order num_stitches expected; 
DATALINES; 
A	1	5	5
A	2	.	5
A	3	3	8
B	1	2	2
B	2	4	6
B	3	.	6
B	4	12	18
C	1	.	.
C	2	.	.
D	1	.	.
D	2	2	2
D	3	.	2
; 

data want;
  set have;
  by id;
  if first.id then total_stitches=.;
  total_stitches + num_stitches;
run;

But why are you setting total_stitches to missing instead of zero when first visit does not have any stitches?  If you would rather the total be zero in those cases just change the value that is assigned for the first observation for the patient.

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

Please explain why the 3rd row shows total_stitches=3

--
Paige Miller
awardell
Obsidian | Level 7
that is a typo. My apologies. It should be 8
PaigeMiller
Diamond | Level 26

You use a SUM statement

 

data want;
    set have;
    by id;
    if first.id then total_stitches=0;
    total_stitches+num_stitches;
run;
    

 

--
Paige Miller
Tom
Super User Tom
Super User

Just use a SUM statement.

data have; 
	input ID $ visit_order num_stitches expected; 
DATALINES; 
A	1	5	5
A	2	.	5
A	3	3	8
B	1	2	2
B	2	4	6
B	3	.	6
B	4	12	18
C	1	.	.
C	2	.	.
D	1	.	.
D	2	2	2
D	3	.	2
; 

data want;
  set have;
  by id;
  if first.id then total_stitches=.;
  total_stitches + num_stitches;
run;

But why are you setting total_stitches to missing instead of zero when first visit does not have any stitches?  If you would rather the total be zero in those cases just change the value that is assigned for the first observation for the patient.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 765 views
  • 3 likes
  • 3 in conversation