Month Flag based on Date Range

[Zeda]

Hello List Members.

Here is my problem. I have to provide trends of bed use by how many clients were using a bed in each given month during those six years. So I need syntax to create 72 month variables and then have a 1 in each month variable the client was using a bed.

In the data below, I have drawn a handful of records from a 75,000 record file an an example. In record 1, there would be a 1 in Month only. In record 2 there would be a 1 in Months 1 – 10 and in record 6 there would be only a 1 in Month 72.

I can create the months variables – that isn’t the problem. But how do I get a 1 in the corresponding month variable?

DO REPEAT flag=Month1 TO Month72  
/value=0 TO 71.

Clientid   sdate                              edate
C1001 1-Jan-2009 18:00:00 1-Jan-2009 19:12:00
C1002 1-Jan-2009 19:26:00 20-Oct-2009 09:00:00
C1003 5-May-2006 20:17:00 23-Jun-2006 06:00:00
C1004 5-May-2006 20:24:00 8-May-2006 06:00:00
C1005 5-May-2006 20:28:00 5-May-2006 20:30:00
C1006 31-Dec-2014 15:00:00 31-Dec-2014 23:59:00

Thanks for your help.

Zeda

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

[Andy W]

Here is one way. Instead of using DO REPEAT I mark the first and last month in the sequence according to the sdate and edate. Then just loop over those and fill in the 1's. The IF statement is to prevent errors due to times before/after the 72 months.

*******************************************************.
DATA LIST FREE / Clientid (A5) sdate edate (2DATETIME40.0).
BEGIN DATA
C1001 "1-Jan-2009 18:00:00" "1-Jan-2009 19:12:00"
C1002 "1-Jan-2009 19:26:00" "20-Oct-2009 09:00:00"
C1003 "5-May-2006 20:17:00" "23-Jun-2006 06:00:00"
C1004 "5-May-2006 20:24:00" "8-May-2006 06:00:00"
C1005 "5-May-2006 20:28:00" "5-May-2006 20:30:00"
C1006 "31-Dec-2014 15:00:00" "31-Dec-2014 23:59:00"
END DATA.

VECTOR Month(72,F1.0).
COMPUTE #BaseMonth = DATE.MDY(12,1,2008).
COMPUTE #BeginM = DATEDIFF(sdate,#BaseMonth,"MONTHS").
COMPUTE #EndM = DATEDIFF(edate,#BaseMonth,"MONTHS").
LOOP #i = #BeginM TO #EndM.
  IF RANGE(#i,1,72) Month(#i) = 1.
END LOOP.
RECODE Month1 TO Month72 (SYSMIS = 0).
EXECUTE.
*******************************************************.

[David Marso]

Well done Andy!!
I would do only one thing extra (be on the lookout for data entry anomalies)!

Instead of:
IF RANGE(#i,1,72) Month(#i) = 1.

I would protect it with the following:

NUMERIC DATA_ERROR (F1).
RECODE DATA_ERROR (ELSE=0).
DO IF RANGE(#i,1,72) .
+  COMPUTE Month(#i) = 1.
ELSE.
+  COMPUTE DATA_ERROR =1.
END IF.
VALUE LABELS DATA_ERROR
  0 "Both date values in range"
  1 "Out of range date values".

Andy W wrote

Here is one way. Instead of using DO REPEAT I mark the first and last month in the sequence according to the sdate and edate. Then just loop over those and fill in the 1's. The IF statement is to prevent errors due to times before/after the 72 months.

*******************************************************.
DATA LIST FREE / Clientid (A5) sdate edate (2DATETIME40.0).
BEGIN DATA
C1001 "1-Jan-2009 18:00:00" "1-Jan-2009 19:12:00"
C1002 "1-Jan-2009 19:26:00" "20-Oct-2009 09:00:00"
C1003 "5-May-2006 20:17:00" "23-Jun-2006 06:00:00"
C1004 "5-May-2006 20:24:00" "8-May-2006 06:00:00"
C1005 "5-May-2006 20:28:00" "5-May-2006 20:30:00"
C1006 "31-Dec-2014 15:00:00" "31-Dec-2014 23:59:00"
END DATA.

VECTOR Month(72,F1.0).
COMPUTE #BaseMonth = DATE.MDY(12,1,2008).
COMPUTE #BeginM = DATEDIFF(sdate,#BaseMonth,"MONTHS").
COMPUTE #EndM = DATEDIFF(edate,#BaseMonth,"MONTHS").
LOOP #i = #BeginM TO #EndM.
  IF RANGE(#i,1,72) Month(#i) = 1.
END LOOP.
RECODE Month1 TO Month72 (SYSMIS = 0).
EXECUTE.
*******************************************************.

[Zeda]

David, Andy,

Thanks so much. The syntax worked beautifully. I can't begin to find the words about how much this is helping me meet my deadlines.

Really really thank you thank you.

Zeda

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

[David Marso]

You are very welcome.  Happy to be of assistance.
Just out of curiosity, did the DATA_ERROR flag come back with any 1's?
--

Zeda wrote

David, Andy,

Thanks so much. The syntax worked beautifully. I can't begin to find the words about how much this is helping me meet my deadlines.

Really really thank you thank you.

Zeda

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