Identifying Dates within Multiple Windows

[lm12abh]

Hi,

How do i convert the following to a variable i that can be any value? I am trying to flag transactions that exist within x number of window.

	if transaction_date >=REDRESS_WDW_START_1 and transaction_date <= REDRESS_WDW_end_1 then trans_inscope_check='Y';
		else if transaction_date >=REDRESS_WDW_START_2 and transaction_date <= REDRESS_WDW_end_2 then trans_inscope_check='Y';
			else if transaction_date >=REDRESS_WDW_START_3 and transaction_date <= REDRESS_WDW_end_3 then trans_inscope_check='Y';
				else if transaction_date >=REDRESS_WDW_START_4 and transaction_date <= REDRESS_WDW_end_4 then trans_inscope_check='Y';
					else if transaction_date >=REDRESS_WDW_START_5 and transaction_date <= REDRESS_WDW_end_5 then trans_inscope_check='Y';

Thanks,

Laurence

[tomrvincent]

Put your date pairs in a separate table and then join to it with either a between or your '>= and <=' logic. You'll then get all windows the date falls between.

[lm12abh]

The issue with this is if a transaction falls within multiple windows it will create duplicates

[tomrvincent]

Simply use DISTINCT

[lm12abh]

You then lose the information of which window it falls under. Thanks for your help though, the other solution is the one im going to try.

Kind regards,

Laurence

[tomrvincent]

Then they aren't really duplicates.

[Kurt_Bremser · Accepted solution]

Simplify your code by using arrays:

array start {5} redress_wdw_start1-redress_wdw_start5;
array end {5} redress_wdw_end1-redress_wdw_end5;
trans_inscope_check = "N";
do i = 1 to dim(start);
  if start{i} le transaction_date le end{i} then trans_inscope_check = "Y";
end;

[lm12abh]

Thanks. This is closer but it still isn't right. will it cause issue if accounts have a different number of windows?

[Astounding]

That's not possible.  All accounts must have the same number of windows.  In a SAS data set, all variables are part of all observations.

It is possible that there are missing values for some start or end dates, on some observations.  To avoid extra processing, you can switch to:

do i = 1 to dim(start) until (trans_inscope_check='Y') ;

[Kurt_Bremser]

---

@lm12abh wrote:

Thanks. This is closer but it still isn't right. will it cause issue if accounts have a different number of windows?

---

Define what "isn't right". If you want specific help, supply usable data (data step with datalines) and what you expect as a result.

[lm12abh]

Apologies I didn't mean to come across rude. It must be due to the missing data for some windows.

Example data

acc     trand date           wdw start 1          wdw end 1       wdw start 2        wdw end 2        wdw start 3        wdw end 3

123      01mar2016       01jan2015           01aug2016       01jan2017         01feb2017        01dec2017         04apr2019

123      01mar2017       01jan2015           01aug2016       01jan2017         01feb2017        01dec2017         04apr2019

321      01mar2017       01mar2017         01dec2018        .                         .                        .                          .

321      01jan 2019       01mar2017         01dec2018

[lm12abh]

All fixed now if was because of some windows that are ., needed to replace the missing value. Thanks for your help.

Kind regards,

Laurence

[ballardw]

---

@lm12abh wrote:

Hi,

How do i convert the following to a variable i that can be any value? I am trying to flag transactions that exist within x number of window.

	if transaction_date >=REDRESS_WDW_START_1 and transaction_date <= REDRESS_WDW_end_1 then trans_inscope_check='Y';
		else if transaction_date >=REDRESS_WDW_START_2 and transaction_date <= REDRESS_WDW_end_2 then trans_inscope_check='Y';
			else if transaction_date >=REDRESS_WDW_START_3 and transaction_date <= REDRESS_WDW_end_3 then trans_inscope_check='Y';
				else if transaction_date >=REDRESS_WDW_START_4 and transaction_date <= REDRESS_WDW_end_4 then trans_inscope_check='Y';
					else if transaction_date >=REDRESS_WDW_START_5 and transaction_date <= REDRESS_WDW_end_5 then trans_inscope_check='Y';

Thanks,

Laurence

---

Does your code do what you expect? Your requirement of "exist within x number of window" sounds like you need to count something but since the above code assigns values to the same variable you only have one value. I think that you might be intending to create five variables such as trans_inscope_check1 to trans_inscope_check5 to check.

Note that instead of 'Y' it is much easier in most cases to deal with a numeric variable coded as 1 for true and 0 for false.

Something like this perhaps (though arrays for this would be cleaner)

trans_inscope_check1 =  (REDRESS_WDW_START_1   <= transaction_date <= REDRESS_WDW_end_1);
trans_inscope_check2 =  (REDRESS_WDW_START_2   <= transaction_date <= REDRESS_WDW_end_2);
trans_inscope_check3 =  (REDRESS_WDW_START_3   <= transaction_date <= REDRESS_WDW_end_3);
trans_inscope_check4 =  (REDRESS_WDW_START_4   <= transaction_date <= REDRESS_WDW_end_4);
trans_inscope_check5 =  (REDRESS_WDW_START_5   <= transaction_date <= REDRESS_WDW_end_5);

check_count= sum( of trans_inscope_check:);

SAS will return a numeric 1 for "true" in the comparison and 0 for false.

You can get the counts of true (or yes) by summing the variable, mean will give percentage of yes.

[lm12abh]

Thanks, I thought about using the Boolean but for some reason I used a flag of 'Y' instead. Probably not best practice. Thanks for your help.