topic Re: Case when multiple conditions to create aggregated value in SAS Programming

Case when multiple conditions to create aggregated value

David_Billa — Fri, 21 Aug 2020 09:08:32 GMT

I would like to understand whether code below logically is right. Because it is not producing the desired results. I want to create a variable 'CR_Ratio' which is equal to (CR/sum(Yearly_Premium) when it statisfies the condition

Contract='yes' and Start_CR='no' and year_start='yes' otherwise I want '0'

proc sql;
   create table want  as
   select * ,(case when (Contract='yes' and Start_CR='no' and Full_year_start='yes')
            then (CR/sum(Yearly_Premium
            else 0)) 
            end) as CR_Ratio length = 8
            format = 21.4
from have
group by
      Segment,
      Company
;
quit;

what are other ways to tackle it apart from case when?

Re: Case when multiple conditions to create aggregated value

Kurt_Bremser — Fri, 21 Aug 2020 09:14:22 GMT

Please supply an example for have, point out where the result does not meet your expectations, and show what you expected.

Re: Case when multiple conditions to create aggregated value

David_Billa — Fri, 21 Aug 2020 09:24:21 GMT

Before I provide the sample data, can I understand that the provided
program matches with the conditions in ' case when ' to create new
variable?

Re: Case when multiple conditions to create aggregated value

Satish_Parida — Fri, 21 Aug 2020 10:16:36 GMT

@David_Billa You misplaced few parenthesis. Here is the code you need.

proc sql;
   create table want  as
   select * ,(case when (Contract='yes' and Start_CR='no' and Full_year_start='yes') 
					then CR/sum(Yearly_Premium)   
					else 0)      
			  end) as CR_Ratio length = 8 format = 21.4
from have
group by
      Segment,
      Company
;
quit;

Re: Case when multiple conditions to create aggregated value

Satish_Parida — Fri, 21 Aug 2020 10:52:09 GMT

@David_Billa If the issue is resolved please close the issue.
If not please provide us some test data.

Re: Case when multiple conditions to create aggregated value

David_Billa — Fri, 21 Aug 2020 10:57:29 GMT

@Satish_Parida @Kurt_Bremser I don't think I've misplaced any paranthesis in my post. Code works fine. I tried with your proposed code also but still the data is not matching to my desired results.

Sample data:

data have;
    input Branch $ Sub_Segment $ Currency $ Yearly_Premium Contract $ Start_CR $  End_CR $ Full_year_Start RC;
    datalines;
1831234 7183_AS1    EUR 2533629   yes no  no  yes 131169336
1831234 7183_AS1    EUR 11643516  yes no  no  yes 131169336
;
run;

Desired result is, I want all the rows and observations from the dataset 'have' plus one more variable 'CR_ratio' and it should have value as 9.2521 in the matching rows. I want to display 0 if the condition is not statisfied.

Condition to claculate new variable is,

(case
    when (Contract='yes' and Start_CR='no' and Full_year_start='yes') then CR/sum(Yearly_Premium) 
    else 0)

I'm looking for the solution in proc sql

Re: Case when multiple conditions to create aggregated value

ballardw — Fri, 21 Aug 2020 15:13:55 GMT

@David_Billa wrote:

@Satish_Parida @Kurt_Bremser I don't think I've misplaced any paranthesis in my post.

I'm looking for the solution in proc sql

Considering that the original post "code"

proc sql;
   create table want  as
   select * ,(case when (Contract='yes' and Start_CR='no' and Full_year_start='yes')
            then (CR/sum(Yearly_Premium
            else 0)) 
            end) as CR_Ratio length = 8
            format = 21.4
from have
group by
      Segment,
      Company
;
quit;

Has the closing ) for both the "(CR/ " and "sum(Yearly_premium" after "else 0" very clearly, you did post code with bad parentheses. Which is one reason you will see a lot of requests to post code from the LOG in this forum. Then there is no question as to what was actually submitted.