Sum and Case Statement Help

Reply
Frequent Contributor
Posts: 138

Sum and Case Statement Help

Hi,

I have the following code but I cant get the logic to work.

The variables names are correct and I think the code I have produce should show what im trying to achieve. If anyone can interpret that I would be grateful. I thin the first 2 are correct and maybe the 4th one but not 100%

case when Date_Term_Prod = Key_hedge then Conversion_Rate_hedge else 0 end as Alco_pc,

Case when Product_Term_comps = Term_hedge_2 then Prepayment_rate_hedge_2 else 0 end as prepayment,

case when alco_pc = 0 then sum(Pipeline_ytd, Pipeline_ytd*alco_pc) end as pipeline_x_alco_pc,

sum(case when pipeline_x_alco_pc + Completions_ytd * prepayment) as LTD,

case when Product_end_date = 0 then sum(Weekly_movement_Allocs * prepayment) else sum(Weekly_movement_Allocs * Alco_pc * prepayment) as weekly_movement

Respected Advisor
Posts: 4,609

Re: Sum and Case Statement Help

Every expression seems syntactically correct, except the fourth one which contains an incomplete CASE expression.

Are you aware that there are two kinds of SUM functions in your query? SUM(x, y) simply returns x + y, whereas SUM(x*y), ...  GROUP BY a returns the sum of x*y over every value of a. The first one is a SAS datastep function and the second one is a SQL aggregate function.

PG

PG
Frequent Contributor
Posts: 138

Re: Sum and Case Statement Help

No I wasn't aware of that, thank you.

Grand Advisor
Posts: 17,464

Re: Sum and Case Statement Help

There's no way to say what's 'correct' without having sample input/output data. They may syntactically correct, but be logically incorrect.

This appears incorrect to me, you'd at least need an END.

sum(case when pipeline_x_alco_pc + Completions_ytd * prepayment) as LTD,

Ask a Question
Discussion stats
  • 3 replies
  • 191 views
  • 0 likes
  • 3 in conversation