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 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?
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)
So something like
(open between &date1 and &date2)
or (close between &date1 and &date2)
or (missing(close) and open > &date3)
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)
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).
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: