topic Re: Sum up column based on ID in SAS Programming

Sum up column based on ID

sasuser_sk — Mon, 26 Jul 2021 21:56:33 GMT

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

Re: Sum up column based on ID

Reeza — Mon, 26 Jul 2021 22:08:21 GMT

That's a RUNNING TOTAL.

Retain - holds value across rows
BY group processing to ensure that the process is reset at the first of each ID.
SUM() function in case of missing data

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

Re: Sum up column based on ID

sasuser_sk — Mon, 26 Jul 2021 22:14:39 GMT

Thank you, Reeza.

Re: Sum up column based on ID

maguiremq — Mon, 26 Jul 2021 22:16:45 GMT

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

Re: Sum up column based on ID

sasuser_sk — Mon, 26 Jul 2021 22:46:57 GMT

Thank you for another example! I see that you used else if instead.