BookmarkSubscribeRSS Feed

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;

7 REPLIES 7
ballardw
Super User

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

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
ballardw
Super User

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)

Reeza
Super User

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;



dcruik
Lapis Lazuli | Level 10

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.

jjjon
Calcite | Level 5

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.

Patrick
Opal | Level 21

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 741 views
  • 0 likes
  • 6 in conversation