topic Re: Running Total in SAS Programming

Running Total

anonymous_user — Fri, 24 Jul 2015 08:58:44 GMT

Hi,

I need to capture a running_sum but unsure how to against the key_hedge and Dealt_amount.

Key_hedge and Dealt amount are in a table called static_summit.

Any ideas please?

Key_hedge	Dealt_amount	Running_sum
421240GapClose	15,000,000
421241GapClose	1,000,000
421241GapClose	1,000,000
421241GapClose	1,000,000
421241GapClose	1,000,000
421241GapClose	1,000,000
421242GapClose	1,000,000
421242GapClose	1,000,000

Re: Running Total

RW9 — Fri, 24 Jul 2015 09:38:58 GMT

If I guess right then:

data want;

set have;

by key_hedge;

retain running_sum;

if first.key_hedge then running_sum=dealt_amount;

else running_sum=running_sum+dealt_amount;

run;

Re: Running Total

Steelers_In_DC — Fri, 24 Jul 2015 13:21:09 GMT

Here you go, this is set up to start the running_sum as new for every new key_hedge, if that is not what you want you should remove the line that starts with 'if'.

data have;

infile cards dsd;

format Key_hedge $14. Dealt_amount comma10.;

input Key_hedge$ Dealt_amount;

cards;

421240GapClose,15000000

421241GapClose,1000000

421242GapClose,1000000

;

data want;

format Key_hedge $14. Dealt_amount Running_sum comma10.;

set have;

by key_hedge;

if first.key_hedge then running_sum = 0;

running_sum + dealt_amount;

run;

Re: Running Total

anonymous_user — Fri, 24 Jul 2015 15:20:15 GMT

This code doesn't seem to work. I have running_sum and dealt_amount all blank.

Maybe I wasn't too clear originally .

Key_hedge and Dealt_amount are vaiables im pulling in from a table Summit static.

Running_sum doesn't exist so im needing to create this variable. I have manually added the data within the running sum as to what it is i need to see but the code above doesn't do this.

Key_hedge	Dealt_amount	Running_sum
421240GapClose	15,000,000	15000000
421241GapClose	1,000,000	1000000
421241GapClose	1,000,000	2000000
421241GapClose	1,000,000	3000000
421241GapClose	1,000,000	4000000
421241GapClose	1,000,000	5000000
421242GapClose	1,000,000	1000000
421242GapClose	1,000,000	2000000

Re: Running Total

anonymous_user — Fri, 24 Jul 2015 15:21:50 GMT

Thanks Mark, not sure this willl work as the key_hedge will change and it's best not to keep adding in the cards each time

Re: Running Total

RW9 — Fri, 24 Jul 2015 15:23:49 GMT

I don;t see any reason why the code would not work, the data is sorted by key_hedge yes? And dealt amount is numeric yes? If so this should work:

data want;

set have;

by key_hedge;

retain running_sum;

if first.key_hedge then running_sum=dealt_amount;

else running_sum=sum(running_sum,dealt_amount); /* Maybe change to this also in case your first value is 0 */

run;

If it doesn't please post test data - as a datastep - so I can test it.

Re: Running Total

Reeza — Fri, 24 Jul 2015 15:26:04 GMT

The CARDS is used to generate sample data (HAVE) for testing. The second data step is what you need.

Re: Running Total

Steelers_In_DC — Fri, 24 Jul 2015 15:26:47 GMT

The data was added using cards only to show an example. How's this:

data whatever;

set static_summit;

by key_hedge;

if first.key_hedge then running_sum = 0;

running_sum + dealt_amount;

run;