topic Re: Extract using date between in SAS Programming

Extract using date between

Babloo — Sun, 29 Mar 2020 08:50:20 GMT

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.

Re: Extract using date between

PeterClemmensen — Sun, 29 Mar 2020 09:47:57 GMT

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

Re: Extract using date between

Babloo — Sun, 29 Mar 2020 09:08:51 GMT

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

Re: Extract using date between

PeterClemmensen — Sun, 29 Mar 2020 09:15:47 GMT

I don't see any example data?

Re: Extract using date between

Babloo — Sun, 29 Mar 2020 09:33:19 GMT

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

Re: Extract using date between

PeterClemmensen — Sun, 29 Mar 2020 09:48:33 GMT

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

Re: Extract using date between

Kurt_Bremser — Sun, 29 Mar 2020 14:27:39 GMT

@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.

Re: Extract using date between

Reeza — Sun, 29 Mar 2020 17:52:59 GMT

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-data-AKA-generate/ta-p/258712

Re: Extract using date between

Patrick — Mon, 30 Mar 2020 01:29:24 GMT

@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.