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;
It really helps to provide some input data and the matching output you want.
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 |
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)
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;
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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.