Activity Between Dates

[Kahklen, Craig]

Hello,

I am having difficulty trying to come up with syntax that will produce what
I need.  I need to calculate activity hours between dates when clients can
participate in more than one activity in a given time period.

The results I need using the data below are:

Client 1 participated for 80 hours from 9/1/06 to 9/21/06 and for 10 hours
9/25/06 to 9/30/06.

Client 2 participated for 50 hours from 9/1/06 and 9/14/06.

Client 3 participated for 80 hours from 9/1/06 and 9/30/06...and so on.

Here is the data:

DATA LIST /ID 1
   ACT1ST 3-10 (ADATE)  ACT1END 12-19 (ADATE) ACT1HRS 21-22
   ACT2ST 24-31 (ADATE) ACT2END 33-40 (ADATE) ACT2HRS 42-43
   ACT3ST 45-52 (ADATE) ACT3END 54-61 (ADATE) ACT3HRS 63-64.
BEGIN DATA
1 09/01/06 09/14/06 40 09/07/06 09/21/06 40 09/25/06 09/30/06 10
2 09/01/06 09/14/06 40 09/05/06 09/12/06 10
3 09/01/06 09/08/06 20 09/09/06 09/30/06 60
4 09/01/06 09/05/06 20 09/21/06 09/30/06 60
5 09/18/06 09/30/06 90
END DATA.

Please note that clients can have between 1 and 9 activities in a given
month and activities can be performed concurrently.

I would appreciate any help I can get.

Thank you,
Craig Kahklen

[Melissa Ives]

How will you know which activities to combine at which times?  Will you
always combine activity 1 and 2, but keep 3 separate?  You have
different time periods for each client--what is the rule for reporting
these for each client?

You could compute a min(ACT1ST,ACT2ST...) to max(ACT1END,ACT2END...) to
compute the widest range, then sum the appropriate activity hours.

Melissa

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Kahklen, Craig
Sent: Tuesday, November 28, 2006 12:21 PM
To: [hidden email]
Subject: [SPSSX-L] Activity Between Dates

Hello,

I am having difficulty trying to come up with syntax that will produce
what I need.  I need to calculate activity hours between dates when
clients can participate in more than one activity in a given time
period.

The results I need using the data below are:

Client 1 participated for 80 hours from 9/1/06 to 9/21/06 and for 10
hours
9/25/06 to 9/30/06.

Client 2 participated for 50 hours from 9/1/06 and 9/14/06.

Client 3 participated for 80 hours from 9/1/06 and 9/30/06...and so on.

Here is the data:

DATA LIST /ID 1
   ACT1ST 3-10 (ADATE)  ACT1END 12-19 (ADATE) a 21-22
   ACT2ST 24-31 (ADATE) ACT2END 33-40 (ADATE) ACT2HRS 42-43
   ACT3ST 45-52 (ADATE) ACT3END 54-61 (ADATE) ACT3HRS 63-64.
BEGIN DATA
1 09/01/06 09/14/06 40 09/07/06 09/21/06 40 09/25/06 09/30/06 10
2 09/01/06 09/14/06 40 09/05/06 09/12/06 10
3 09/01/06 09/08/06 20 09/09/06 09/30/06 60
4 09/01/06 09/05/06 20 09/21/06 09/30/06 60
5 09/18/06 09/30/06 90
END DATA.

Please note that clients can have between 1 and 9 activities in a given
month and activities can be performed concurrently.

I would appreciate any help I can get.

Thank you,
Craig Kahklen



PRIVILEGED AND CONFIDENTIAL INFORMATION
This transmittal and any attachments may contain PRIVILEGED AND
CONFIDENTIAL information and is intended only for the use of the
addressee. If you are not the designated recipient, or an employee
or agent authorized to deliver such transmittals to the designated
recipient, you are hereby notified that any dissemination,
copying or publication of this transmittal is strictly prohibited. If
you have received this transmittal in error, please notify us
immediately by replying to the sender and delete this copy from your
system. You may also call us at [phone] for assistance.

[Marks, Jim]

Craig:

Is the rule if the start of the next is before the previous end (or the
day following-- e.g. case 3) then we should combine the data?

If so, does this do what you want?

** Sample data (note additional cases and variables
** compared to your sample.

DATA LIST /ID 1
   ACT1ST 3-10 (ADATE)  ACT1END 12-19 (ADATE) ACT1HRS 21-22
   ACT2ST 24-31 (ADATE) ACT2END 33-40 (ADATE) ACT2HRS 42-43
   ACT3ST 45-52 (ADATE) ACT3END 54-61 (ADATE) ACT3HRS 63-64
   ACT4ST 66-74 (ADATE) ACT4END 76-83 (ADATE) ACT4HRS 85-96.
BEGIN DATA
1 09/01/06 09/14/06 40 09/07/06 09/21/06 40 09/25/06 09/30/06 10
2 09/01/06 09/14/06 40 09/05/06 09/12/06 10
3 09/01/06 09/08/06 20 09/09/06 09/30/06 60
4 09/01/06 09/05/06 20 09/21/06 09/30/06 60
5 09/18/06 09/30/06 90
6 09/01/06 09/14/06 40 09/17/06 09/21/06 40 09/25/06 09/30/06 10
6 09/01/06 09/14/06 40 09/17/06 09/21/06 40 09/25/06 09/30/06 10
7 09/01/06 09/14/06 40 09/07/06 09/21/06 40 09/25/06 09/30/06 10
10/25/06 10/30/06 10
END DATA.


** restructure data to "long" format-- one record per start-end pair.
** you will need to update the variable list and index value to include
9 pairs.

VARSTOCASES  /MAKE Start FROM ACT1ST ACT2ST ACT3ST ACT4ST
 /MAKE END FROM ACT1END ACT2END ACT3END ACT4END
  /MAKE HOURS from ACT1HRS ACT2HRS ACT3HRS ACT4HRS
 /INDEX = episode(4)
 /KEEP =  ID
 /NULL = KEEP.

** identify distinct sessions.

COMPUTE session = 1.
DO IF start gt lag(end)+86400 and id = lag(id).
COMPUTE SESSION = lag(session)+1.
END IF.

** exclude cases with no data.
COMPUTE usedat = ~SYSMIS(START).

EXE.

FILTER BY usedat.

** create a new dataset with the summarized episodes-- includes the
earliest start and latest end.

DATASET DECLARE SUMMADAT.

AGGREGATE OUTFILE = SUMMADAT  /BREAK = ID SESSION
  /START = MIN(START) /END = MAX(END) /HOURS = SUM(HOURS)
  /RECS = N.

I didn't test for unusual "episodes" e.g. act2 starting before act1, or
all 4 acts rolling up into one episode.

HTH
--jim

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Kahklen, Craig
Sent: Tuesday, November 28, 2006 12:21 PM
To: [hidden email]
Subject: Activity Between Dates

Hello,

I am having difficulty trying to come up with syntax that will produce
what I need.  I need to calculate activity hours between dates when
clients can participate in more than one activity in a given time
period.

The results I need using the data below are:

Client 1 participated for 80 hours from 9/1/06 to 9/21/06 and for 10
hours
9/25/06 to 9/30/06.

Client 2 participated for 50 hours from 9/1/06 and 9/14/06.

Client 3 participated for 80 hours from 9/1/06 and 9/30/06...and so on.

Here is the data:

DATA LIST /ID 1
   ACT1ST 3-10 (ADATE)  ACT1END 12-19 (ADATE) ACT1HRS 21-22
   ACT2ST 24-31 (ADATE) ACT2END 33-40 (ADATE) ACT2HRS 42-43
   ACT3ST 45-52 (ADATE) ACT3END 54-61 (ADATE) ACT3HRS 63-64.
BEGIN DATA
1 09/01/06 09/14/06 40 09/07/06 09/21/06 40 09/25/06 09/30/06 10
2 09/01/06 09/14/06 40 09/05/06 09/12/06 10
3 09/01/06 09/08/06 20 09/09/06 09/30/06 60
4 09/01/06 09/05/06 20 09/21/06 09/30/06 60
5 09/18/06 09/30/06 90
END DATA.

Please note that clients can have between 1 and 9 activities in a given
month and activities can be performed concurrently.

I would appreciate any help I can get.

Thank you,
Craig Kahklen

[Kahklen, Craig]

Hi Melissa,

Thank you for helping.

What I need to do is combine the hours of participation in unbroken spells
of activity, regardless of how many different activities are performed
within a spell.

For instance, client 1 in the data below participated in an activity from
9/1/06 to 9/14/06 and in another concurrent activity from 9/7/06 to 9/21/06,
I would count that as one spell from 9/1/06 to 9/21/06 and sum the hours.
Client 1 also had a break in participation then participated in another
activity 9/25/06 to 9/30/06.  I need to count that as a second "spell" of
activity.

Also, if one activity begins the day after another ends, I need to combine
and report those as one spell.  9/1/06 to 9/5/06 and 9/6/06 to 9/14/06 =
9/1/06 to 9/14/06.

I need syntax that will take in to account any bizarre mixture of
participation you can imagine...from participating for the entire month in a
single activity to participating in multiple activities where some start and
end dates overlap and multiple breaks in activity occurs.

It gets very complicated.

Thanks for any help you can offer,
Craig

-----Original Message-----
From: Melissa Ives [mailto:[hidden email]]
Sent: Tuesday, November 28, 2006 9:38 AM
To: Kahklen, Craig; [hidden email]
Subject: RE: [SPSSX-L] Activity Between Dates


How will you know which activities to combine at which times?  Will you
always combine activity 1 and 2, but keep 3 separate?  You have
different time periods for each client--what is the rule for reporting
these for each client?

You could compute a min(ACT1ST,ACT2ST...) to max(ACT1END,ACT2END...) to
compute the widest range, then sum the appropriate activity hours.

Melissa

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Kahklen, Craig
Sent: Tuesday, November 28, 2006 12:21 PM
To: [hidden email]
Subject: [SPSSX-L] Activity Between Dates

Hello,

I am having difficulty trying to come up with syntax that will produce
what I need.  I need to calculate activity hours between dates when
clients can participate in more than one activity in a given time
period.

The results I need using the data below are:

Client 1 participated for 80 hours from 9/1/06 to 9/21/06 and for 10
hours
9/25/06 to 9/30/06.

Client 2 participated for 50 hours from 9/1/06 and 9/14/06.

Client 3 participated for 80 hours from 9/1/06 and 9/30/06...and so on.

Here is the data:

DATA LIST /ID 1
   ACT1ST 3-10 (ADATE)  ACT1END 12-19 (ADATE) a 21-22
   ACT2ST 24-31 (ADATE) ACT2END 33-40 (ADATE) ACT2HRS 42-43
   ACT3ST 45-52 (ADATE) ACT3END 54-61 (ADATE) ACT3HRS 63-64.
BEGIN DATA
1 09/01/06 09/14/06 40 09/07/06 09/21/06 40 09/25/06 09/30/06 10
2 09/01/06 09/14/06 40 09/05/06 09/12/06 10
3 09/01/06 09/08/06 20 09/09/06 09/30/06 60
4 09/01/06 09/05/06 20 09/21/06 09/30/06 60
5 09/18/06 09/30/06 90
END DATA.

Please note that clients can have between 1 and 9 activities in a given
month and activities can be performed concurrently.

I would appreciate any help I can get.

Thank you,
Craig Kahklen



PRIVILEGED AND CONFIDENTIAL INFORMATION
This transmittal and any attachments may contain PRIVILEGED AND
CONFIDENTIAL information and is intended only for the use of the
addressee. If you are not the designated recipient, or an employee
or agent authorized to deliver such transmittals to the designated
recipient, you are hereby notified that any dissemination,
copying or publication of this transmittal is strictly prohibited. If
you have received this transmittal in error, please notify us
immediately by replying to the sender and delete this copy from your
system. You may also call us at [phone] for assistance.

[Dennis Deck]

Not sure we have enough info yet about your data or your need but
conceptually I would do something like to "roll up" the episodes:

1) order the data:  SORT CASES BY ClientID StartDate EndDate .

2) use LAG() function in Ifs, DO Ifs, or COMPUTEs to determine if this
record is a continuation by your criteria.
- If not a continuation, increment episode
- if new client, reset episode

Tip: For record 1 there is no prior record so LAG() yields missing, thus
I would do something like:

DO IF (#casenum=1 or ID=LAG(ID)) .
+ COMPUTE Episode = 1 .
ELSE IF (StartDate<LAG(EndDate)) .
+ COMPUTE Episode = Episode + 1 .
END IF .

3) use AGGREGATE to create the rolled up data set, breaking on client
and episode, summing hrs.  /TotHrs = SUM(Hrs) .


Dennis Deck, PhD
RMC Research Corporation
[hidden email]

-----Original Message-----
From: Kahklen, Craig [mailto:[hidden email]]
Sent: Wednesday, November 29, 2006 9:47 AM
Subject: Re: Activity Between Dates

Hi Melissa,

Thank you for helping.

What I need to do is combine the hours of participation in unbroken
spells
of activity, regardless of how many different activities are performed
within a spell.

For instance, client 1 in the data below participated in an activity
from
9/1/06 to 9/14/06 and in another concurrent activity from 9/7/06 to
9/21/06,
I would count that as one spell from 9/1/06 to 9/21/06 and sum the
hours.
Client 1 also had a break in participation then participated in another
activity 9/25/06 to 9/30/06.  I need to count that as a second "spell"
of
activity.

Also, if one activity begins the day after another ends, I need to
combine
and report those as one spell.  9/1/06 to 9/5/06 and 9/6/06 to 9/14/06 =
9/1/06 to 9/14/06.

I need syntax that will take in to account any bizarre mixture of
participation you can imagine...from participating for the entire month
in a
single activity to participating in multiple activities where some start
and
end dates overlap and multiple breaks in activity occurs.

It gets very complicated.

Thanks for any help you can offer,
Craig

-----Original Message-----
From: Melissa Ives [mailto:[hidden email]]
Sent: Tuesday, November 28, 2006 9:38 AM
To: Kahklen, Craig; [hidden email]
Subject: RE: [SPSSX-L] Activity Between Dates


How will you know which activities to combine at which times?  Will you
always combine activity 1 and 2, but keep 3 separate?  You have
different time periods for each client--what is the rule for reporting
these for each client?

You could compute a min(ACT1ST,ACT2ST...) to max(ACT1END,ACT2END...) to
compute the widest range, then sum the appropriate activity hours.

Melissa

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Kahklen, Craig
Sent: Tuesday, November 28, 2006 12:21 PM
To: [hidden email]
Subject: [SPSSX-L] Activity Between Dates

Hello,

I am having difficulty trying to come up with syntax that will produce
what I need.  I need to calculate activity hours between dates when
clients can participate in more than one activity in a given time
period.

The results I need using the data below are:

Client 1 participated for 80 hours from 9/1/06 to 9/21/06 and for 10
hours
9/25/06 to 9/30/06.

Client 2 participated for 50 hours from 9/1/06 and 9/14/06.

Client 3 participated for 80 hours from 9/1/06 and 9/30/06...and so on.

Here is the data:

DATA LIST /ID 1
   ACT1ST 3-10 (ADATE)  ACT1END 12-19 (ADATE) a 21-22
   ACT2ST 24-31 (ADATE) ACT2END 33-40 (ADATE) ACT2HRS 42-43
   ACT3ST 45-52 (ADATE) ACT3END 54-61 (ADATE) ACT3HRS 63-64.
BEGIN DATA
1 09/01/06 09/14/06 40 09/07/06 09/21/06 40 09/25/06 09/30/06 10
2 09/01/06 09/14/06 40 09/05/06 09/12/06 10
3 09/01/06 09/08/06 20 09/09/06 09/30/06 60
4 09/01/06 09/05/06 20 09/21/06 09/30/06 60
5 09/18/06 09/30/06 90
END DATA.

Please note that clients can have between 1 and 9 activities in a given
month and activities can be performed concurrently.

I would appreciate any help I can get.

Thank you,
Craig Kahklen



PRIVILEGED AND CONFIDENTIAL INFORMATION
This transmittal and any attachments may contain PRIVILEGED AND
CONFIDENTIAL information and is intended only for the use of the
addressee. If you are not the designated recipient, or an employee
or agent authorized to deliver such transmittals to the designated
recipient, you are hereby notified that any dissemination,
copying or publication of this transmittal is strictly prohibited. If
you have received this transmittal in error, please notify us
immediately by replying to the sender and delete this copy from your
system. You may also call us at [phone] for assistance.

[Marks, Jim]

Dennis:

This is the technique I used in my reply on 11/28/2006. (The data needed
to be restructured from wide to long first.)

As far as I know the OP has a solution.

--jim



-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Dennis Deck
Sent: Thursday, November 30, 2006 2:00 AM
To: [hidden email]
Subject: Re: Activity Between Dates

Not sure we have enough info yet about your data or your need but
conceptually I would do something like to "roll up" the episodes:

1) order the data:  SORT CASES BY ClientID StartDate EndDate .

2) use LAG() function in Ifs, DO Ifs, or COMPUTEs to determine if this
record is a continuation by your criteria.
- If not a continuation, increment episode
- if new client, reset episode

Tip: For record 1 there is no prior record so LAG() yields missing, thus
I would do something like:

DO IF (#casenum=1 or ID=LAG(ID)) .
+ COMPUTE Episode = 1 .
ELSE IF (StartDate<LAG(EndDate)) .
+ COMPUTE Episode = Episode + 1 .
END IF .

3) use AGGREGATE to create the rolled up data set, breaking on client
and episode, summing hrs.  /TotHrs = SUM(Hrs) .


Dennis Deck, PhD
RMC Research Corporation
[hidden email]

-----Original Message-----
From: Kahklen, Craig [mailto:[hidden email]]
Sent: Wednesday, November 29, 2006 9:47 AM
Subject: Re: Activity Between Dates

Hi Melissa,

Thank you for helping.

What I need to do is combine the hours of participation in unbroken
spells of activity, regardless of how many different activities are
performed within a spell.

For instance, client 1 in the data below participated in an activity
from
9/1/06 to 9/14/06 and in another concurrent activity from 9/7/06 to
9/21/06, I would count that as one spell from 9/1/06 to 9/21/06 and sum
the hours.
Client 1 also had a break in participation then participated in another
activity 9/25/06 to 9/30/06.  I need to count that as a second "spell"
of
activity.

Also, if one activity begins the day after another ends, I need to
combine and report those as one spell.  9/1/06 to 9/5/06 and 9/6/06 to
9/14/06 =
9/1/06 to 9/14/06.

I need syntax that will take in to account any bizarre mixture of
participation you can imagine...from participating for the entire month
in a single activity to participating in multiple activities where some
start and end dates overlap and multiple breaks in activity occurs.

It gets very complicated.

Thanks for any help you can offer,
Craig

-----Original Message-----
From: Melissa Ives [mailto:[hidden email]]
Sent: Tuesday, November 28, 2006 9:38 AM
To: Kahklen, Craig; [hidden email]
Subject: RE: [SPSSX-L] Activity Between Dates


How will you know which activities to combine at which times?  Will you
always combine activity 1 and 2, but keep 3 separate?  You have
different time periods for each client--what is the rule for reporting
these for each client?

You could compute a min(ACT1ST,ACT2ST...) to max(ACT1END,ACT2END...) to
compute the widest range, then sum the appropriate activity hours.

Melissa

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Kahklen, Craig
Sent: Tuesday, November 28, 2006 12:21 PM
To: [hidden email]
Subject: [SPSSX-L] Activity Between Dates

Hello,

I am having difficulty trying to come up with syntax that will produce
what I need.  I need to calculate activity hours between dates when
clients can participate in more than one activity in a given time
period.

The results I need using the data below are:

Client 1 participated for 80 hours from 9/1/06 to 9/21/06 and for 10
hours
9/25/06 to 9/30/06.

Client 2 participated for 50 hours from 9/1/06 and 9/14/06.

Client 3 participated for 80 hours from 9/1/06 and 9/30/06...and so on.

Here is the data:

DATA LIST /ID 1
   ACT1ST 3-10 (ADATE)  ACT1END 12-19 (ADATE) a 21-22
   ACT2ST 24-31 (ADATE) ACT2END 33-40 (ADATE) ACT2HRS 42-43
   ACT3ST 45-52 (ADATE) ACT3END 54-61 (ADATE) ACT3HRS 63-64.
BEGIN DATA
1 09/01/06 09/14/06 40 09/07/06 09/21/06 40 09/25/06 09/30/06 10
2 09/01/06 09/14/06 40 09/05/06 09/12/06 10
3 09/01/06 09/08/06 20 09/09/06 09/30/06 60
4 09/01/06 09/05/06 20 09/21/06 09/30/06 60
5 09/18/06 09/30/06 90
END DATA.

Please note that clients can have between 1 and 9 activities in a given
month and activities can be performed concurrently.

I would appreciate any help I can get.

Thank you,
Craig Kahklen



PRIVILEGED AND CONFIDENTIAL INFORMATION
This transmittal and any attachments may contain PRIVILEGED AND
CONFIDENTIAL information and is intended only for the use of the
addressee. If you are not the designated recipient, or an employee or
agent authorized to deliver such transmittals to the designated
recipient, you are hereby notified that any dissemination, copying or
publication of this transmittal is strictly prohibited. If you have
received this transmittal in error, please notify us immediately by
replying to the sender and delete this copy from your system. You may
also call us at [phone] for assistance.