Missing ELSE statement?

[bhca60]

I'm doing a case when but I'm getting a NOTE that Case has no ELSE clause.  Should I be using "AND" instead of "THEN" in certain places? Do I have too many END statements?  Any help is appreciated.

92    proc sql;
93         create table work.short2 as
94            select a.*,
95    case when missing(a.date)then .
96         when a.date<="05may2024"d then
97              case when a.m_score >= 0.13 then 1 else 0 end
98    else case when a.date >="06may2024"d then case when
99    a.Days >= 20 then 1 else 0 end
100   end
101   end as T_Flag
102              from short a
103                 left join b.rti b
104                    on a.id= b.id and
105                       a.sflag = b.sflag;
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE 
      expression.

[quickbluefish]

I suspect you could do something much simpler than this. I think it would be helpful if you first wrote out what you want in plain English (i.e., not code) - better yet, draw it (like a tree diagram).   I have literally never seen that degree of nesting / daisy-chaining with CASE statements.  

[yabwon]

/* fake data */
libname b (work);
data short b.rti(keep=id sflag);
  id = 1;
  sflag = 2;
  date = "06may2024"d;
  days = 42;
  m_score = 0.16;
run;


proc sql;
create table work.short2 as
select a.*,

case when missing(a.date) then .
     when a.date<="05may2024"d then
          case when a.m_score >= 0.13 then 1 else 0 end

     else 
          case when a.date >="06may2024"d then 
               case when a.Days >= 20 then 1 
                                      else 0 
               end
                                          ELSE .  /* missing ELSE */
          end
end 
as T_Flag

from short a
  left join 
     b.rti b
  on a.id = b.id 
  and a.sflag = b.sflag
;
quit;

[SASKiwi]

I don't think nesting CASE expressions is what you intended as it doesn't make a lot of sense. Maybe something more like this?

   case 
      when missing(a.date) then .
      when a.date<="05may2024"d and a.m_score >= 0.13 then 1
      when a.date<="05may2024"d and a.m_score <  0.13 then 0  
      when a.date >="06may2024"d and a.Days >= 20 then 1
      when a.date >="06may2024"d and a.Days < 20 then 0 
     else 0
   end as T_Flag

[Kurt_Bremser · Accepted solution]

Proper code formatting will quickly reveal where the ELSE is missing:

case
  when missing(a.date)
  then .
  when a.date <= "05may2024"d
  then case
    when a.m_score >= 0.13
    then 1
    else 0
  end
  else case /* CASE starts here */
    when a.date >= "06may2024"d
    then case 
      when a.Days >= 20
      then 1
      else 0
    end
  end /* case ends here, but no ELSE after WHEN/THEN */
end as T_Flag

Writing spaghetti code is always bad and makes code unmaintainable. DO NOT DO IT.

[bhca60]

Thank you much!!!