Running Total

Reply
Frequent Contributor
Posts: 138

Running Total


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_hedgeDealt_amountRunning_sum
421240GapClose15,000,000
421241GapClose1,000,000
421241GapClose1,000,000
421241GapClose1,000,000
421241GapClose1,000,000
421241GapClose1,000,000
421242GapClose1,000,000
421242GapClose1,000,000
Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Running Total

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;

Frequent Contributor
Posts: 138

Re: Running Total

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_hedgeDealt_amountRunning_sum
421240GapClose15,000,00015000000
421241GapClose1,000,0001000000
421241GapClose1,000,0002000000
421241GapClose1,000,0003000000
421241GapClose1,000,0004000000
421241GapClose1,000,0005000000
421242GapClose1,000,0001000000
421242GapClose1,000,0002000000
Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Running Total

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.

Valued Guide
Posts: 854

Re: Running Total

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

421241GapClose,1000000

421241GapClose,1000000

421241GapClose,1000000

421241GapClose,1000000

421242GapClose,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;

Frequent Contributor
Posts: 138

Re: Running Total

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

Grand Advisor
Posts: 17,394

Re: Running Total

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

Valued Guide
Posts: 854

Re: Running Total

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;

Ask a Question
Discussion stats
  • 7 replies
  • 238 views
  • 0 likes
  • 4 in conversation