topic Re: Identifying Dates within Multiple Windows in SAS Programming

Identifying Dates within Multiple Windows

lm12abh — Fri, 30 Aug 2019 14:01:41 GMT

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

Re: Identifying Dates within Multiple Windows

tomrvincent — Fri, 30 Aug 2019 14:35:24 GMT

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.

Re: Identifying Dates within Multiple Windows

Kurt_Bremser — Fri, 30 Aug 2019 14:36:45 GMT

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;

Re: Identifying Dates within Multiple Windows

lm12abh — Fri, 30 Aug 2019 14:59:50 GMT

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

Re: Identifying Dates within Multiple Windows

lm12abh — Fri, 30 Aug 2019 15:03:53 GMT

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

Re: Identifying Dates within Multiple Windows

Astounding — Fri, 30 Aug 2019 15:10:25 GMT

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') ;

Re: Identifying Dates within Multiple Windows

tomrvincent — Fri, 30 Aug 2019 15:32:25 GMT

Simply use DISTINCT

Re: Identifying Dates within Multiple Windows

Kurt_Bremser — Fri, 30 Aug 2019 15:58:41 GMT

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

Re: Identifying Dates within Multiple Windows

ballardw — Fri, 30 Aug 2019 16:10:36 GMT

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

Re: Identifying Dates within Multiple Windows

lm12abh — Mon, 02 Sep 2019 07:06:37 GMT

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

Re: Identifying Dates within Multiple Windows

lm12abh — Mon, 02 Sep 2019 07:14:05 GMT

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

Re: Identifying Dates within Multiple Windows

lm12abh — Mon, 02 Sep 2019 08:25:30 GMT

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.

Re: Identifying Dates within Multiple Windows

lm12abh — Mon, 02 Sep 2019 08:26:54 GMT

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

Kind regards,

Laurence

Re: Identifying Dates within Multiple Windows

tomrvincent — Wed, 04 Sep 2019 12:08:23 GMT

Then they aren't really duplicates.