Extract using date between

[Babloo]

I would like to know how to filter the records based on two different dates. Assume I've a variables called Valid_from and it has  a value 20200323T000000 and other variable called Valid_to and it has a value 99991231T235959 and also other few variables. Formats and informats for Valid_from and Valid_to is B8601DT.

Now I want to select the records only if the current datetime is between  Valid_from and Valid_to.

May be something like below but I'm not sure.

if VALID_FROM <= DATETIME() <= VALID_TO;

I do not have SAS EG at the Moment. So appericiate if someone confirm if the above condition works, otherwise please tell me the correct one. 

[PeterClemmensen]

I can confirm that 🙂

data test;
   VALID_FROM = '23mar2020:00:00:00'dt;
   VALID_TO   = '31dec9999:23:59:59'dt;
   if VALID_FROM <= DATETIME() <= VALID_TO then put 'It works'; 
   format VALID_FROM VALID_TO B8601DT.;
run;

Output:

It works

[Babloo]

Is it possible for you to show me with my example data and the format which I applied and then applying the filters?

[PeterClemmensen]

I don't see any example data?

[Babloo]

I gave the example values and the format I used in the initial post

[PeterClemmensen]

Please look at my code again. I use the variables, values and format exactly as you describe.

[Patrick]

@Babloo 

As you might know the format applied to a variable doesn't change its internal value so the format is of no relevance here.

Furthermore the variable names indicate that you're dealing with an SCD Type2 structure likely loaded using the DIS SCD Type 2 transformation. If using Datetime() in the selection then you're only after the current record and if so then below two clauses would also work as an alternative:

1. where change_current_ind='Y' 

2. datetime() < valid_to_dttm

The 2nd option will work because the SCD Type 2 loader will expire all not current rows based on datetime() when loading; and all current rows will have a valid_to_date somewhere far in the future. 

[Kurt_Bremser]

---

@Babloo wrote:

Is it possible for you to show me with my example data and the format which I applied and then applying the filters?

---

You did not show example data, only example values. Example data = a data step that creates a dataset.

[Reeza]

Here are instructions on how to provide sample data as a data step:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...