topic How do I sum down a column in a fashion that is similar to a cumulative frequency? in SAS Programming

How do I sum down a column in a fashion that is similar to a cumulative frequency?

awardell — Sun, 12 Sep 2021 18:58:41 GMT

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!

Re: How do I sum down a column in a fashion that is similar to a cumulative frequency?

PaigeMiller — Sun, 12 Sep 2021 18:51:08 GMT

Please explain why the 3rd row shows total_stitches=3

Re: How do I sum down a column in a fashion that is similar to a cumulative frequency?

awardell — Sun, 12 Sep 2021 18:53:00 GMT

that is a typo. My apologies. It should be 8

Re: How do I sum down a column in a fashion that is similar to a cumulative frequency?

PaigeMiller — Sun, 12 Sep 2021 18:58:55 GMT

You use a SUM statement

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

Re: How do I sum down a column in a fashion that is similar to a cumulative frequency?

Tom — Sun, 12 Sep 2021 18:59:08 GMT

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.