Active participants?

[DKUKEC]

 * Using the syntax on this site to generate a “census day” dataset has been extremely useful to generate average daily population, admissions, and exits from programming.  
 * I wanted to compute the number of people “still in the facility” (variable ACTIVE) on the last day of a reporting period – in this case YEAR and MONTH (e.g., 31-MAR-2014) –
only if they are still in the program the following day.  However, I would like to exclude them from the count if they are no longer in the facility after this date (e.g., 31-MAR-2014).  In this
example id 42 admissionid 6 is counted as active on the last year (2014) and month (3), I would like to exclude this person from the count as they are not in the facility on 01-APR-2014.
*Any suggestions would be much appreciated, please let me know if you have any questions or require clarification.  I have included a sample dataset and my syntax.

*Thank you
Damir


DATA LIST FREE / ID (F4) ADMISSIONID (F4) STARTDATE (DATE11) ENDDATE (DATE11) .
BEGIN DATA.
          21      1.00   01-OCT-2013 30-OCT-2013
          41      3.00   01-OCT-2013 30-DEC-2013
          42      5.00   01-OCT-2013 12-DEC-2013
          43      7.00   01-OCT-2013 30-OCT-2013
          44      9.00   01-OCT-2013 30-OCT-2013
          44     10.00   30-OCT-2013 03-MAR-2014
          21      2.00   01-NOV-2013 14-APR-2014
          42      6.00   01-DEC-2013 31-MAR-2014
          43      8.00   01-JAN-2014 14-APR-2014
          41      4.00   01-FEB-2014 14-APR-2014
END DATA.
DATASET NAME PRIMARY WINDOW=FRONT .

NUMERIC TODAY (DATE11)
   /STARTDAY ENDDAY (F2).

VARIABLE LABELS
    TODAY "Date: within program stay"
    STARTDAY "Flag: Participant started this date"
    ENDDAY "Flag: Participant ended this date".
FORMATS TODAY (DATE11).
EXECUTE.

**************************************************************************************************************************************************************************************************** .

LOOP  TODAY = STARTDATE to ENDDATE BY TIME.DAYS(1).
  COMPUTE STARTDAY = (TODAY EQ STARTDATE).
  COMPUTE ENDDAY = (TODAY EQ ENDDATE).
  XSAVE OUTFILE='C:\USERS\CENSUSDAYS.SAV'
   /KEEP= ID ADMISSIONID STARTDATE ENDDATE TODAY STARTDAY ENDDAY .
END LOOP.
EXECUTE  /* this one is needed */.
GET FILE='C:\USERS\CENSUSDAYS.SAV'.
DATASET NAME CENSUSDAYS WINDOW=FRONT.
COMPUTE YEAR=XDATE.YEAR (TODAY).
COMPUTE MONTH=XDATE.MONTH (TODAY).
VARIABLE LABELS YEAR "Year" MONTH "Month".
* COMPUTES QUARTERS -COUNTY FISCAL .
RECODE MONTH (10 thru 12=4) (1 thru 3=1) (4 thru 6=2) (7 thru 9=3) INTO QUARTER.
VARIABLE LABELS  QUARTER 'Fiscal Quarters (SPSS)'.
EXECUTE.
VALUE LABELS QUARTER
1"January, February, & March"
2"April, May, & June"
3"July, August, & September"
4"October, November, & December" .
EXECUTE.

**************************************************************************************************************************************************************************************************** .

*CENSUS DAYS .
CROSSTABS YEAR BY MONTH .
* Identify Duplicate Cases.
SORT CASES BY TODAY (A).
MATCH FILES
  /FILE=*
  /BY TODAY
  /FIRST=PrimaryFirst
  /LAST=PrimaryLast.
DO IF (PrimaryFirst).
COMPUTE  MatchSequence=1-PrimaryLast.
ELSE.
COMPUTE  MatchSequence=MatchSequence+1.
END IF.
LEAVE  MatchSequence.
FORMAT  MatchSequence (f7).
COMPUTE  InDupGrp=MatchSequence>0.
SORT CASES InDupGrp(D).
MATCH FILES
  /FILE=*
  /DROP=PrimaryFirst InDupGrp MatchSequence.
VARIABLE LABELS  PrimaryLast 'Indicator of each last matching case as Primary'.
VALUE LABELS  PrimaryLast 0 'Duplicate Case' 1 'Primary Case'.
VARIABLE LEVEL  PrimaryLast (ORDINAL).
FREQUENCIES VARIABLES=PrimaryLast.
RENAME VARIABLES PrimaryLast=UNIQUEDAY .
EXECUTE.

********************************************************************************************************************************************************************* .

DATASET DECLARE DAYSINMONTH .
AGGREGATE
  /OUTFILE='DAYSINMONTH'
  /BREAK=YEAR MONTH
  /DAYSINMONTH=SUM(UNIQUEDAY) .
EXECUTE.

*COUNTS THE NUMBER OF PARTICIPANTS DURING A SPECIFIC DATE (VAR=TODAY) .
AGGREGATE
  /BREAK=TODAY
  /DAILY = N .
EXECUTE.

DATASET DECLARE ADPREPORT.
AGGREGATE
  /OUTFILE='ADPREPORT'
  /BREAK=YEAR MONTH
  /ADMISSIONS=SUM (STARTDAY)
  /EXITS=SUM (ENDDAY)
  /ACTIVE = MIN (DAILY)
  /CENSUSDAYS=N.
EXECUTE .

DATASET ACTIVATE ADPREPORT .
MATCH FILES /FILE=*
  /TABLE='DAYSINMONTH'
  /BY  YEAR MONTH .
EXECUTE.

* / COMPUTES AVERAGE DAILY POPULATION /  .
COMPUTE ADP=CENSUSDAYS/DAYSINMONTH.
VARIABLE LABELS ADP "Monthly Average Daily Population".

*/ DESIGNATE DESIRED CAPACITY (N)    .
COMPUTE CAPACITYX=200.
COMPUTE CAPACITY = ADP / CAPACITYX * 100 .
VARIABLE LABELS CAPACITYX "Target Monthly Capacity (n)" .
VARIABLE LABELS CAPACITY "Monthly Capacity (%)".

VARIABLE LABELS ADMISSIONS "Monthly Admissions (n)" .
VARIABLE LABELS EXITS "Monthly Total Dispositions (n)"  .
VARIABLE LABELS MSERVED "Monthly Total Served (n)"  .
VARIABLE LABELS CENSUSDAYS "Monthly Census Days (n)"  .
VARIABLE LABELS DAYSINMONTH "Monthly Active Days (n)"  .
EXECUTE.

DATASET CLOSE DAYSINMONTH .

LIST YEAR MONTH ADMISSIONS EXITS ACTIVE .

[David Marso]

Perhaps study the AGGREGATE command.
MODE=ADDVARIABLES and MAX function will be handy.
Other than that you should post focused code rather than your entire program.
I spend at most 5 minutes on any particular stranger's question.  
Reading through all your code would require far more time than I am willing to devote!
---

[Richard Ristow]

Here's one I've been meaning to respond to ---

At 05:17 PM 6/4/2014, DKUKEC wrote:

>  * I wanted to compute the number of people
> *still in the facility* (variable ACTIVE) on
> the last day of a reporting period ­ in this
> case YEAR and MONTH (e.g., 31-MAR-2014) ­ only
> if they are still in the program the following
> day.  However, I would like to exclude them
> from the count if they are no longer in the
> facility after this date (e.g., 31-MAR-2014).

So, people are 'in the facility' from their
STARTDATE up to, but not including, their ENDDATE.

And you want to count those 'in the facility' at
the end of a certain reporting period. It's not
clear from your note or your code whether you
want to count by calendar month or calendar
quarter. The following counts by calendar month
-- gives total in the facility (by the above
definition) on the last day of each calendar
month. (As a bonus, it gives those in the
facility on *any* day of the calendar month, and
total patient-days in the facility that calendar month.)

I didn't modify your code. What's below is
modified from earlier solutions I've posted. Like
yours, it uses LOOP and XSAVE; but it loops by
month, rather than by day. Here's your test data:
|-----------------------------|---------------------------|
|Output Created               |30-JUN-2014 16:53:44       |
|-----------------------------|---------------------------|
  [PRIMARY]
   ID ADMISSIONID   STARTDATE     ENDDATE

   21        1    01-OCT-2013 30-OCT-2013
   41        3    01-OCT-2013 30-DEC-2013
   42        5    01-OCT-2013 12-DEC-2013
   43        7    01-OCT-2013 30-OCT-2013
   44        9    01-OCT-2013 30-OCT-2013
   44       10    30-OCT-2013 03-MAR-2014
   21        2    01-NOV-2013 14-APR-2014
   42        6    01-DEC-2013 31-MAR-2014
   43        8    01-JAN-2014 14-APR-2014
   41        4    01-FEB-2014 14-APR-2014

Number of cases read:  10    Number of cases listed:  10


Here's the summary created. Note that there were
three patients who were active during April 2014,
but none active at the *end* of that month:
|-----------------------------|---------------------------|
|Output Created               |30-JUN-2014 16:53:45       |
|-----------------------------|---------------------------|
  [MonthSmry]

    Month   NPats PatDays ACTIVE

OCT 2013       6    145      3
NOV 2013       4    116      4
DEC 2013       5    128      3
JAN 2014       4    120      4
FEB 2014       5    135      5
MAR 2014       5    120      3
APR 2014       3     36      0

Number of cases read:  7    Number of cases listed:  7
......................................................
And, below is the logic, including a listing of
the statistics by month for each individual admission. Does this do it for you?

DATASET COPY     Working.
DATASET ACTIVATE Working  WINDOW=FRONT.
*   New variables for the output file, breaking down statistics by   .
*   month for each admission:                                        .

NUMERIC   Month    (MOYR8).
NUMERIC   ActDays  (F3).
NUMERIC   ACTIVE   (F2).

VAR LABEL Month    'Calendar month'
           ActDays  'Days active, during calendar month'
           ACTIVE   'Active at end of calendar month?'.

VAL LABEL ACTIVE  (1) 'Active'
                   (0) 'Inactive'.

*  Scratch variables:                                            ... .

*  . First and last active dates, of this admission:             ... .
NUMERIC  #FrstActv #LastActv (DATE11).

*  . Last day, of the current calendar month:                    ... .
NUMERIC  #EndMon  (DATE11).

*  . Next calendar month (and, its first day):                   ... .
NUMERIC  #NextMon (MOYR8).

COMPUTE  #FrstActv=XDATE.DATE(STARTDATE).
COMPUTE  #LastActv=XDATE.DATE(ENDDATE)  - TIME.DAYS(1).

COMPUTE Month = DATE.MOYR(XDATE.MONTH(#FrstActv),
                           XDATE.YEAR (#FrstActv)).


LOOP.
.  COMPUTE #NextMon = DATESUM(Month,    1,"Month").
.  COMPUTE #EndMon  = DATESUM(#NextMon,-1,"Day").

.  COMPUTE ActDays  = CTIME.DAYS(MIN(#EndMon,#LastActv)
                                 -MAX(Month,  #FrstActv)).

.  COMPUTE ACTIVE   = RANGE(#EndMon,#FrstActv,#LastActv).


.  XSAVE   OUTFILE  = ByMonth.

.  COMPUTE Month    = #NextMon.
END LOOP IF Month GT  #LastActv.

EXECUTE /* necessary, here *.

GET FILE = ByMonth.

DATASET NAME ByMonthDS.
LIST.

List
|-----------------------------|---------------------------|
|Output Created               |30-JUN-2014 16:53:45       |
|-----------------------------|---------------------------|
  [ByMonthDS] C:\Documents and Settings\Richard\My Documents
                \Temporary\SPSS\
                 2014-06-04 DKUKEC-Active participants - ByMonth.SAV

   ID ADMISSIONID   STARTDATE     ENDDATE    Month ActDays ACTIVE

   21        1    01-OCT-2013 30-OCT-2013 OCT 2013    28      0
   41        3    01-OCT-2013 30-DEC-2013 OCT 2013    30      1
   41        3    01-OCT-2013 30-DEC-2013 NOV 2013    29      1
   41        3    01-OCT-2013 30-DEC-2013 DEC 2013    28      0
   42        5    01-OCT-2013 12-DEC-2013 OCT 2013    30      1
   42        5    01-OCT-2013 12-DEC-2013 NOV 2013    29      1
   42        5    01-OCT-2013 12-DEC-2013 DEC 2013    10      0
   43        7    01-OCT-2013 30-OCT-2013 OCT 2013    28      0
   44        9    01-OCT-2013 30-OCT-2013 OCT 2013    28      0
   44       10    30-OCT-2013 03-MAR-2014 OCT 2013     1      1
   44       10    30-OCT-2013 03-MAR-2014 NOV 2013    29      1
   44       10    30-OCT-2013 03-MAR-2014 DEC 2013    30      1
   44       10    30-OCT-2013 03-MAR-2014 JAN 2014    30      1
   44       10    30-OCT-2013 03-MAR-2014 FEB 2014    27      1
   44       10    30-OCT-2013 03-MAR-2014 MAR 2014     1      0
   21        2    01-NOV-2013 14-APR-2014 NOV 2013    29      1
   21        2    01-NOV-2013 14-APR-2014 DEC 2013    30      1
   21        2    01-NOV-2013 14-APR-2014 JAN 2014    30      1
   21        2    01-NOV-2013 14-APR-2014 FEB 2014    27      1
   21        2    01-NOV-2013 14-APR-2014 MAR 2014    30      1
   21        2    01-NOV-2013 14-APR-2014 APR 2014    12      0
   42        6    01-DEC-2013 31-MAR-2014 DEC 2013    30      1
   42        6    01-DEC-2013 31-MAR-2014 JAN 2014    30      1
   42        6    01-DEC-2013 31-MAR-2014 FEB 2014    27      1
   42        6    01-DEC-2013 31-MAR-2014 MAR 2014    29      0
   43        8    01-JAN-2014 14-APR-2014 JAN 2014    30      1
   43        8    01-JAN-2014 14-APR-2014 FEB 2014    27      1
   43        8    01-JAN-2014 14-APR-2014 MAR 2014    30      1
   43        8    01-JAN-2014 14-APR-2014 APR 2014    12      0
   41        4    01-FEB-2014 14-APR-2014 FEB 2014    27      1
   41        4    01-FEB-2014 14-APR-2014 MAR 2014    30      1
   41        4    01-FEB-2014 14-APR-2014 APR 2014    12      0

Number of cases read:  32    Number of cases listed:  32


DATASET   DECLARE  MonthSmry.
AGGREGATE OUTFILE =MonthSmry
    /BREAK=Month
    /NPats   'No. of patients active any day this month' = NU
    /PatDays 'Total patient-days this month'             = SUM(ActDays)
    /ACTIVE  'Admissions active at end of month'         = SUM(ACTIVE).

DATASET   ACTIVATE MonthSmry.
FORMATS   PatDays (F5)
           ACTIVE  (F3).

LIST.

List
|-----------------------------|---------------------------|
|Output Created               |30-JUN-2014 16:53:45       |
|-----------------------------|---------------------------|
  [MonthSmry]

    Month   NPats PatDays ACTIVE

OCT 2013       6    145      3
NOV 2013       4    116      4
DEC 2013       5    128      3
JAN 2014       4    120      4
FEB 2014       5    135      5
MAR 2014       5    120      3
APR 2014       3     36      0

Number of cases read:  7    Number of cases listed:  7

================================
APPENDIX II: Test data and all code
================================
*  C:\Documents and Settings\Richard\My Documents  .
*    \Technical\spssx-l\Z-2014\                    .
*    2014-06-04 DKUKEC-Active participants.SPS     .

*  In response to posting                          .
*  Date:    Wed, 4 Jun 2014 14:17:17 -0700         .
*  From:    DKUKEC <[hidden email]>             .
*  Subject: Active participants?                   .
*  To:      [hidden email]               .

*  " * I wanted to compute the number of people 'still in the         .
*  facility' (variable ACTIVE) on the last day of a reporting period  .
*  ­ in this case YEAR and MONTH (e.g., 31-MAR-2014) ­ only if they     .
*  are still in the program the following day.  However, I would like .
*  to exclude them from the count if they are no longer in the        .
*  facility after this date (e.g., 31-MAR-2014).  In this example id  .
*  42 admissionid 6 is counted as active on the last year (2014) and  .
*  month (3), I would like to exclude this person from the count as   .
*  they are not in the facility on 01-APR-2014."                      .

FILE HANDLE ByMonth
  /NAME='C:\Documents and Settings\Richard\My Documents'             +
          '\Temporary\SPSS\'                                         +
        '2014-06-04 DKUKEC-Active participants'                      +
        ' - '                                                        +
        'ByMonth.SAV'.

DATA LIST LIST /
          ID  ADMISSIONID  STARTDATE  ENDDATE
         (F4,      F4,     DATE11,    DATE11).
BEGIN DATA.
           21      1.00   01-OCT-2013 30-OCT-2013
           41      3.00   01-OCT-2013 30-DEC-2013
           42      5.00   01-OCT-2013 12-DEC-2013
           43      7.00   01-OCT-2013 30-OCT-2013
           44      9.00   01-OCT-2013 30-OCT-2013
           44     10.00   30-OCT-2013 03-MAR-2014
           21      2.00   01-NOV-2013 14-APR-2014
           42      6.00   01-DEC-2013 31-MAR-2014
           43      8.00   01-JAN-2014 14-APR-2014
           41      4.00   01-FEB-2014 14-APR-2014
END DATA.
DATASET NAME     PRIMARY WINDOW=FRONT .
LIST.

DATASET COPY     Working.
DATASET ACTIVATE Working  WINDOW=FRONT.

*   New variables for the output file, breaking down statistics by   .
*   month for each admission:                                        .

NUMERIC   Month    (MOYR8).
NUMERIC   ActDays  (F3).
NUMERIC   ACTIVE   (F2).

VAR LABEL Month    'Calendar month'
           ActDays  'Days active, during calendar month'
           ACTIVE   'Active at end of calendar month?'.

VAL LABEL ACTIVE  (1) 'Active'
                   (0) 'Inactive'.

*  Scratch variables:                                            ... .

*  . First and last active dates, of this admission:             ... .
NUMERIC  #FrstActv #LastActv (DATE11).

*  . Last day, of the current calendar month:                    ... .
NUMERIC  #EndMon  (DATE11).

*  . Next calendar month (and, its first day):                   ... .
NUMERIC  #NextMon (MOYR8).

COMPUTE  #FrstActv=XDATE.DATE(STARTDATE).
COMPUTE  #LastActv=XDATE.DATE(ENDDATE)  - TIME.DAYS(1).

COMPUTE Month = DATE.MOYR(XDATE.MONTH(#FrstActv),
                           XDATE.YEAR (#FrstActv)).


LOOP.
.  COMPUTE #NextMon = DATESUM(Month,    1,"Month").
.  COMPUTE #EndMon  = DATESUM(#NextMon,-1,"Day").

.  COMPUTE ActDays  = CTIME.DAYS(MIN(#EndMon,#LastActv)
                                 -MAX(Month,  #FrstActv)).

.  COMPUTE ACTIVE   = RANGE(#EndMon,#FrstActv,#LastActv).


.  XSAVE   OUTFILE  = ByMonth.

.  COMPUTE Month    = #NextMon.
END LOOP IF Month GT  #LastActv.

EXECUTE /* necessary, here *.

GET FILE = ByMonth.

DATASET NAME ByMonthDS.

LIST.


DATASET   DECLARE  MonthSmry.
AGGREGATE OUTFILE =MonthSmry
    /BREAK=Month
    /NPats   'No. of patients active any day this month' = NU
    /PatDays 'Total patient-days this month'             = SUM(ActDays)
    /ACTIVE  'Admissions active at end of month'         = SUM(ACTIVE).

DATASET   ACTIVATE MonthSmry.

FORMATS   PatDays (F5)
           ACTIVE  (F3).

LIST.

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

[Richard Ristow]

At 05:03 PM 6/30/2014, I posted a solution. I've just noticed one
bug; it affects the count of total days, but not whether the patient
is active at the end of the month.

The statement

.  COMPUTE ActDays  = CTIME.DAYS(MIN(#EndMon,#LastActv)
                                 -MAX(Month,  #FrstActv)).

should be

.  COMPUTE ActDays  = 1
                     + CTIME.DAYS(MIN(#EndMon,#LastActv)
                                 -MAX(Month,  #FrstActv)).


That is, the length of time is the inclusive interval, whose length
is one day more than the difference of the dates.

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