topic Re: Cumulative column based on critera in SAS Programming

Cumulative column based on critera

nharuka — Mon, 21 Mar 2022 18:41:15 GMT

I have a longitudinal dataset with several observations per person. I'm trying to make a cumulative weeks count for each person. How can I do this? I'm a SAS beginner so any help would be appreciated!


data have;
  input ID weeks;
 datalines;
1   0.0
1   0.0
1   1.0
1   1.5
2   0.0
2   2.5
2   3.5
3   0.0
3   0.5
3   4.5
3   4.58

I want the dataset to look like this

ID Weeks Cuml

1 0.0 0.0

1 1.0 1.0

1 1.5 2.5

2 0.0 0.0

2 2.5 2.5

2 3.5 6.0

3 0.0 0.0

3 0.5 0.5

3 4.5 5.0

3 4.58 9.58

Re: Cumulative column based on critera

PaigeMiller — Mon, 21 Mar 2022 19:14:32 GMT

A recent example:

https://communities.sas.com/t5/SAS-Procedures/GROUP-BY/m-p/803182#M81670

Re: Cumulative column based on critera

nharuka — Mon, 21 Mar 2022 19:39:56 GMT

Hi! I tried using that example, but the problem is that the count doesn't reset for each new ID number, which I need it to do. How can I modify the code for the to reset for each new ID number?

data new;
    set have;
    by ID;
    if first.id then do;
        cumul_wks=0;
    end;
    cumul_wks + weeks;
run;

Re: Cumulative column based on critera

PaigeMiller — Mon, 21 Mar 2022 19:44:07 GMT

Are there errors in the log?

If so, we need to see the log (all of it for this DATA step, every single character, every single line, nothing chopped out) copied as text and pasted into your reply in the window that appears after you click on the </> icon.

Are there errors in the output?

If so, show us a screen capture. Make the screen capture and click on the "Insert Photos" icon.

Re: Cumulative column based on critera

ballardw — Mon, 21 Mar 2022 19:52:25 GMT

Log?

Is your data sorted by the ID variable? You might be seeing a previous version of the output data set that wasn't replaced because of a sort error.

Or if you have a variable in the Have data set already named Cumul_wks. Retain will reset when the new record is brought into the data vector if that variable is in the source data.

Re: Cumulative column based on critera

nharuka — Mon, 21 Mar 2022 19:57:14 GMT

I didn't have any errors in my log. I got it to work by using retain and modifying the code a bit. Thanks for the help!

data new;
	set have;
	by ID;
	retain cumul_wks;
	if first.id then cumul_wks = weeks;
	else cumul_wks = cumul_wks + weeks;
run;

Re: Cumulative column based on critera

mkeintz — Mon, 21 Mar 2022 23:34:00 GMT

@nharuka wrote:

I didn't have any errors in my log. I got it to work by using retain and modifying the code a bit. Thanks for the help!
data new;
	set have;
	by ID;
	retain cumul_wks;
	if first.id then cumul_wks = weeks;
	else cumul_wks = cumul_wks + weeks;
run;

This code appears correct. BUT, if for some reason cumul_wks already exists in dataset HAVE (as suggested by @ballardw), you will not get actual cumulative weeks. This is because the RETAINed value will be overwritten by the value retrieved in the SET statement.

Re: Cumulative column based on critera

PaigeMiller — Tue, 22 Mar 2022 10:27:44 GMT

@nharuka wrote:

I didn't have any errors in my log. I got it to work by using retain and modifying the code a bit. Thanks for the help!
data new;
	set have;
	by ID;
	retain cumul_wks;
	if first.id then cumul_wks = weeks;
	else cumul_wks = cumul_wks + weeks;
run;

Won't work properly with missing values.

You should answer the question about exactly what was wrong in the code I provided (where I specifically asked you to SHOW US the results).

Re: Cumulative column based on critera

nharuka — Tue, 22 Mar 2022 22:51:55 GMT

I don't have any missing values in my dataset. When I used your example code, it kept on aggregating and not resetting for each new ID. It looked like this

ID Weeks Cumul_wks

1 0.0 0.0

1 0.0 0.0

1 1.0 1.0

1 1.5 2.5

2 0.0 2.5

2 2.5 4.5

2 3.5 12.8

3 0.0 12.8

3 0.5 13.3

3 4.5 17.8

3 4.58 22.4

Re: Cumulative column based on critera

ballardw — Tue, 22 Mar 2022 23:03:19 GMT

Please show the Log of the actual code that you ran. The data step and any messages. Copy the text from the log, on the forum open a text box with the </> icon that appears above the message window and then paste the text.

Re: Cumulative column based on critera

nharuka — Tue, 22 Mar 2022 23:19:35 GMT

It works now that I reran the code? I'm not sure why it didn't work before.


data have;
  input ID weeks;
 datalines;
1   0.0
1   0.0
1   1.0
1   1.5
2   0.0
2   2.5
2   3.5
3   0.0
3   0.5
3   4.5
3   4.58

data new;
    set have;
    by ID;
    if first.id then do;
        cumul_wks=0;
    end;
    cumul_wks + weeks;
run;

proc print data=new;
run;

Log

1 data have;
2 input ID weeks;
3 datalines;

NOTE: The data set WORK.HAVE has 11 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 33.26 seconds
cpu time 0.93 seconds

15 data have;
16 input ID weeks;
17 datalines;

NOTE: SAS went to a new line when INPUT statement reached past the end of a line.
NOTE: The data set WORK.HAVE has 11 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

30 data new;
31 set have;
32 by ID;
33 if first.id then do;
34 cumul_wks=0;
35 end;
36 cumul_wks + weeks;
37 run;

NOTE: There were 11 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.NEW has 11 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

38 proc print data=new;
NOTE: Writing HTML Body file: sashtml.htm
39 run;

NOTE: There were 11 observations read from the data set WORK.NEW.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.48 seconds
cpu time 0.21 seconds

Output