BookmarkSubscribeRSS Feed
DaveD
Calcite | Level 5

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

4 REPLIES 4
art297
Opal | Level 21

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.


DaveD
Calcite | Level 5

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.

art297
Opal | Level 21

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.

DaveD
Calcite | Level 5

You are absolutely correct.  The ordre of the statement is critical.  Thank you for pointing that out.

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!

SAS Enterprise Guide vs. SAS Studio

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.

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
  • 4 replies
  • 1389 views
  • 6 likes
  • 2 in conversation