Group by with multiple conditions

[David_Billa]

 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 End_CR='no' and year_start='yes' otherwise I want '0'

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.

I tried with case when as below, but it didn't worked either as it didn't produce desired results. I'm looking for the solution in proc sql. Any leads?

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

[PaigeMiller]

Your text says you want End_CR='no', but this condition does not appear in the example of SQL code you show. 

[David_Billa]

@PaigeMiller sorry it's a typo error. I've corrected it in the initial post now.

[PaigeMiller]

Okay, so we would need to see the ENTIRE code for the PROC SQL step, not just the CASE statement. Also, you seem to refer to variable CR in your CASE statement, but the data set does not have a variable CR.

When you say "it didn't produce desired results", that simply is not specific enough. Show us the desired result. Show us the results you are getting.

[David_Billa]

I already mentioned the desired result in my initial post. Code which I tried is,

proc sql;
   create table want  as
   select * ,(case when (Contract='yes' and Start_CR='no' and End_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;

When I tried this code, I got the value as 0097 for CR_Ratio which is not correct.

[PaigeMiller]

So, first of all, it doesn't help if you provide data as SAS code with errors in it.

Secondly, your PROC SQL code does not work with the data you provided, as I said there is no variable named CR in your data. Nor is there a variable named SEGMENT or COMPANY, even though your SQL refers to them.

So you need to straighten all of this out, because I really can't even get to the incorrect output as things stand.

[David_Billa]

There is no error in the SAS code which creates data for calculation. I
already provided the condition to calculate the new variable and the
desired output.

[Shmuel]

Pay attention - the input contains a variable RC, the formula uses CR  - 

is this a typo?

[David_Billa]

Yes, it's a typo. You can take either 'RC' or 'CR' for explanation.

[PaigeMiller]

Here's the log when I run the code you have provided — your data step and your PROC SQL. It doesn't work. There is no output. There are syntax errors, data errors, as well as reference to unknown variables in PROC SQL. We do not get any results, we do not even get the incorrect results that you would like to fix. Please straighten all this out, this is something you can do and should do, it is not something we can do and it is not something we should do in this case.

3977  data have;
3978      input Branch $ Sub_Segment $ Currency $ Yearly_Premium Contract $ Start_CR $  End_CR $ Full_year_Start RC;
3979      datalines;

NOTE: Invalid data for Full_year_Start in line 3980 47-49.
RULE:       ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
3980        1831234 7183_AS1    EUR 2533629   yes no  no  yes 131169336
Branch=1831234 Sub_Segment=7183_AS1 Currency=EUR Yearly_Premium=2533629 Contract=yes Start_CR=no End_CR=no Full_year_Start=. RC=131169336 _ERROR_=1
_N_=1
NOTE: Invalid data for Full_year_Start in line 3981 47-49.
3981        1831234 7183_AS1    EUR 11643516  yes no  no  yes 131169336
Branch=1831234 Sub_Segment=7183_AS1 Currency=EUR Yearly_Premium=11643516 Contract=yes Start_CR=no End_CR=no Full_year_Start=. RC=131169336 _ERROR_=1
_N_=2
NOTE: The data set WORK.HAVE has 2 observations and 9 variables.
NOTE: Compressing data set WORK.HAVE increased size by 100.00 percent.
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


3982  ;
3983  run;
3984  proc sql;
3985     create table want  as
3986     select * ,(case when (Contract='yes' and Start_CR='no' and End_CR='no' and Full_year_start='yes')
3987                      then CR/sum(Yearly_Premium)
3988                      else 0)
                                -
                                22
                                200
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, END, EQ, EQT,
              GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 200-322: The symbol is not recognized and will be ignored.

3989                end) as CR_Ratio length = 8 format = 21.4
3990  from have
3991  group by
3992        Segment,
3993        Company
3994  ;
3995  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

[Shmuel]

I believe first step need be:

data have;
    input Branch $ Sub_Segment $ Currency $ Yearly_Premium 
    Contract $ Start_CR $  End_CR $ Full_year_Start $ CR;
                                                 *==|=== change done ==;
    datalines;
1831234 7183_AS1    EUR 2533629   yes no  no  yes 131169336
1831234 7183_AS1    EUR 11643516  yes no  no  yes 131169336
;
run;

there is a syntax error in the SQL formula - what did you mean by

    CR/sum(Yearly_Premium)  ?

[David_Billa]

'CR' value divided by sum of 'yearly_premium' value to get the target
variable.

[gulshy]

Remove the closing parentheses in the query at 'else 0' 

proc sql;
create table want as
select * ,(case when (Contract='yes' and Start_CR='no' and End_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
Sub_Segment,
Branch
;
quit;

regards,

gulshy