Counting Consecutive Dates or Gaps in dates

[Hughes, John]

I have one record per client per month but some have stops and restarts.
I would like to count either how many gaps there are in service or the
number of months of service that are consecutive. Any help would be
appreciated!

 

John Hughes, Ph.D.

Director, Decision Support Services

Office of Early Learning

Agency for Workforce Innovation

107 East Madison Street

MSC 140

Tallahassee, FL 32399

850.921.3471 (Office)

850.414.2434 (Fax)

 

Please note: Florida has a very broad public records law.  Most written
communications to or from state officials regarding state business are
public records available to the public and media upon request.  Your
e-mail communications may therefore be subject to public disclosure.

 

[Mahbub Khandoker]

Hi John,
I am not sure how your dataset is designed. So I assume the following dataset for five months and calculate gapes for each client id.

DATA LIST free/ id(n2) month(n2).
Begin data
1 1
1 2
1 3
1 4
1 5
2 1
2 3
2 4
3 2
3 3
3 4
3 5
4 1
4 2
4 4
4 5
End data.

CASESTOVARS
/ID=id.

COUNT
service = month.1 to month.5 (1 thru 5).

Compute Gaps=5-Service.
Exe.

Hopefully some one from the list comes up with more efficient way to do this.
Thanks,
Mahbub

Mahbub Khandoker
IT/DS Coordinator
IMG-Decision Support
Tel: [phone] Ex 6534
Fax: [phone]

 -----Original Message-----
From:   SPSSX(r) Discussion [mailto:[hidden email]]  On Behalf Of Hughes, John
Sent:   4-Apr-07 10:18 AM
To:     [hidden email]
Subject:             Counting Consecutive Dates or Gaps in dates

I have one record per client per month but some have stops and restarts.
I would like to count either how many gaps there are in service or the
number of months of service that are consecutive. Any help would be
appreciated!



John Hughes, Ph.D.

Director, Decision Support Services

Office of Early Learning

Agency for Workforce Innovation

107 East Madison Street

MSC 140

Tallahassee, FL 32399

850.921.3471 (Office)

850.414.2434 (Fax)



Please note: Florida has a very broad public records law.  Most written
communications to or from state officials regarding state business are
public records available to the public and media upon request.  Your
e-mail communications may therefore be subject to public disclosure.

[Richard Ristow]

At 10:17 AM 4/4/2007, Hughes, John wrote:

>I have one record per client per month but some have stops and
>restarts. I would like to count either how many gaps there are in
>service or the number of months of service that are consecutive.

Might as well count both. This is a pure 'long' solution, i.e. keeping
months in separate cases; there is, therefore, no particular
restriction on the length of history for one case. This is SPSS 15
draft output (WRR-not saved separately):

|-----------------------------|---------------------------|
|Output Created               |04-APR-2007 13:56:52       |
|-----------------------------|---------------------------|
  id month

001   01
001   02
001   03
001   04
001   05
002   01
002   03
002   04
003   02
003   03
003   04
003   05
004   01
004   02
004   04
004   05

Number of cases read:  16    Number of cases listed:  16


NUMERIC  GAP   (F3)
         /CONSEC(F4).
VAR LABEL
     GAP    'Month immediately follows a gap in service'
     CONSEC 'Months of consecutive svc, thru this one'.

DO IF    MISSING(LAG(ID))
       OR         LAG(ID) NE ID.
.  COMPUTE  GAP    = 0.
.  COMPUTE  CONSEC = 1.
ELSE IF  MONTH EQ LAG(MONTH)     + 1.
.  COMPUTE  GAP    = 0.
.  COMPUTE  CONSEC = LAG(CONSEC) + 1.
ELSE.
.  COMPUTE  GAP    = 1.
.  COMPUTE  CONSEC = 1.
END IF.
LIST.

List
|-----------------------------|---------------------------|
|Output Created               |04-APR-2007 13:56:53       |
|-----------------------------|---------------------------|
  id month GAP CONSEC

001   01    0     1
001   02    0     2
001   03    0     3
001   04    0     4
001   05    0     5
002   01    0     1
002   03    1     1
002   04    0     2
003   02    0     1
003   03    0     2
003   04    0     3
003   05    0     4
004   01    0     1
004   02    0     2
004   04    1     1
004   05    0     2

Number of cases read:  16    Number of cases listed:  16


AGGREGATE OUTFILE=*
    /BREAK = ID
    /GAPS   'Number of gaps in svc' = SUM(GAP)
    /CONSEC 'Longest consecutive period of service' = MAX(CONSEC).
FORMATS GAPS  CONSEC (F3).
LIST.

List
|-----------------------------|---------------------------|
|Output Created               |04-APR-2007 13:56:53       |
|-----------------------------|---------------------------|
  id GAPS CONSEC

001    0     5
002    1     2
003    0     4
004    1     2

Number of cases read:  4    Number of cases listed:  4
======================================
APPENDIX: Test data (Mahbub Khandoker)
======================================
DATA LIST free/ id(n2) month(n2).
Begin data
1 1
1 2
1 3
1 4
1 5
2 1
2 3
2 4
3 2
3 3
3 4
3 5
4 1
4 2
4 4
4 5
End data.
FORMATS ID    (N3)
        /MONTH (N2).
SORT CASES BY ID MONTH.
LIST.