# Difficult date question

20 messages
Open this post in threaded view
|

## Difficult date question

Here’s one I can’t get my head around.

I can get part of this via the lag function and it’s easy with one complication that I can’t figure out. I’m assuming it needs some type of macro or a few nested loops.

What’s the best way to do the following.

Each person has an ID and can have several different rows. Each row as a begin date (DateIn) and end date (DateOut) but also date of an occurrence (compare date).

I have to compare each of the CompareDates with the DateIn and DateOut periods to see whether any fall within the period from DateIn to DateOut (inclusive of the DateIn and DateOut) across all rows with the same ID number.  Result = 1 when the CompareDate falls within any of the DateIn to DateOut periods within the same Person ID number (either in rows before or after).

So it’s possible that the CompareDate can be within an interval on the row either before it or after it and that’s the part that’s throwing me.

In getting started, I can do the following easily, but unsure how to finish.

If((CompareDate >= DateIn) and (CompareDate <= DateOut))        Result = 1.

If((CompareDate >= lag(DateIn)) and (CompareDate <= lag(DateOut)))        Result = 1.

If((CompareDate >= lag(DateIn 2)) and (CompareDate <= lag(DateOut,2)))  Result = 1.

If((CompareDate >= lag(DateIn,3)) and (CompareDate <= lag(DateOut 3)))  Result = 1.

Example with just the first 2 people. Each person will have a different number of rows.

ID   DateIn            DateOut          CompareDate          Result

1     01-Sep-04      09-Sep-04          05-Oct-04                 1

1     23-Sep-04      30-Sep-04          15-Jan-04                  0

1     10-Sep-04      09-Nov-04          06-Sep-04                1

2     01-Jan-04      09-Feb-04          05-Oct-04                  0

2     20-Feb-04      15-Mar-04          24-Sept-04               0

Jeff

 Virus-free. www.avast.com
===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD
Open this post in threaded view
|

## Re: Difficult date question

Open this post in threaded view
|

## Re: Difficult date question

Open this post in threaded view
|

## Re: Difficult date question

Open this post in threaded view
|

## Re: Difficult date question

 Bruce, Yabbut ... His is always right, and yours can fail.  I'm reading the requirement like he does - that the separate ranges have to be used. Using RANGE( ) would make his Macro easier to read. The problem with his is, Do you have a (reasonably small) maximum number of lines for a case? -- Rich Ulrich From: SPSSX(r) Discussion <[hidden email]> on behalf of Bruce Weaver <[hidden email]> Sent: Saturday, June 27, 2020 9:55 AM To: [hidden email] <[hidden email]> Subject: Re: Difficult date question   Here's what I had in mind, using Mario's sample dataset. NEW FILE. DATASET CLOSE ALL. DATA LIST FREE(",") /ID (F8) DateIn (DATE) DateOut (DATE) CompareDate (DATE). BEGIN DATA 1,01-Sep-04,09-Sep-04,05-Oct-04 1,23-Sep-04,30-Sep-04,15-Jan-04 1,10-Sep-04,09-Nov-04,06-Sep-04 2,01-Jan-04,09-Feb-04,05-Oct-04 2,20-Feb-04,15-Mar-04,24-Sep-04 END DATA. AGGREGATE   /OUTFILE=* MODE=ADDVARIABLES OVERWRITE=YES   /BREAK=ID   /MinDateIn=MIN(DateIn)   /MaxDateOut=MAX(DateOut). ALTER TYPE MinDateIn MaxDateOut (DATE11). COMPUTE Result = RANGE(CompareDate, MinDateIn, MaxDateOut). FORMATS Result (F1). LIST ID DateIn DateOut CompareDate Result . Here is the output from my LIST command:       ID      DateIn     DateOut CompareDate Result          1 01-SEP-2004 09-SEP-2004 05-OCT-2004    1        1 23-SEP-2004 30-SEP-2004 15-JAN-2004    0        1 10-SEP-2004 09-NOV-2004 06-SEP-2004    1        2 01-JAN-2004 09-FEB-2004 05-OCT-2004    0        2 20-FEB-2004 15-MAR-2004 24-SEP-2004    0 Here is the output I get if I run Mario's code, but change his final EXECUTE to LIST:       ID      DateIn     DateOut CompareDate   Result          1 01-SEP-2004 09-SEP-2004 05-OCT-2004     1.00        1 23-SEP-2004 30-SEP-2004 15-JAN-2004      .00        1 10-SEP-2004 09-NOV-2004 06-SEP-2004     1.00        2 01-JAN-2004 09-FEB-2004 05-OCT-2004      .00        2 20-FEB-2004 15-MAR-2004 24-SEP-2004      .00 The results look the same to me.  ;-)  Mario Giesel-2 wrote > @Bruce: I think aggregating can create problems if there are dates between > min and max that are not included in any time interval. > @Jeff: This can be done along the following logic. I'm sure there are more > elegant solutions but this is  straightforward and easy to understand. > --- snip--- ----- -- Bruce Weaver [hidden email] http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." NOTE: My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. -- Sent from: http://spssx-discussion.1045642.n5.nabble.com/ ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD
Open this post in threaded view
|

## Re: Difficult date question

Open this post in threaded view
|

## Re: Difficult date question

Open this post in threaded view
|

## Re: Difficult date question

Open this post in threaded view
|

## Re: Difficult date question

Open this post in threaded view
|

## Re: Difficult date question

 In reply to this post by Rich Ulrich Interesting approach. The use of RECODE to initialize is something I'll try to remember. I appreciate that you frequently bring insight to the questions posed on this list.  "I don't know how much the processing would slow down when using 2 or 3 or 10 years. " That gives you away as one of us old-timers. It is several years since I timed SPSS runs using different approaches. YMMV. With today's computers the difference in CPU time, memory, or storage is usually negligible. It is even becoming increasingly rare for me to bother redrafting syntax only on a subset of the data. Often the personnel time (1) to quality-assurance check the syntax and (2) explain the syntax to clients or critics is much more of a consideration. So I emphasize the readability of syntax. ----- Art Kendall Social Research Consultants -- Sent from: http://spssx-discussion.1045642.n5.nabble.com/===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD Art Kendall Social Research Consultants
Open this post in threaded view
|

## Re: Difficult date question

Open this post in threaded view
|

## Re: Difficult date question

Open this post in threaded view
|

## Re: Difficult date question

Open this post in threaded view
|

## Re: Difficult date question

Open this post in threaded view
|

## Re: Difficult date question

Open this post in threaded view
|

## Re: Difficult date question

Open this post in threaded view
|

## Re: Difficult date question

Open this post in threaded view
|