SUM value all the same

Reply
Frequent Contributor
Posts: 138

SUM value all the same

Hi,

I have the code below which is trying to do a calculation, however I get the same value for each row, I have tried order and group by but same issue, any ideas pleasee?

proc sql;

create table steve_3 as

select

key_hedge_allocs,

pipeline_ytd,

completions_ytd,

Weekly_Movement_Allocs,

product_end_date_allocs,

Prepayment_rate_hedge_2,

Term_hedge_2,

Product_Term_comps,

alco_pc,

pipeline_x_alco_pc,

prepayment,

sum(pipeline_x_alco_pc + Completions_ytd * prepayment) as LTD

from

steve_2;

quit;

Grand Advisor
Posts: 10,210

Re: SUM value all the same

It really helps to provide some input data and the matching output you want.

Frequent Contributor
Posts: 138

Re: SUM value all the same

pipeline_x_alco_pccompletions_ytdprepaymentLTDLTD - should be
£               244,175.00 £  187,875,824.00 0 £  956,199,765.00 £  188,119,999.00
£               799,015.00 0 £             0.86 £  956,199,765.00 £          799,015.86
Grand Advisor
Posts: 10,210

Re: SUM value all the same

With a value of 0 for prepayment you can't get the desired result for LTD with any multiplication and the second line looks like you actually want

pipeline_x_alco_pc + Completions_ytd + prepayment

or sum (pipeline_x_alco_pc , Completions_ytd , prepayment)

Grand Advisor
Posts: 17,332

Re: SUM value all the same

You need a group statement, but without know what you're calculating, and how you're aggregating your data it's hard to say. 

proc sql;

create table steve_3 as

select

key_hedge_allocs,

pipeline_ytd,

completions_ytd,

Weekly_Movement_Allocs,

product_end_date_allocs,

Prepayment_rate_hedge_2,

Term_hedge_2,

Product_Term_comps,

alco_pc,

pipeline_x_alco_pc,

prepayment,

sum(pipeline_x_alco_pc + Completions_ytd * prepayment) as LTD

from

steve_2

group by key_hedge_allocs;

quit;



Frequent Contributor
Posts: 128

Re: SUM value all the same

Not knowing what the field values are in the data "steve_2", I would assume that you're going to want to have a group by clause.  If you use the formula you have that you're calling "LTD", it will remerge the LTD values of the entire data set back with the original data, so you will have the exact same value for each of your observations.  Using the Group By clause on every variable you want to calcuate "LTD" by, will prevent it from calculating one value on the entire data set and all observations and won't require a remerge, instead it will calculate the values of "LTD" for each of the group by values.  Below is a basic example of the difference of using group by vs not.

data have;

input key_hedge_allocs $ completions_ytd pipeline_x_alco_pc prepayment;

datalines;

A 50 4 2000

B 40 6 1300

C 45 5 1700

;

run;

proc sql;

create table want1 as

select key_hedge_allocs, sum(pipeline_x_alco_pc + Completions_ytd * prepayment) as LTD

from have;

quit;

proc sql;

create table want2 as

select Key_hedge_allocs, sum(pipeline_x_alco_pc + Completions_ytd * prepayment) as LTD

from have

group by Key_hedge_allocs

order by Key_Hedge_allocs;

quit;

Hope this helps to give you an idea of what to use in the group by from all those fields being selected.  Like ballardw said though, it would be better to have a basic understanding of the field values for those variables.

Occasional Contributor
Posts: 5

Re: SUM value all the same

You need to group your data when using a sum statement, on what level do you want the calculations do be done?

Further, I think you need to rethink your LTD logic if the wanted results really are as you wrote them.

Respected Advisor
Posts: 3,831

Re: SUM value all the same

I believe a data step would be much more efficient and easier to understand in this case. Else as already said by others: you need a "Group By".

data have;

  do key1=1 to 5;

    do key2=1 to 5;

      var1+1;

      var2=1;

      output;

    end;

  end;

  stop;

run;

proc sql;

  create table not_wanted as

    select key1, key2, sum(var1+var2) as my_sum

    from have

    ;

quit;

data want2;

  set have;

  my_sum=sum(var1+var2);

run;

proc sql;

  create table want3 as

    select key1, key2, sum(var1+var2) as my_sum

    from have

    group by key1, key2

    ;

quit;

Ask a Question
Discussion stats
  • 7 replies
  • 272 views
  • 0 likes
  • 6 in conversation