About Alice_SAS

Alice_SAS · ‎10-10-2023

Yes. HAVE2 has over 60000 rows and 20 columns, while HAVE1 has 1.5 million rows and 10 columns.

Alice_SAS · ‎10-10-2023

Yes. But they are in different situations, especially for have1. Each NAME and CODE corresponds to different time intervals and needs to be concatenated for judgment. The previous code cannot be implemented in this situation.

Alice_SAS · ‎10-10-2023

Here is the data: data have1; input start: YYMMDD8. end: YYMMDD8. name:$1. code; format start YYMMDDn8. end YYMMDDn8.; datalines; 20050712 20060531 A 83265 20070531 20080531 A 83265 20100624 20130812 B 79080 20090620 20110912 B 79080 20040526 20051109 C 35423 20051109 20100831 C 35423 20100831 20110930 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; 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 1 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;

Alice_SAS · ‎10-10-2023

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. Any part of the YEAR can be within the time range. I hope to match through the common CODE of two tables and obtain Want. Thank you for your help!

Alice_SAS · ‎10-09-2023

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！

Alice_SAS · ‎10-09-2023

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

Alice_SAS · ‎10-09-2023

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.

Alice_SAS · ‎10-09-2023

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;

Alice_SAS · ‎10-09-2023

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!

Alice_SAS · ‎10-09-2023

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;

Alice_SAS · ‎10-09-2023

Thank you for your help. However, I found an error in the output of your code regarding the results of B Y 20030601 20091211 and B P 20040704 20100806 in the Want table I provided. How can I modify the code？

Alice_SAS · ‎10-09-2023

I have the data: data have; input source:$1. target:$1. start: YYMMDD8. end: YYMMDD8.; format start YYMMDDn8. end YYMMDDn8.; datalines; A X 20050106 20070506 B Y 20030601 20060704 B Y 20060704 20080905 B Y 20080905 20091211 B P 20040704 20080905 B P 20061001 20100806 C L 20070911 20081120 D M 20030507 20040506 D M 20050809 20071001 ; run; I want to obtain the form: data want; input source:$1. target:$1. start: YYMMDD8. end: YYMMDD8.; format start YYMMDDn8. end YYMMDDn8.; datalines; A X 20050106 20070506 B Y 20030601 20091211 B P 20040704 20100806 C L 20070911 20081120 D M 20030507 20040506 D M 20050809 20071001 ; run; Thank you!

Alice_SAS · ‎10-09-2023

Thanks for your answer. But I forgot one situation. I don't want to merge non-overlapping times. For example, what should I do in the following situation? source target start end D M 20030507 20040506 D M 20050809 20071001 In this situation, I don't want to do any merge, what should I do? Thank you!

Alice_SAS · ‎10-09-2023

For time ranges that are connected or overlap, I want to save the start and end times. For non-overlapping times, I don't want to merge them.

Alice_SAS · ‎10-09-2023

Yes, that situation exists. If the two separate date ranges don't overlap, I prefer not to merge them and leave them as they are.

Online Status	Offline
Date Last Visited	‎10-29-2023 02:19 AM

Re: How to : Determine whether the year in one table is within the co...

Re: How to : Determine whether the year in one table is within the co...

Re: How to : Determine whether the year in one table is within the co...

How to : Determine whether the year in one table is within the corres...

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

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

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

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

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

How to: Determine if YEAR in Table 1 is within the time range [START E...

Re: How to : Determine whether the year in one table is within the co...

Re: How to : Determine whether the year in one table is within the co...

Re: How to : Determine whether the year in one table is within the co...

How to : Determine whether the year in one table is within the corres...

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

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

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

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

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

How to: Determine if YEAR in Table 1 is within the time range [START E...

Re: Merge data by date range

Re: Merge data by date range

Re: Merge data by date range

Re: Merge data by date range

Re: Merge data by date range