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!
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.
Please explain why the 3rd row shows total_stitches=3
You use a SUM statement
data want;
set have;
by id;
if first.id then total_stitches=0;
total_stitches+num_stitches;
run;
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.
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!
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.
Ready to level-up your skills? Choose your own adventure.