flagging dates against a range of dates

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

flagging dates against a range of dates

Kylie Lange-3

Hi all,

 

I have two files from the same study. One is a Daily File, containing one case for each day that the subject participated in our program (each day starts from midnight, but may not be consecutive). The second is a StartStop File and contains the datetimes that each subject started and stopped a particular task within the program. Within the Daily File I need to flag whether that day is included in a Start/Stop period in the StartStop File.

 

So what I have is:

 

Daily File:

ID  Day  Date

111  1  10.3.2016 00:00:00

111  2  11.3.2016 00:00:00

111  3  12.3.2016 00:00:00

111  4  13.3.2016 00:00:00

111  5  14.3.2016 00:00:00

111  7  16.3.2016 00:00:00

112  1  1.11.2016 00:00:00

112  2  2.11.2016 00:00:00

112  3  3.11.2016 00:00:00

112  4  4.11.2016 00:00:00

113  1  2.8.2016 00:00:00

113  3  4.8.2016 00:00:00

113  4  5.8.2016 00:00:00

 

StartStop File:

ID  StartDate  StopDate

111  10.3.2016 09:30:00  14.3.2016 16:17:00

112  1.11.2016 14:45:00   3.11.2016 08:30:00

113  4.8.2016 10:30:00  4.8.2016 17:05:00

 

What I want to end up with:

 

Daily File:

ID  Day  Date InclYN

111  1  10.3.2016 00:00:00  1

111  2  11.3.2016 00:00:00  1

111  3  12.3.2016 00:00:00  1

111  4  13.3.2016 00:00:00  1

111  5  14.3.2016 00:00:00  1

111  7  16.3.2016 00:00:00  0

112  1  1.11.2016 00:00:00  1

112  2  2.11.2016 00:00:00  1

112  3  3.11.2016 00:00:00  1

112  4  4.11.2016 00:00:00  0

113  1  2.8.2016 00:00:00  0

113  3  4.8.2016 00:00:00  1

113  4  5.8.2016 00:00:00  0

 

Can anyone give me a hand with this? I am stuck with how to expand the StartStop file into individual records for each day, which I could then merge with the Daily File to create the Incl flag.

 

Thanks,
Kylie.

 

===================== 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
Reply | Threaded
Open this post in threaded view
|

Re: flagging dates against a range of dates

Maguin, Eugene

Can the startstop file have multiple records for the same id?

If no, then have you tried treating the startstop file as the table file in a match files command.

If yes, then, yeah, that’s harder but you could do a casestovars command on the startstop file and a do repeat, I think, would work to do the comparisons.

 

Gene Maguin

 

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Kylie Lange
Sent: Tuesday, August 29, 2017 3:33 AM
To: [hidden email]
Subject: flagging dates against a range of dates

 

Hi all,

 

I have two files from the same study. One is a Daily File, containing one case for each day that the subject participated in our program (each day starts from midnight, but may not be consecutive). The second is a StartStop File and contains the datetimes that each subject started and stopped a particular task within the program. Within the Daily File I need to flag whether that day is included in a Start/Stop period in the StartStop File.

 

So what I have is:

 

Daily File:

ID  Day  Date

111  1  10.3.2016 00:00:00

111  2  11.3.2016 00:00:00

111  3  12.3.2016 00:00:00

111  4  13.3.2016 00:00:00

111  5  14.3.2016 00:00:00

111  7  16.3.2016 00:00:00

112  1  1.11.2016 00:00:00

112  2  2.11.2016 00:00:00

112  3  3.11.2016 00:00:00

112  4  4.11.2016 00:00:00

113  1  2.8.2016 00:00:00

113  3  4.8.2016 00:00:00

113  4  5.8.2016 00:00:00

 

StartStop File:

ID  StartDate  StopDate

111  10.3.2016 09:30:00  14.3.2016 16:17:00

112  1.11.2016 14:45:00   3.11.2016 08:30:00

113  4.8.2016 10:30:00  4.8.2016 17:05:00

 

What I want to end up with:

 

Daily File:

ID  Day  Date InclYN

111  1  10.3.2016 00:00:00  1

111  2  11.3.2016 00:00:00  1

111  3  12.3.2016 00:00:00  1

111  4  13.3.2016 00:00:00  1

111  5  14.3.2016 00:00:00  1

111  7  16.3.2016 00:00:00  0

112  1  1.11.2016 00:00:00  1

112  2  2.11.2016 00:00:00  1

112  3  3.11.2016 00:00:00  1

112  4  4.11.2016 00:00:00  0

113  1  2.8.2016 00:00:00  0

113  3  4.8.2016 00:00:00  1

113  4  5.8.2016 00:00:00  0

 

Can anyone give me a hand with this? I am stuck with how to expand the StartStop file into individual records for each day, which I could then merge with the Daily File to create the Incl flag.

 

Thanks,
Kylie.

 

===================== 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
Reply | Threaded
Open this post in threaded view
|

Re: flagging dates against a range of dates

88videos
In reply to this post by Kylie Lange-3


I usually get advices here, my first advice to someone...

to older users: don' t  laugh .... :)

Try something like this.

dataset close all.
NEW FILE.

****************************
*Transformations to have your databases
*****************************

data list list
/ID(f8.0) Day(f8.0) Date(A55).
begin data.
111  1  '10.3.2016 00:00:00'
111  2  '11.3.2016 00:00:00'
111  3  '12.3.2016 00:00:00'
111  4  '13.3.2016 00:00:00'
111  5  '14.3.2016 00:00:00'
111  7  '16.3.2016 00:00:00'
112  1  '1.11.2016 00:00:00'
112  2  '2.11.2016 00:00:00'
112  3  '3.11.2016 00:00:00'
112  4  '4.11.2016 00:00:00'
113  1  '2.8.2016 00:00:00'
113  3  '4.8.2016 00:00:00'
113  4  '5.8.2016 00:00:00'
end data.
execute.
DATASET NAME b1.

DATASET ACTIVATE b1.

compute #i= CHAR.INDEX(date, ' ' ).
compute date=CHAR.SUBSTR(date, 1, (#i-1)).
execute.


SPSSINC TRANS RESULT = a1 to a3 TYPE=4
      /FORMULA "string.split(Date,'.')".

ALTER TYPE a1 to a3 (f8.0).

compute date2 = DATE.dmy(a1, a2, a3).
execute.

formats date2(edate10).

data list list
/ID(f8.0) StartDate(A55) StopDate(A55).
begin data.
111  '10.3.2016 09:30:00'  '14.3.2016 16:17:00'
112  '1.11.2016 14:45:00'   '3.11.2016 08:30:00'
113  '4.8.2016 10:30:00'  '4.8.2016 17:05:00'
end data.
execute.
DATASET NAME b2.

dataset activate b2.

do repeat a = #i1 #i2 / b =StartDate StopDate.
compute a = CHAR.INDEX(b, ' ' ).
compute b=CHAR.SUBSTR(b, 1, (a-1)).
end repeat.
execute.


SPSSINC TRANS RESULT = a1 to a3 TYPE=4
      /FORMULA "string.split(StartDate,'.')".


SPSSINC TRANS RESULT = b1 to b3 TYPE=4
      /FORMULA "string.split(StopDate,'.')".



ALTER TYPE a1 to b3 (f8.0).

compute startdate2 = DATE.dmy(a1, a2, a3).
execute.


compute stopdate2 = DATE.dmy(b1, b2, b3).
execute.

formats startdate2 stopdate2(edate10).

*************************************************
*Here you have my solution
***********************************************


compute datediff = stopdate2 -  startdate2.
execute.

do if datediff = 86400.
compute d1=startdate2+86400.
else if datediff = 2*86400.
compute d1=startdate2+86400.
compute d2=startdate2+2*86400.
else if datediff = 3*86400.
compute d1=startdate2+86400.
compute d2=startdate2+2*86400.
compute d3=startdate2+3*86400.
else if datediff = 4*86400.
compute d1=startdate2+86400.
compute d2=startdate2+2*86400.
compute d3=startdate2+3*86400.
compute d4=startdate2+4*86400.
else if datediff = 5*86400.
compute d1=startdate2+86400.
compute d2=startdate2+2*86400.
compute d3=startdate2+3*86400.
compute d4=startdate2+4*86400.
compute d5=startdate2+5*86400.
end if.
execute.


formats d1 to d5(edate10).

MATCH FILES
/file=*
/keep startdate2 d1 to d5.
execute.

VARSTOCASES
/MAKE date2 from startdate2 to d5.

compute flag=1.
execute.
SORT CASES by date2(a).

dataset activate b1.

SORT CASES by date2(a).


match files
/file=*
/file='b2'
/by date2.
execute.

sort cases by id.


DELETE VARIABLES date to a3.


list.




Reply | Threaded
Open this post in threaded view
|

Re: flagging dates against a range of dates

Kylie Lange-3
In reply to this post by Maguin, Eugene
Thanks Gene.
The StartStop file can have more than one case per ID (though I was going to be happy to ignore that for now). The time periods in the multiple StartStops will be non-overlapping.
I've also discovered that the number of entries in the Daily file will be a max of 28 per ID.


Sent from my Windows Phone

From: [hidden email]
Sent: ‎29/‎08/‎2017 10:27 PM
To: [hidden email]; [hidden email]
Subject: RE: flagging dates against a range of dates

Can the startstop file have multiple records for the same id?

If no, then have you tried treating the startstop file as the table file in a match files command.

If yes, then, yeah, that’s harder but you could do a casestovars command on the startstop file and a do repeat, I think, would work to do the comparisons.

 

Gene Maguin

 

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Kylie Lange
Sent: Tuesday, August 29, 2017 3:33 AM
To: [hidden email]
Subject: flagging dates against a range of dates

 

Hi all,

 

I have two files from the same study. One is a Daily File, containing one case for each day that the subject participated in our program (each day starts from midnight, but may not be consecutive). The second is a StartStop File and contains the datetimes that each subject started and stopped a particular task within the program. Within the Daily File I need to flag whether that day is included in a Start/Stop period in the StartStop File.

 

So what I have is:

 

Daily File:

ID  Day  Date

111  1  10.3.2016 00:00:00

111  2  11.3.2016 00:00:00

111  3  12.3.2016 00:00:00

111  4  13.3.2016 00:00:00

111  5  14.3.2016 00:00:00

111  7  16.3.2016 00:00:00

112  1  1.11.2016 00:00:00

112  2  2.11.2016 00:00:00

112  3  3.11.2016 00:00:00

112  4  4.11.2016 00:00:00

113  1  2.8.2016 00:00:00

113  3  4.8.2016 00:00:00

113  4  5.8.2016 00:00:00

 

StartStop File:

ID  StartDate  StopDate

111  10.3.2016 09:30:00  14.3.2016 16:17:00

112  1.11.2016 14:45:00   3.11.2016 08:30:00

113  4.8.2016 10:30:00  4.8.2016 17:05:00

 

What I want to end up with:

 

Daily File:

ID  Day  Date InclYN

111  1  10.3.2016 00:00:00  1

111  2  11.3.2016 00:00:00  1

111  3  12.3.2016 00:00:00  1

111  4  13.3.2016 00:00:00  1

111  5  14.3.2016 00:00:00  1

111  7  16.3.2016 00:00:00  0

112  1  1.11.2016 00:00:00  1

112  2  2.11.2016 00:00:00  1

112  3  3.11.2016 00:00:00  1

112  4  4.11.2016 00:00:00  0

113  1  2.8.2016 00:00:00  0

113  3  4.8.2016 00:00:00  1

113  4  5.8.2016 00:00:00  0

 

Can anyone give me a hand with this? I am stuck with how to expand the StartStop file into individual records for each day, which I could then merge with the Daily File to create the Incl flag.

 

Thanks,
Kylie.

 

===================== 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
Reply | Threaded
Open this post in threaded view
|

Re: flagging dates against a range of dates

Bruce Weaver
Administrator
In reply to this post by Maguin, Eugene
Given the sample data Kylie provided, it *looks* like a simple MATCH FILES with the StartStop dataset treated as a look-up table.  But that approach does not yield results that match Kylie's desired outcome.  

* Code to generate the datasets omitted to save space.
MATCH FILES
 FILE = Daily /
 TABLE = StartStop /
 BY ID.
EXECUTE.
DATASET NAME alldata.
DATASET ACTIVATE alldata.

COMPUTE InclYN = RANGE(Date, StartDate, StopDate).
FORMATS InclYN(F1).
LIST ID Day Date InclYN.

OUTPUT from LIST with DESIRED output shown too:

   ID Day       Date InclYN   DESIRED
 
  111   1 10.03.2016    0      1
  111   2 11.03.2016    1      1
  111   3 12.03.2016    1      1
  111   4 13.03.2016    1      1
  111   5 14.03.2016    1      1
  111   7 16.03.2016    0      0
  112   1 01.11.2016    0      1
  112   2 02.11.2016    1      1
  112   3 03.11.2016    1      1
  112   4 04.11.2016    0      0
  113   1 02.08.2016    0      0
  113   3 04.08.2016    0      1
  113   4 05.08.2016    0      0
 
Number of cases read:  13    Number of cases listed:  13

Kylie, can you explain the discrepancy?  


Maguin, Eugene wrote
Can the startstop file have multiple records for the same id?
If no, then have you tried treating the startstop file as the table file in a match files command.
If yes, then, yeah, that's harder but you could do a casestovars command on the startstop file and a do repeat, I think, would work to do the comparisons.

Gene Maguin

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Kylie Lange
Sent: Tuesday, August 29, 2017 3:33 AM
To: [hidden email]
Subject: flagging dates against a range of dates

Hi all,

I have two files from the same study. One is a Daily File, containing one case for each day that the subject participated in our program (each day starts from midnight, but may not be consecutive). The second is a StartStop File and contains the datetimes that each subject started and stopped a particular task within the program. Within the Daily File I need to flag whether that day is included in a Start/Stop period in the StartStop File.

So what I have is:

Daily File:
ID  Day  Date
111  1  10.3.2016 00:00:00
111  2  11.3.2016 00:00:00
111  3  12.3.2016 00:00:00
111  4  13.3.2016 00:00:00
111  5  14.3.2016 00:00:00
111  7  16.3.2016 00:00:00
112  1  1.11.2016 00:00:00
112  2  2.11.2016 00:00:00
112  3  3.11.2016 00:00:00
112  4  4.11.2016 00:00:00
113  1  2.8.2016 00:00:00
113  3  4.8.2016 00:00:00
113  4  5.8.2016 00:00:00

StartStop File:
ID  StartDate  StopDate
111  10.3.2016 09:30:00  14.3.2016 16:17:00
112  1.11.2016 14:45:00   3.11.2016 08:30:00
113  4.8.2016 10:30:00  4.8.2016 17:05:00

What I want to end up with:

Daily File:
ID  Day  Date InclYN
111  1  10.3.2016 00:00:00  1
111  2  11.3.2016 00:00:00  1
111  3  12.3.2016 00:00:00  1
111  4  13.3.2016 00:00:00  1
111  5  14.3.2016 00:00:00  1
111  7  16.3.2016 00:00:00  0
112  1  1.11.2016 00:00:00  1
112  2  2.11.2016 00:00:00  1
112  3  3.11.2016 00:00:00  1
112  4  4.11.2016 00:00:00  0
113  1  2.8.2016 00:00:00  0
113  3  4.8.2016 00:00:00  1
113  4  5.8.2016 00:00:00  0

Can anyone give me a hand with this? I am stuck with how to expand the StartStop file into individual records for each day, which I could then merge with the Daily File to create the Incl flag.

Thanks,
Kylie.

===================== To manage your subscription to SPSSX-L, send a message to [hidden email]<mailto:[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
--
Bruce Weaver
bweaver@lakeheadu.ca
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.
Reply | Threaded
Open this post in threaded view
|

Re: flagging dates against a range of dates

Kylie Lange-3
Thanks Bruce. I'm not in front of SPSS at the moment, but it looks like the RANGE result is just not including the start date if it is a match (ie, its not an inclusive range)? If the first day is included, then InclYN would be spot on. Should be an easy tweak.

Appreciate the pointer from all to using MATCH FILES - I had a blind spot to the most obvious approach.

Sent from my Windows Phone

From: [hidden email]
Sent: ‎29/‎08/‎2017 11:58 PM
To: [hidden email]
Subject: Re: flagging dates against a range of dates

Given the sample data Kylie provided, it *looks* like a simple MATCH FILES
with the StartStop dataset treated as a look-up table.  But that approach
does not yield results that match Kylie's desired outcome. 

* Code to generate the datasets omitted to save space.
MATCH FILES
 FILE = Daily /
 TABLE = StartStop /
 BY ID.
EXECUTE.
DATASET NAME alldata.
DATASET ACTIVATE alldata.

COMPUTE InclYN = RANGE(Date, StartDate, StopDate).
FORMATS InclYN(F1).
LIST ID Day Date InclYN.

OUTPUT from LIST with DESIRED output shown too:

   ID Day       Date InclYN   DESIRED
 
  111   1 10.03.2016    0      1
  111   2 11.03.2016    1      1
  111   3 12.03.2016    1      1
  111   4 13.03.2016    1      1
  111   5 14.03.2016    1      1
  111   7 16.03.2016    0      0
  112   1 01.11.2016    0      1
  112   2 02.11.2016    1      1
  112   3 03.11.2016    1      1
  112   4 04.11.2016    0      0
  113   1 02.08.2016    0      0
  113   3 04.08.2016    0      1
  113   4 05.08.2016    0      0
 
Number of cases read:  13    Number of cases listed:  13

Kylie, can you explain the discrepancy? 



Maguin, Eugene wrote
> Can the startstop file have multiple records for the same id?
> If no, then have you tried treating the startstop file as the table file
> in a match files command.
> If yes, then, yeah, that's harder but you could do a casestovars command
> on the startstop file and a do repeat, I think, would work to do the
> comparisons.
>
> Gene Maguin
>
> From: SPSSX(r) Discussion [mailto:

> SPSSX-L@.UGA

> ] On Behalf Of Kylie Lange
> Sent: Tuesday, August 29, 2017 3:33 AM
> To:

> SPSSX-L@.UGA

> Subject: flagging dates against a range of dates
>
> Hi all,
>
> I have two files from the same study. One is a Daily File, containing one
> case for each day that the subject participated in our program (each day
> starts from midnight, but may not be consecutive). The second is a
> StartStop File and contains the datetimes that each subject started and
> stopped a particular task within the program. Within the Daily File I need
> to flag whether that day is included in a Start/Stop period in the
> StartStop File.
>
> So what I have is:
>
> Daily File:
> ID  Day  Date
> 111  1  10.3.2016 00:00:00
> 111  2  11.3.2016 00:00:00
> 111  3  12.3.2016 00:00:00
> 111  4  13.3.2016 00:00:00
> 111  5  14.3.2016 00:00:00
> 111  7  16.3.2016 00:00:00
> 112  1  1.11.2016 00:00:00
> 112  2  2.11.2016 00:00:00
> 112  3  3.11.2016 00:00:00
> 112  4  4.11.2016 00:00:00
> 113  1  2.8.2016 00:00:00
> 113  3  4.8.2016 00:00:00
> 113  4  5.8.2016 00:00:00
>
> StartStop File:
> ID  StartDate  StopDate
> 111  10.3.2016 09:30:00  14.3.2016 16:17:00
> 112  1.11.2016 14:45:00   3.11.2016 08:30:00
> 113  4.8.2016 10:30:00  4.8.2016 17:05:00
>
> What I want to end up with:
>
> Daily File:
> ID  Day  Date InclYN
> 111  1  10.3.2016 00:00:00  1
> 111  2  11.3.2016 00:00:00  1
> 111  3  12.3.2016 00:00:00  1
> 111  4  13.3.2016 00:00:00  1
> 111  5  14.3.2016 00:00:00  1
> 111  7  16.3.2016 00:00:00  0
> 112  1  1.11.2016 00:00:00  1
> 112  2  2.11.2016 00:00:00  1
> 112  3  3.11.2016 00:00:00  1
> 112  4  4.11.2016 00:00:00  0
> 113  1  2.8.2016 00:00:00  0
> 113  3  4.8.2016 00:00:00  1
> 113  4  5.8.2016 00:00:00  0
>
> Can anyone give me a hand with this? I am stuck with how to expand the
> StartStop file into individual records for each day, which I could then
> merge with the Daily File to create the Incl flag.
>
> Thanks,
> Kylie.
>
> ===================== To manage your subscription to SPSSX-L, send a
> message to

> LISTSERV@.UGA

> &lt;mailto:

> LISTSERV@.UGA

> &gt; (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

> LISTSERV@.UGA

>  (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





-----
--
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.

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/flagging-dates-against-a-range-of-dates-tp5734769p5734774.html
Sent from the SPSSX Discussion mailing list archive at 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
Reply | Threaded
Open this post in threaded view
|

Re: flagging dates against a range of dates

Bruce Weaver
Administrator
The problem is not with RANGE.  The problem is that your StartStop file includes the time portion, whereas the Daily file has Time = 00:00:00 in all cases.  If you ignore the time portion of the start and stop dates, I think it will work as you want.  

Here is the output after modifying my code to ignore the TIME portion in the start/stop file.

   ID Day        Date InclYN
 
  111   1 10-MAR-2016    1
  111   2 11-MAR-2016    1
  111   3 12-MAR-2016    1
  111   4 13-MAR-2016    1
  111   5 14-MAR-2016    1
  111   7 16-MAR-2016    0
  112   1 01-NOV-2016    1
  112   2 02-NOV-2016    1
  112   3 03-NOV-2016    1
  112   4 04-NOV-2016    0
  113   1 02-AUG-2016    0
  113   3 04-AUG-2016    1
  113   4 05-AUG-2016    0
 
Number of cases read:  13    Number of cases listed:  13

This now matches your desired output.  


Kylie Lange-3 wrote
Thanks Bruce. I'm not in front of SPSS at the moment, but it looks like the RANGE result is just not including the start date if it is a match (ie, its not an inclusive range)? If the first day is included, then InclYN would be spot on. Should be an easy tweak.

Appreciate the pointer from all to using MATCH FILES - I had a blind spot to the most obvious approach.

Sent from my Windows Phone
________________________________
From: Bruce Weaver<mailto:[hidden email]>
Sent: ‎29/‎08/‎2017 11:58 PM
To: [hidden email]<mailto:[hidden email]>
Subject: Re: flagging dates against a range of dates

Given the sample data Kylie provided, it *looks* like a simple MATCH FILES
with the StartStop dataset treated as a look-up table.  But that approach
does not yield results that match Kylie's desired outcome.

* Code to generate the datasets omitted to save space.
MATCH FILES
 FILE = Daily /
 TABLE = StartStop /
 BY ID.
EXECUTE.
DATASET NAME alldata.
DATASET ACTIVATE alldata.

COMPUTE InclYN = RANGE(Date, StartDate, StopDate).
FORMATS InclYN(F1).
LIST ID Day Date InclYN.

OUTPUT from LIST with DESIRED output shown too:

   ID Day       Date InclYN   DESIRED

  111   1 10.03.2016    0      1
  111   2 11.03.2016    1      1
  111   3 12.03.2016    1      1
  111   4 13.03.2016    1      1
  111   5 14.03.2016    1      1
  111   7 16.03.2016    0      0
  112   1 01.11.2016    0      1
  112   2 02.11.2016    1      1
  112   3 03.11.2016    1      1
  112   4 04.11.2016    0      0
  113   1 02.08.2016    0      0
  113   3 04.08.2016    0      1
  113   4 05.08.2016    0      0

Number of cases read:  13    Number of cases listed:  13

Kylie, can you explain the discrepancy?



Maguin, Eugene wrote
> Can the startstop file have multiple records for the same id?
> If no, then have you tried treating the startstop file as the table file
> in a match files command.
> If yes, then, yeah, that's harder but you could do a casestovars command
> on the startstop file and a do repeat, I think, would work to do the
> comparisons.
>
> Gene Maguin
>
> From: SPSSX(r) Discussion [mailto:

> SPSSX-L@.UGA

> ] On Behalf Of Kylie Lange
> Sent: Tuesday, August 29, 2017 3:33 AM
> To:

> SPSSX-L@.UGA

> Subject: flagging dates against a range of dates
>
> Hi all,
>
> I have two files from the same study. One is a Daily File, containing one
> case for each day that the subject participated in our program (each day
> starts from midnight, but may not be consecutive). The second is a
> StartStop File and contains the datetimes that each subject started and
> stopped a particular task within the program. Within the Daily File I need
> to flag whether that day is included in a Start/Stop period in the
> StartStop File.
>
> So what I have is:
>
> Daily File:
> ID  Day  Date
> 111  1  10.3.2016 00:00:00
> 111  2  11.3.2016 00:00:00
> 111  3  12.3.2016 00:00:00
> 111  4  13.3.2016 00:00:00
> 111  5  14.3.2016 00:00:00
> 111  7  16.3.2016 00:00:00
> 112  1  1.11.2016 00:00:00
> 112  2  2.11.2016 00:00:00
> 112  3  3.11.2016 00:00:00
> 112  4  4.11.2016 00:00:00
> 113  1  2.8.2016 00:00:00
> 113  3  4.8.2016 00:00:00
> 113  4  5.8.2016 00:00:00
>
> StartStop File:
> ID  StartDate  StopDate
> 111  10.3.2016 09:30:00  14.3.2016 16:17:00
> 112  1.11.2016 14:45:00   3.11.2016 08:30:00
> 113  4.8.2016 10:30:00  4.8.2016 17:05:00
>
> What I want to end up with:
>
> Daily File:
> ID  Day  Date InclYN
> 111  1  10.3.2016 00:00:00  1
> 111  2  11.3.2016 00:00:00  1
> 111  3  12.3.2016 00:00:00  1
> 111  4  13.3.2016 00:00:00  1
> 111  5  14.3.2016 00:00:00  1
> 111  7  16.3.2016 00:00:00  0
> 112  1  1.11.2016 00:00:00  1
> 112  2  2.11.2016 00:00:00  1
> 112  3  3.11.2016 00:00:00  1
> 112  4  4.11.2016 00:00:00  0
> 113  1  2.8.2016 00:00:00  0
> 113  3  4.8.2016 00:00:00  1
> 113  4  5.8.2016 00:00:00  0
>
> Can anyone give me a hand with this? I am stuck with how to expand the
> StartStop file into individual records for each day, which I could then
> merge with the Daily File to create the Incl flag.
>
> Thanks,
> Kylie.
>
> ===================== To manage your subscription to SPSSX-L, send a
> message to

> LISTSERV@.UGA

> <mailto:

> LISTSERV@.UGA

> > (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

> LISTSERV@.UGA

>  (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





-----
--
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.

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/flagging-dates-against-a-range-of-dates-tp5734769p5734774.html
Sent from the SPSSX Discussion mailing list archive at 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
--
Bruce Weaver
bweaver@lakeheadu.ca
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.
Reply | Threaded
Open this post in threaded view
|

Re: flagging dates against a range of dates

Kylie Lange-3
Thanks Bruce and all. This did indeed give me what I needed, and I have been able to modify it for some further extensions that have come up since.
Thanks again.
Kylie.


-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Bruce Weaver
Sent: Wednesday, 30 August 2017 1:22 AM
To: [hidden email]
Subject: Re: flagging dates against a range of dates

The problem is not with RANGE.  The problem is that your StartStop file
includes the time portion, whereas the Daily file has Time = 00:00:00 in all
cases.  If you ignore the time portion of the start and stop dates, I think
it will work as you want.

Here is the output after modifying my code to ignore the TIME portion in the
start/stop file.

   ID Day        Date InclYN

  111   1 10-MAR-2016    1
  111   2 11-MAR-2016    1
  111   3 12-MAR-2016    1
  111   4 13-MAR-2016    1
  111   5 14-MAR-2016    1
  111   7 16-MAR-2016    0
  112   1 01-NOV-2016    1
  112   2 02-NOV-2016    1
  112   3 03-NOV-2016    1
  112   4 04-NOV-2016    0
  113   1 02-AUG-2016    0
  113   3 04-AUG-2016    1
  113   4 05-AUG-2016    0

Number of cases read:  13    Number of cases listed:  13

This now matches your desired output.



Kylie Lange-3 wrote

> Thanks Bruce. I'm not in front of SPSS at the moment, but it looks like
> the RANGE result is just not including the start date if it is a match
> (ie, its not an inclusive range)? If the first day is included, then
> InclYN would be spot on. Should be an easy tweak.
>
> Appreciate the pointer from all to using MATCH FILES - I had a blind spot
> to the most obvious approach.
>
> Sent from my Windows Phone
> ________________________________
> From: Bruce Weaver&lt;mailto:

> bruce.weaver@

> &gt;
> Sent: ‎29/‎08/‎2017 11:58 PM
> To:

> SPSSX-L@.UGA

> &lt;mailto:

> SPSSX-L@.UGA

> &gt;
> Subject: Re: flagging dates against a range of dates
>
> Given the sample data Kylie provided, it *looks* like a simple MATCH FILES
> with the StartStop dataset treated as a look-up table.  But that approach
> does not yield results that match Kylie's desired outcome.
>
> * Code to generate the datasets omitted to save space.
> MATCH FILES
>  FILE = Daily /
>  TABLE = StartStop /
>  BY ID.
> EXECUTE.
> DATASET NAME alldata.
> DATASET ACTIVATE alldata.
>
> COMPUTE InclYN = RANGE(Date, StartDate, StopDate).
> FORMATS InclYN(F1).
> LIST ID Day Date InclYN.
>
> OUTPUT from LIST with DESIRED output shown too:
>
>    ID Day       Date InclYN   DESIRED
>
>   111   1 10.03.2016    0      1
>   111   2 11.03.2016    1      1
>   111   3 12.03.2016    1      1
>   111   4 13.03.2016    1      1
>   111   5 14.03.2016    1      1
>   111   7 16.03.2016    0      0
>   112   1 01.11.2016    0      1
>   112   2 02.11.2016    1      1
>   112   3 03.11.2016    1      1
>   112   4 04.11.2016    0      0
>   113   1 02.08.2016    0      0
>   113   3 04.08.2016    0      1
>   113   4 05.08.2016    0      0
>
> Number of cases read:  13    Number of cases listed:  13
>
> Kylie, can you explain the discrepancy?
>
>
>
> Maguin, Eugene wrote
>> Can the startstop file have multiple records for the same id?
>> If no, then have you tried treating the startstop file as the table file
>> in a match files command.
>> If yes, then, yeah, that's harder but you could do a casestovars command
>> on the startstop file and a do repeat, I think, would work to do the
>> comparisons.
>>
>> Gene Maguin
>>
>> From: SPSSX(r) Discussion [mailto:
>
>> SPSSX-L@.UGA
>
>> ] On Behalf Of Kylie Lange
>> Sent: Tuesday, August 29, 2017 3:33 AM
>> To:
>
>> SPSSX-L@.UGA
>
>> Subject: flagging dates against a range of dates
>>
>> Hi all,
>>
>> I have two files from the same study. One is a Daily File, containing one
>> case for each day that the subject participated in our program (each day
>> starts from midnight, but may not be consecutive). The second is a
>> StartStop File and contains the datetimes that each subject started and
>> stopped a particular task within the program. Within the Daily File I
>> need
>> to flag whether that day is included in a Start/Stop period in the
>> StartStop File.
>>
>> So what I have is:
>>
>> Daily File:
>> ID  Day  Date
>> 111  1  10.3.2016 00:00:00
>> 111  2  11.3.2016 00:00:00
>> 111  3  12.3.2016 00:00:00
>> 111  4  13.3.2016 00:00:00
>> 111  5  14.3.2016 00:00:00
>> 111  7  16.3.2016 00:00:00
>> 112  1  1.11.2016 00:00:00
>> 112  2  2.11.2016 00:00:00
>> 112  3  3.11.2016 00:00:00
>> 112  4  4.11.2016 00:00:00
>> 113  1  2.8.2016 00:00:00
>> 113  3  4.8.2016 00:00:00
>> 113  4  5.8.2016 00:00:00
>>
>> StartStop File:
>> ID  StartDate  StopDate
>> 111  10.3.2016 09:30:00  14.3.2016 16:17:00
>> 112  1.11.2016 14:45:00   3.11.2016 08:30:00
>> 113  4.8.2016 10:30:00  4.8.2016 17:05:00
>>
>> What I want to end up with:
>>
>> Daily File:
>> ID  Day  Date InclYN
>> 111  1  10.3.2016 00:00:00  1
>> 111  2  11.3.2016 00:00:00  1
>> 111  3  12.3.2016 00:00:00  1
>> 111  4  13.3.2016 00:00:00  1
>> 111  5  14.3.2016 00:00:00  1
>> 111  7  16.3.2016 00:00:00  0
>> 112  1  1.11.2016 00:00:00  1
>> 112  2  2.11.2016 00:00:00  1
>> 112  3  3.11.2016 00:00:00  1
>> 112  4  4.11.2016 00:00:00  0
>> 113  1  2.8.2016 00:00:00  0
>> 113  3  4.8.2016 00:00:00  1
>> 113  4  5.8.2016 00:00:00  0
>>
>> Can anyone give me a hand with this? I am stuck with how to expand the
>> StartStop file into individual records for each day, which I could then
>> merge with the Daily File to create the Incl flag.
>>
>> Thanks,
>> Kylie.
>>
>> ===================== To manage your subscription to SPSSX-L, send a
>> message to
>
>> LISTSERV@.UGA
>
>> &lt;mailto:
>
>> LISTSERV@.UGA
>
>> &gt; (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
>
>> LISTSERV@.UGA
>
>>  (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
>
>
>
>
>
> -----
> --
> Bruce Weaver

> bweaver@

> 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.
>
> --
> View this message in context:
> http://spssx-discussion.1045642.n5.nabble.com/flagging-dates-against-a-range-of-dates-tp5734769p5734774.html
> Sent from the SPSSX Discussion mailing list archive at Nabble.com.
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

> LISTSERV@.UGA

>  (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

> LISTSERV@.UGA

>  (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





-----
--
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.

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/flagging-dates-against-a-range-of-dates-tp5734769p5734776.html
Sent from the SPSSX Discussion mailing list archive at 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