Why is it not returning null values

[Coding4you]

Hi All,

Please see the below codes. I’m trying to extract the data between those dates and that if there is a null value in both closing and open date. Is there a way to specify that in the below codes?
Thanks

Proc sql;
Create or replace table Cais as
Select sourcecode, opendate, closingdate from Expin where
sourcecode in (‘125’, ‘154’, ‘264’) and opendate >= ‘2021-03-01’ and closingdate <= ‘2023-02-07’ and closingdate is null ;
Quit;

I

[PaigeMiller]

Your variables OPENDATE and CLOSINGDATE must be character strings for this to work. Furthermore CLOSINGDATE cannot satisfy both conditions

closingdate <= '2023-02-07' and closingdate is null

unless closingdate is null, I'm sure that's not what you want.

Maybe (maybe maybe maybe) you mean this (in which case opendate and closingdate must be numeric):

and opendate >= '03JAN2021'd and closingdate <= '07FEB2023'd

but since you didn't really explain what you want, that's a big guess, and there's no way to incorporate the idea that closingdate is null into this logic. Can you give examples of what this is supposed to do, when it is supposed to succeed and when it is supposed to fail?

Lastly, you have "curly" quotes in your code, that will never work.

[Coding4you]

Thank you for your speedy response!

Sorry for the confusion.

What I was trying to do was extract all the source code that was between those dates. However, if I ran the code where opendate >= '03JAN2021'and closingdate <= '07FEB2023’ it will only provide the dates that are within those specific dates, which is correct. What I wanted to include was that when there are records where there are null values in the closing date but not the open date, and vice versa within the same step, which won't work as it wouldn't be able to satisfy both conditions as mentioned below.

i was thinking whether there's a quick way to get all that information in a few steps from the same table.



i hope they

[Tom]

---

@Coding4you wrote:
Thank you for your speedy response!

Sorry for the confusion.

What I was trying to do was extract all the source code that was between those dates. However, if I ran the code where opendate >= '03JAN2021'and closingdate <= '07FEB2023’ it will only provide the dates that are within those specific dates, which is correct. What I wanted to include was that when there are records where there are null values in the closing date but not the open date, and vice versa within the same step, which won't work as it wouldn't be able to satisfy both conditions as mentioned below.

i was thinking whether there's a quick way to get all that information in a few steps from the same table.



i hope they

---

Are you looking to see if the two intervals, (OPENINGDATE to CLOSINGDATE) and ('03JAN2021'd to '07FEB2023'd) overlap at all?

Or are you trying to see if one is totally contained in the other?

[Tom]

Take a piece of paper and draw a line with endpoints A and B.  Now under it draw more lines with end points labeled C and D.  Draw one where C to D is totally inside of A to B.  Draw one where it is totally before, another with it after.  Then draw one where only C is between A and B and one where only D is between A and B.  

Period              A-----------------B
Inside                 C-----D
Before        C--D
After                                       C---D
left          C----------D
right                             C----------D

So which of these do you want to detect?

Also what do want to do when either of the two dataset variables are missing?  

To select only the INSIDE example then use

(C between A and B) AND (D between A and B)

To select any overlap use

(C between A and B) OR (D between A and B)

[Coding4you]

Yes I’m trying to extract anything that fall between those dates and if
closing date is null when open date >= ‘2021-03-31’ if that make sense.

[Tom]

So something like

   (open between &date1 and &date2) 
or (close between &date1 and &date2)
or (missing(close) and open > &date3)

[Tom]

So before you said:

where
sourcecode in ('125', '154', '264') and opendate >= ‘2021-03-01’ and closingdate <= ‘2023-02-07’  /* and closingdate is null */ 

If you are doing this in SAS then the syntax would be more like:

where sourcecode in ('125', '154', '264')
  and opendate >=  '01MAR2021'd
  and closingdate <= '07FEB2023'd

Since SAS treats missing values as less than any valid number then "null" values of CLOSINGDATE would be included in that last AND.

If for some reason you had to push that logic into some external database that uses TRI level logic instead of normal BINARY logic you could add that extra condition this way.

where sourcecode in ('125', '154', '264')
  and opendate >=  '01MAR2021'd
  and (closingdate <= '07FEB2023'd or closingdate is null)

[PaigeMiller]

However, if I ran the code where opendate >= '03JAN2021'and closingdate <= '07FEB2023’ it will only provide the dates that are within those specific dates, which is correct. What I wanted to include was that when there are records where there are null values in the closing date but not the open date

How about this: 

where (opendate>='03JAN2021'd and closingdate<='07FEB2023'd) OR (opendate>='03JAN202'd and closingdate is null)

but even this logic fails because a closing date of '02JAN2021'd would pass.

Please take some time and carefully and thoroughly write out the proper IF statements that will work for you (you can do this in plain English, the IF statements don't have to be in SAS).