topic Problem with case when in sql in SAS Programming

Problem with case when in sql

MinKhee — Tue, 18 Aug 2015 09:11:21 GMT

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".

Re: Problem with case when in sql

ballardw — Tue, 18 Aug 2015 15:19:20 GMT

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.

Re: Problem with case when in sql

slchen — Tue, 18 Aug 2015 15:58:54 GMT

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;

Re: Problem with case when in sql

Tom — Tue, 18 Aug 2015 17:18:34 GMT

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.