Hi Everyone: Help will be greatly appreciated. Thanks!
I have this dataset:
ID MONTH AMOUNT
1679 201611 16.47
1679 201612 1.95
1679 201701 -1.95
1679 201702 188.56
4656 201611 287.52
4656 201612 277.92
4656 201701 285
4656 201702 277.87
4656 201703 284.18
4656 201704 282.37
I want to create Total_Amount Column that adds Amount based on ID and want output to look like this:
ID MONTH AMOUNT TOTAL_AMOUNT
1679 201611 16.47 16.47
1679 201612 1.95 18.42
1679 201701 -1.95 16.47
1679 201702 188.56 205.03
4656 201611 287.52 287.52
4656 201612 277.92 565.44
4656 201701 285 850.44
4656 201702 277.87 1128.31
4656 201703 284.18 1412.49
4656 201704 282.37 1694.86
That's a RUNNING TOTAL.
data want;
set have;
by ID;
retain RUNNING_TOTAL;
if first.ID then RUNNING_TOTAL = AMOUNT;
else RUNNING_TOTAL = sum(RUNNING_TOTAL, AMOUNT);
run;
@sasuser_sk wrote:
Hi Everyone: Help will be greatly appreciated. Thanks!
I have this dataset:
ID MONTH AMOUNT
1679 201611 16.47
1679 201612 1.95
1679 201701 -1.95
1679 201702 188.56
4656 201611 287.52
4656 201612 277.92
4656 201701 285
4656 201702 277.87
4656 201703 284.18
4656 201704 282.37
I want to create Total_Amount Column that adds Amount based on ID and want output to look like this:
ID MONTH AMOUNT TOTAL_AMOUNT
1679 201611 16.47 16.47
1679 201612 1.95 18.42
1679 201701 -1.95 16.47
1679 201702 188.56 205.03
4656 201611 287.52 287.52
4656 201612 277.92 565.44
4656 201701 285 850.44
4656 201702 277.87 1128.31
4656 201703 284.18 1412.49
4656 201704 282.37 1694.86
That's a RUNNING TOTAL.
data want;
set have;
by ID;
retain RUNNING_TOTAL;
if first.ID then RUNNING_TOTAL = AMOUNT;
else RUNNING_TOTAL = sum(RUNNING_TOTAL, AMOUNT);
run;
@sasuser_sk wrote:
Hi Everyone: Help will be greatly appreciated. Thanks!
I have this dataset:
ID MONTH AMOUNT
1679 201611 16.47
1679 201612 1.95
1679 201701 -1.95
1679 201702 188.56
4656 201611 287.52
4656 201612 277.92
4656 201701 285
4656 201702 277.87
4656 201703 284.18
4656 201704 282.37
I want to create Total_Amount Column that adds Amount based on ID and want output to look like this:
ID MONTH AMOUNT TOTAL_AMOUNT
1679 201611 16.47 16.47
1679 201612 1.95 18.42
1679 201701 -1.95 16.47
1679 201702 188.56 205.03
4656 201611 287.52 287.52
4656 201612 277.92 565.44
4656 201701 285 850.44
4656 201702 277.87 1128.31
4656 201703 284.18 1412.49
4656 201704 282.37 1694.86
Thank you, Reeza.
I like @Reeza's better, but here's how I did it before I saw their solution.
data want;
set have;
by id;
retain total_amount;
if first.id then total_amount = amount;
else if not first.id then total_amount + amount;
run;
id month amount total_amount 1679 201611 16.47 16.47 1679 201612 1.95 18.42 1679 201701 -1.95 16.47 1679 201702 188.56 205.03 4656 201611 287.52 287.52 4656 201612 277.92 565.44 4656 201701 285.00 850.44 4656 201702 277.87 1128.31 4656 201703 284.18 1412.49 4656 201704 282.37 1694.86
Thank you for another example! I see that you used else if instead.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.