topic How to: Determine if YEAR in Table 1 is within the time range [START END] in Table 2 in SAS Programming

How to: Determine if YEAR in Table 1 is within the time range [START END] in Table 2

Alice_SAS — Mon, 09 Oct 2023 11:04:50 GMT

I currently have two tables have1 and have2, and I want to determine if the YEAR corresponding to each NAME in have2 is within the time range of have1 [START END]. If marked as 1, otherwise it is 0.

I hope to match two tables by CODE and obtain the table Want.

Thank you for your help!

Here is the data:

data have1;
input start: YYMMDD8. end: YYMMDD8. name:$1. code;
format start YYMMDDn8. end YYMMDDn8.;
datalines;
20050712 20060531 A 83265
20100624 20130812 B 79080
20040526 20051109 C 35423
20140401 20160418 D 69105
20170419 20180424 E 53272
;
run;

data have2;
input year name:$1. code;
datalines;
2011 A 83265
2012 A 83265
2007 B 79080
2008 B 79080
2009 B 79080
2010 B 79080
2012 D 69105
2013 D 69105
2014 D 69105
2015 D 69105
2016 D 69105
;
run;

I want to obtain:

data want;
input year name:$1. code exist;
datalines;
2011 A 83265 0
2012 A 83265 0
2007 B 79080 0
2008 B 79080 0
2009 B 79080 0
2010 B 79080 1
2012 D 69105 0
2013 D 69105 0
2014 D 69105 1
2015 D 69105 1
2016 D 69105 1
;
run;

Re: How to: Determine if YEAR in Table 1 is within the time range [START END] in Table 2

ballardw — Mon, 09 Oct 2023 20:56:52 GMT

This appears to work for your example data:

data want;
   merge have2 (in=in2)
         have1 (in=in1);
   by name code;
   exist = (year(start) le year le year(end));
   drop start end;
   if in2;
run;

Caution: this requires the sets to be sorted by Name and Code.

IF ONLY Code is to be considered for the BY match then both sets need to be sorted by CODE.

If a Name/Code in the first case, or Code in the second exists on two or more observations in both sets you will need to provide some rules as that could well mean a much more complicated situation and resolution.

It is a good idea when considering comparing Year or Month values against actual dates to carefully describe the rules. It may be that you get some cases where you don't expect Year=2006 to be considered in the interval 20050712 20060531 for some reason, such as less than half of 2006 is in the interval. So if you have such rules that were not stated then you need to add them.