Can anyone show me what I am doing wrong with this computed column case function?
CASE t1.event_date
WHEN (DATDIF(t1.event_date, DATE(), 'act/act')) GT 383 THEN 60
WHEN (DATDIF(t1.event_date, DATE(), 'act/act')) GT 383 AND (DATDIF(t1.event_date, DATE(), 'act/act')) LE 748 THEN 40
ELSE 0
END
It runs, but only returns 0. When I do the DATDIF function in it's own computed column, I get the correct numerical return.
Thanks
I would think that your first when statement would override your second statement (i.e., to meet the second statement the first statement has to be true, thus the second statement never gets to be considered.
Second, don't know if it would make a difference, but you have one redundant set of parentheses in each when statement. the following would suffice:
WHEN DATDIF(t1.event_date, DATE(), 'act/act') GT 383 THEN 60
Also, you don't have to indicate t1.event_date in the case statement.
I would test the calculations by changing the select statement to:
select t1.event_date, DATDIF(t1.event_date, DATE(), 'act/act') as datedif,
CASE
WHEN DATDIF(t1.event_date, DATE(), 'act/act') GT 383 AND DATDIF(t1.event_date, DATE(), 'act/act') LE 748 THEN 40
WHEN DATDIF(t1.event_date, DATE(), 'act/act') GT 383 THEN 60
ELSE 0
END
as whatever
and see what your calculations/comparisons are really doing.
Actually, less is better:
David Dillard wrote:
Can anyone show me what I am doing wrong with this computed column case function?
CASE t1.event_date
WHEN (DATDIF(t1.event_date, DATE(), 'act/act')) GT 383 THEN 60
WHEN (DATDIF(t1.event_date, DATE(), 'act/act')) GT 383 AND (DATDIF(t1.event_date, DATE(), 'act/act')) LE 748 THEN 40
ELSE 0
END
It runs, but only returns 0. When I do the DATDIF function in it's own computed column, I get the correct numerical return.
Thanks
What works is:
CASE
WHEN DATDIF(t1.event_date, DATE(), 'act/act') GT 383 THEN 60
WHEN DATDIF(t1.event_date, DATE(), 'act/act') GT 383 AND DATDIF(t1.event_date, DATE(), 'act/act') LE 784 THEN 40
ELSE 0
END
However, I am now trying to add a prompt to the WHEN statement and can not get it to recognize the prompt. I have associated the prompt with the query, but when I add it to the WHEN line (WHEN DATDIF(t1.event_date, DATE(), 'act/act') GT 383 + &Prompt_offset GT 383 THEN 60), it flags the &.
Any help is greatly appreciated.
I can't answer your question about the prompt, but still advise that you change the order of your when statements. Since the first must be correct before the second is addressed, the second one will never be recognized.
You are absolutely correct. The ordre of the statement is critical. Thank you for pointing that out.
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.