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 !
If you are seeing dates of 01JAN1960 then the value is ZERO and not missing (or NULL as SQL would call it).
If you are seeing dates of 01JAN1960 then the value is ZERO and not missing (or NULL as SQL would call it).
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?
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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.