Proc sql : replace the null value in case statement

[SASdevAnneMarie]

Hello Experts,

I would like to replace the . value using the case statement, but it doesn't work, I have the 01/01/1960 in the place of . value.

My code is :

proc sql;
	create table Donnees as 
   select  c.cd_rga
         , b.is_support
         , c.lb_court
         , b.d_fin
         , a.mt_ea,
	      , sum(a.mt_ea) as total	
	from SUPPORT as a

   left join (select is_support,
    case d_fin when . then d_fin="31DEC2023"d else d_fin
             end as d_fin format ddmmyy10.
              from RGA
              group by is_support
              having 
              or max(b.d_fin) = b.d_fin)  as b
   on a.is_support = b.is_support

	left join RGPC as c 
   on b.is_rga=c.is_rga

	where a.s_type_support="OK"
	group by c.cd_rga
	;
quit;

Thank you for your help !

[Tom · Accepted solution]

If you are seeing dates of 01JAN1960 then the value is ZERO and not missing (or NULL as SQL would call it).

[SASdevAnneMarie]

Hi Tom,
Sorry, I accepted the answer but the code still not work. When I put in « when 0 »
I don’t have 01/01/1960 any more, I obtain only the points. I suppose that my case statement doesn’t work.

[Tom]

You should be able to debug yourself by executing the different parts in simpler queries.

Here are two obvious things:

case d_fin
  when . then d_fin="31DEC2023"d 
  else d_fin
end as d_fin format ddmmyy10.

Will set the calculated D_FIN variable ZERO when it is missing because MISSING does not equal the last day of 2023 and SAS returns a ZERO when a boolean expression like your equality test is FALSE.

This HAVING clause looks suspect.  

having or max(b.d_fin) = b.d_fin

OR is binary operator, it needs TWO arguments and you have only given it one.

What did you intend here?

[SASdevAnneMarie]

I see : then d_fin="31DEC2023"d is not correct, I must write then "31DEC2023"d and it's work.

[Tom]

Or just use the SQL function COALESCE() instead of CASE construct. 

This is exactly the type of situation that function is designed for.

coalesce(d_fin,"31DEC2023"d)