🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Fluorite | Level 6

## Identifying Dates within Multiple Windows

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Identifying Dates within Multiple Windows

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;``````
13 REPLIES 13
Rhodochrosite | Level 12

## Re: Identifying Dates within Multiple Windows

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.
Fluorite | Level 6

## Re: Identifying Dates within Multiple Windows

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

Rhodochrosite | Level 12

## Re: Identifying Dates within Multiple Windows

Simply use DISTINCT
Fluorite | Level 6

## Re: Identifying Dates within Multiple Windows

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

Rhodochrosite | Level 12

## Re: Identifying Dates within Multiple Windows

Then they aren't really duplicates.
Super User

## Re: Identifying Dates within Multiple Windows

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;``````
Fluorite | Level 6

## Re: Identifying Dates within Multiple Windows

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

PROC Star

## Re: Identifying Dates within Multiple Windows

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') ;``
Super User

## Re: Identifying Dates within Multiple Windows

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

Fluorite | Level 6

## Re: Identifying Dates within Multiple Windows

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

Fluorite | Level 6

## Re: Identifying Dates within Multiple Windows

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

Kind regards,

Laurence

Super User

## Re: Identifying Dates within Multiple Windows

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

Fluorite | Level 6

## Re: Identifying Dates within Multiple Windows

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.

Discussion stats
• 13 replies
• 1173 views
• 2 likes
• 5 in conversation