topic Re: How to: Determine if YEAR in Table 2 is within the time range of Table 1 in SAS Programming

How to: Determine if YEAR in Table 2 is within the time range of Table 1

Alice_SAS — Mon, 09 Oct 2023 11:08:30 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 through the common CODE of two tables and obtain Want.

Thank you for your help!

Re: How to: Determine if YEAR in Table 2 is within the time range of Table 1

Alice_SAS — Mon, 09 Oct 2023 11:09:37 GMT

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 2 is within the time range of Table 1

PaigeMiller — Mon, 09 Oct 2023 11:17:19 GMT

Do you mean that ANY PART of the year in HAVE2 is contained between the dates in HAVE1?

Or do you mean that ALL of the year in HAVE2 is contained between the dates in HAVE1?

Or do you mean something else?

Re: How to: Determine if YEAR in Table 2 is within the time range of Table 1

Alice_SAS — Mon, 09 Oct 2023 11:28:20 GMT

My idea is that for have2, first I need to determine whether there is a corresponding CODE in have1. If so, I will then separately determine whether each year is within the time range in have1.

For example, for the first line in have2, I first determine that CODE 83265 is within have1, and then I verify whether year 2011 is within the time range of 20050712 20060531. Obviously, it is not, so I let exist = 0.

Re: How to: Determine if YEAR in Table 2 is within the time range of Table 1

PaigeMiller — Mon, 09 Oct 2023 11:29:36 GMT

Thanks, but I don't think you have answered my questions clearly.

Do you mean that ANY PART of the year in HAVE2 is contained between the dates in HAVE1?

Or do you mean that ALL of the year in HAVE2 is contained between the dates in HAVE1?

Or do you mean something else?

Re: How to: Determine if YEAR in Table 2 is within the time range of Table 1

Alice_SAS — Mon, 09 Oct 2023 11:32:22 GMT

I understand your question. Any PART is OK, thank you very much!

Re: How to: Determine if YEAR in Table 2 is within the time range of Table 1

PaigeMiller — Mon, 09 Oct 2023 11:55:15 GMT

proc sql;
    create table want as select b.*,
    case when mdy(1,1,year) between start and end
        or mdy(12,31,year) between start and end then 1 else 0 end as exist
    from have1 as a right join have2 as b
    on a.code=b.code and a.name=b.name;
quit;

Re: How to: Determine if YEAR in Table 2 is within the time range of Table 1

FreelanceReinh — Mon, 09 Oct 2023 13:40:55 GMT

Hello @PaigeMiller,

I had a very similar idea, but then realized that it would fail if start and end were dates in the same year, e.g., from April and October, respectively. Therefore, the suggestion I had prepared used the criterion year(start)<=year<=year(end), possibly in a CASE-WHEN expression if the case "code not found in HAVE1" should be distinguished from "code found, but year criterion not met":

proc sql;
create table want as
select a.*, case when missing(b.code) then .
              else year(start)<=year<=year(end)
            end as exist
from have2 a left join have1 b
on a.code=b.code
order by name, year;
quit;

Re: How to: Determine if YEAR in Table 2 is within the time range of Table 1

Alice_SAS — Mon, 09 Oct 2023 13:44:28 GMT

Thank you for your answer. Although you solved my problem. But in reality, my tables have1 and have2 are both very large. I used your method but the operation failed because there was no memory. I think the problem lies in mdy(1,1, year). Is there a more efficient method? Thanks！

Re: How to: Determine if YEAR in Table 2 is within the time range of Table 1

PaigeMiller — Mon, 09 Oct 2023 14:12:31 GMT

MDY has nothing to do with this issue. The issue is that you don't have enough memory because the tables are so large.

Please give us the information about how many rows and columns are in each data set.