topic Re: SUM value all the same in SAS Programming

SUM value all the same

anonymous_user — Mon, 13 Jul 2015 15:27:09 GMT

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;

Re: SUM value all the same

ballardw — Mon, 13 Jul 2015 15:36:48 GMT

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

Re: SUM value all the same

Reeza — Mon, 13 Jul 2015 15:47:17 GMT

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;

Re: SUM value all the same

anonymous_user — Mon, 13 Jul 2015 15:48:04 GMT

pipeline_x_alco_pc	completions_ytd	prepayment	LTD	LTD - 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

Re: SUM value all the same

dcruik — Mon, 13 Jul 2015 15:53:41 GMT

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.

Re: SUM value all the same

ballardw — Mon, 13 Jul 2015 15:59:31 GMT

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)

Re: SUM value all the same

jjjon — Tue, 14 Jul 2015 09:05:32 GMT

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.

Re: SUM value all the same

Patrick — Tue, 14 Jul 2015 10:32:17 GMT

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;

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;