Problem with case when in sql

[MinKhee]

Hi, I am wondering why this code does not working, hope u all figure out the reason

create table data_tt1_pct_case as

  select *,

  case (select ifa_pct from data_tt1_pct_case)

  when between (10) and (30) then  'Qutlier quarter'

  when ge (31) and le (50) then 'Average quarter'

  when ge (51) and le (80) then 'Good quarter'

  when ge (81) and le (100) then 'Poor quarter'

  else 'not in range'

  end as Case_pct

  from data_tt1_pct;

quit;

ERROR: Function BETWEEN could not be located.

ERROR: Function GE could not be located.

ERROR: Function LE could not be located.

ERROR: Function GE could not be located.

ERROR: Function LE could not be located.

ERROR: Function GE could not be located.

ERROR: Function LE could not be located.

if I change the command as follow:

create table data_tt1_pct as

  select QuarterCode, Total label='Total Premiums', ifa as ifa_premium label="IFA Premium",

  abs(ifa/Total) as ifa_pct format= percent8.1 label='Percentages for IFA'

  from data_tt1_diff;

  create table data_tt1_pct_case as

  select *,

  case

  when ifa_pct between 10 and 30 then 'Qutlier quarter'

  when ifa_pct between 31 and 50 then 'Average quarter'

  when ifa_pct between 51 and 80 then 'Good quarter'

  when ifa_pct between 81 and 100 then 'Poor quarter'

  else 'not in range'

  end as Case_pct

  from data_tt1_pct;

quit;

the word between is black, thus I am not sure it is recognised as keyword or not, and there is no error, but the output of Case_PCT is all stated "not in range".

[ballardw · Accepted solution]

Can you show some of the values for ifa_pct in table data_ttl_pct? I suspect that you see 10.1% for example but the format Percent8.1 is displaying an actual value of .101 so it is less than 10. Basically I suspect that if you change ranges to 0.1 and 0.3 , 0.31 and 0.5 you'll get more expected results.

But since neither of those will yield expected results for a value of 0.3002 you might consider either rounding your values or possibly a custom format.

[slchen]

There is a little tricky on case when in proc sql, when it only use equal value, it ok with case  var=  when, or case when var=, but if there is compare or between, it must be used case when var bwtween...  and.... Here are examples:

proc sql;

  select *,  case

             when age<=13 then 1

             when age between 14 and 15 then 2

             else 3

             end as group from sashelp.class;

  select *, case sex

            when 'M' then 1

            else  2

            end as gender from sashelp.class;

  select *, case

            when sex='M' then 1

            else  2

            end as gender from sashelp.class;

quit;

There are not any problem those expression, but it is wrong for this:

proc sql;

  select *,  case age

             when <=13 then 1

             when between 14 and 15 then 2

             else 3

             end as group from sashelp.class;

quit;

[Tom]

This is a documented limitation of the shorthand form of the CASE clause.

Note:   When you use the shorthand method, the conditions must all be equality tests. That is, they cannot use comparison operators or other types of operators.