BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MinKhee
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

3 REPLIES 3
ballardw
Super User

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
Lapis Lazuli | Level 10

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
Super User Tom
Super User

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.  [cautionend]

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1486 views
  • 6 likes
  • 4 in conversation