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

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
Kurt_Bremser
Super User

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;

View solution in original post

13 REPLIES 13
tomrvincent
Rhodochrosite | Level 12
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
Fluorite | Level 6

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

tomrvincent
Rhodochrosite | Level 12
Simply use DISTINCT
lm12abh
Fluorite | Level 6

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
Rhodochrosite | Level 12
Then they aren't really duplicates.
Kurt_Bremser
Super User

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

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

Astounding
PROC Star

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
Super User

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

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

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
Super User

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

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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