Determining number of days used per month with a beginning & ending date
Locked
12
12
Re: Determining number of days used per month with a beginning & ending date
 
|
At 07:29 PM 2/4/2014, Rich Ulrich wrote:
>I'm not sure that Monday evening to Friday morning counts as 5 days. >It is 4 nights at a hotel. What are your billing rules? That's a good question, which only the original poster can answer. However, it's common for a hospital 'stay' to be counted as the beginning through the ending dates, inclusive. Further, for the special case of the beginning date being the same as the end date, the poster wrote, "if Beg date=End Date then needs to default to 1 day," which is at least consistent with an all-days-inclusive definition, and hard to fit to a different definition. If the 'stay' is the beginning through end dates inclusive, then its length is, indeed, one day more than the difference of dates. And if the 'stay' is defined any other way, it's necessary to know not just *how many* days don't count, but *which* don't. When you're distributing the days of stay among calendar months, as the original poster needs to do, counting (say) the beginning but not the ending date gives a different result from counting the ending but not the beginning date. ===================== 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 |
|
I just verified that the length of stay is the difference between the Beg Date and the End Date except if the dates are the same, then length of stay should be 1 day. My original post was incorrect when I said the length of stay should be 184, it should be 183. The simple datediff produces 183 days which is what I'm looking for. We count every day between the beg and end date except the last day.
You are correct when asking which days should not count and in which month should they not count. That would be end month. My original example Beg Date 04/05/2013 v2013.04 should have 26 days V2013.05=31 V2013.06=30 V2013.07=31 V2013.08=31 V2013.09=30 End Date 10/05/2013 v2013.10 should have 5 days-1=4=183 I haven't completely tested the last code that was posted so I'm not sure if the results are correct. |
Re: Determining number of days used per month with a beginning & ending date
|
|
At 02:34 PM 2/5/2014, ldonaghey wrote:
>I just verified that the length of stay is the difference between >the Beg Date and the End Date except if the dates are the same, then >length of stay should be 1 day. We count every day between the beg >and end date except the last day. I'm handling this by defining an interval of consecutive days, all of which are included. This interval begins with the beginning date and extends through the day *before* the ending date; except, if the beginning date and ending date are the same, the end of the interval is the ending date. (For simply calculating total length of stay, there are simpler ways; but having the stay as a set of consecutive included days is important when allocating days to months.) Here is the revised code, adding a one-day stay (id=99) to the test data. Logic to allocate patient-days among months is as in my previous posting. Listings included are, . Test data, with total length-of-stay calculated . Code to allocate days among calendar months . Comparison of total days allocated with length of stay, by patient . List of patients present, and patient-days, by calendar month . List of days by patient and calendar month. DATASET ACTIVATE TestData WINDOW=FRONT. DATASET COPY Length_of_Stay. DATASET ACTIVATE Length_of_Stay WINDOW=FRONT. * For this project, a 'stay' spans from the beginning date ... . * up to, but not including, the ending date; except that if ... . * the beginning and ending date are the same, that day *is* ... . * included. ... . NUMERIC #End_Of_Stay (ADATE10). COMPUTE #End_Of_Stay = MAX(Beg_Date, End_Date-TIME.DAYS(1)). NUMERIC LenOfStay (F5). VAR LABEL LenOfStay 'Length of stay, days'. COMPUTE LenOfStay = CTIME.DAYS(#End_Of_Stay-Beg_Date) + 1. LIST. List |-----------------------------|---------------------------| |Output Created |05-FEB-2014 16:39:07 | |-----------------------------|---------------------------| [Length_of_Stay] id Beg_Date End_Date LenOfStay 1 04/05/2013 10/05/2013 183 3 05/01/2013 12/15/2013 228 4 08/16/2013 12/25/2013 131 5 07/14/2013 08/25/2013 42 8 08/04/2013 11/28/2013 116 9 11/12/2012 01/15/2014 429 10 06/02/2011 02/01/2014 975 11 10/11/2012 05/09/2013 210 99 12/15/2013 12/15/2013 1 Number of cases read: 9 Number of cases listed: 9 * Allocate all days of stay among calendar months. ... . DATASET ACTIVATE TestData WINDOW=FRONT. DATASET COPY WorkDSet. DATASET ACTIVATE WorkDSet WINDOW=FRONT. NUMERIC #End_Of_Stay (ADATE10). COMPUTE #End_Of_Stay = MAX(Beg_Date, End_Date-TIME.DAYS(1)). NUMERIC CalMonth (MOYR8) NUMERIC Days (F3). VAR LABEL CalMonth 'Calendar month' Days 'Days of stay during calendar month'. NUMERIC #Yr# #Mo# (F4). NUMERIC #NextMon (MOYR8). NUMERIC #End_Of_Mon (ADATE10) /* Last day of current month */. COMPUTE #Yr# = XDATE.YEAR (Beg_Date). COMPUTE #Mo# = XDATE.MONTH(Beg_Date). COMPUTE CalMonth = DATE.MOYR(#Mo#,#Yr#). LOOP. * Get *a* day in the next month, then the *first* day ... . . COMPUTE #NextMon = CalMonth + TIME.DAYS(32). . COMPUTE #Yr# = XDATE.YEAR (#NextMon). . COMPUTE #Mo# = XDATE.MONTH(#NextMon). . COMPUTE #NextMon = DATE.MOYR (#Mo#,#Yr#). * The last day in THIS month is the day before the ... . * first day in the NEXT month ... . . COMPUTE #End_Of_Mon = #NextMon - TIME.DAYS(1). * Days of stay in this month extends from first day of ... . * stay, but not earlier than the beginning of the month; ... . * through last day of stay, but not laer than end of the ... . * month. ... . . COMPUTE Days = CTIME.DAYS(MIN(#End_Of_Stay,#End_Of_Mon) - MAX(Beg_Date,CalMonth)) + 1. . XSAVE OUTFILE = By_Month /KEEP=id Beg_Date End_Date CalMonth Days. . COMPUTE CalMonth = #NextMon. END LOOP IF #NextMon GT End_Date. EXECUTE. GET FILE = By_Month. DATASET NAME By_Month WINDOW=FRONT. * Confirm that total days matches length of stay ... . AGGREGATE OUTFILE=* /BREAK=id /TotDays 'Total days, across all months' = SUM(Days). FORMATS TotDays (F5). MATCH FILES /FILE=Length_of_Stay /FILE=* /BY id. LIST. List |-----------------------------|---------------------------| |Output Created |05-FEB-2014 16:39:09 | |-----------------------------|---------------------------| id Beg_Date End_Date LenOfStay TotDays 1 04/05/2013 10/05/2013 183 183 3 05/01/2013 12/15/2013 228 228 4 08/16/2013 12/25/2013 131 131 5 07/14/2013 08/25/2013 42 42 8 08/04/2013 11/28/2013 116 116 9 11/12/2012 01/15/2014 429 429 10 06/02/2011 02/01/2014 975 975 11 10/11/2012 05/09/2013 210 210 99 12/15/2013 12/15/2013 1 1 Number of cases read: 9 Number of cases listed: 9 * Summarize days by month, over all patients ... . DATASET ACTIVATE By_Month WINDOW=FRONT. AGGREGATE OUTFILE=* /BREAK = CalMonth /NumPats 'Number of patients present in this month' = NU /TotDays 'Total days of stay, over all patients' = SUM(Days). FORMATS NumPats (F3) /TotDays (F5). LIST. List |-----------------------------|---------------------------| |Output Created |05-FEB-2014 16:39:09 | |-----------------------------|---------------------------| CalMonth NumPats TotDays JUN 2011 1 29 JUL 2011 1 31 AUG 2011 1 31 SEP 2011 1 30 OCT 2011 1 31 NOV 2011 1 30 DEC 2011 1 31 JAN 2012 1 31 FEB 2012 1 29 MAR 2012 1 31 APR 2012 1 30 MAY 2012 1 31 JUN 2012 1 30 JUL 2012 1 31 AUG 2012 1 31 SEP 2012 1 30 OCT 2012 2 52 NOV 2012 3 79 DEC 2012 3 93 JAN 2013 3 93 FEB 2013 3 84 MAR 2013 3 93 APR 2013 4 116 MAY 2013 5 132 JUN 2013 4 120 JUL 2013 5 142 AUG 2013 7 192 SEP 2013 6 180 OCT 2013 6 159 NOV 2013 5 147 DEC 2013 5 101 JAN 2014 2 45 FEB 2014 1 0 Number of cases read: 33 Number of cases listed: 33 * List days by patient and month ... . DATASET ACTIVATE By_Month WINDOW=FRONT. LIST. List |-----------------------------|---------------------------| |Output Created |05-FEB-2014 16:39:09 | |-----------------------------|---------------------------| [By_Month] C:\Documents and Settings\Richard\My Documents\Temporary\SPSS\ 2014-02-05 ldonaghey-Re Determining number of days used per month - ByMonth.SAV id Beg_Date End_Date CalMonth Days 1 04/05/2013 10/05/2013 APR 2013 26 1 04/05/2013 10/05/2013 MAY 2013 31 1 04/05/2013 10/05/2013 JUN 2013 30 1 04/05/2013 10/05/2013 JUL 2013 31 1 04/05/2013 10/05/2013 AUG 2013 31 1 04/05/2013 10/05/2013 SEP 2013 30 1 04/05/2013 10/05/2013 OCT 2013 4 3 05/01/2013 12/15/2013 MAY 2013 31 3 05/01/2013 12/15/2013 JUN 2013 30 3 05/01/2013 12/15/2013 JUL 2013 31 3 05/01/2013 12/15/2013 AUG 2013 31 3 05/01/2013 12/15/2013 SEP 2013 30 3 05/01/2013 12/15/2013 OCT 2013 31 3 05/01/2013 12/15/2013 NOV 2013 30 3 05/01/2013 12/15/2013 DEC 2013 14 4 08/16/2013 12/25/2013 AUG 2013 16 4 08/16/2013 12/25/2013 SEP 2013 30 4 08/16/2013 12/25/2013 OCT 2013 31 4 08/16/2013 12/25/2013 NOV 2013 30 4 08/16/2013 12/25/2013 DEC 2013 24 5 07/14/2013 08/25/2013 JUL 2013 18 5 07/14/2013 08/25/2013 AUG 2013 24 8 08/04/2013 11/28/2013 AUG 2013 28 8 08/04/2013 11/28/2013 SEP 2013 30 8 08/04/2013 11/28/2013 OCT 2013 31 8 08/04/2013 11/28/2013 NOV 2013 27 9 11/12/2012 01/15/2014 NOV 2012 19 9 11/12/2012 01/15/2014 DEC 2012 31 9 11/12/2012 01/15/2014 JAN 2013 31 9 11/12/2012 01/15/2014 FEB 2013 28 9 11/12/2012 01/15/2014 MAR 2013 31 9 11/12/2012 01/15/2014 APR 2013 30 9 11/12/2012 01/15/2014 MAY 2013 31 9 11/12/2012 01/15/2014 JUN 2013 30 9 11/12/2012 01/15/2014 JUL 2013 31 9 11/12/2012 01/15/2014 AUG 2013 31 9 11/12/2012 01/15/2014 SEP 2013 30 9 11/12/2012 01/15/2014 OCT 2013 31 9 11/12/2012 01/15/2014 NOV 2013 30 9 11/12/2012 01/15/2014 DEC 2013 31 9 11/12/2012 01/15/2014 JAN 2014 14 10 06/02/2011 02/01/2014 JUN 2011 29 10 06/02/2011 02/01/2014 JUL 2011 31 10 06/02/2011 02/01/2014 AUG 2011 31 10 06/02/2011 02/01/2014 SEP 2011 30 10 06/02/2011 02/01/2014 OCT 2011 31 10 06/02/2011 02/01/2014 NOV 2011 30 10 06/02/2011 02/01/2014 DEC 2011 31 10 06/02/2011 02/01/2014 JAN 2012 31 10 06/02/2011 02/01/2014 FEB 2012 29 10 06/02/2011 02/01/2014 MAR 2012 31 10 06/02/2011 02/01/2014 APR 2012 30 10 06/02/2011 02/01/2014 MAY 2012 31 10 06/02/2011 02/01/2014 JUN 2012 30 10 06/02/2011 02/01/2014 JUL 2012 31 10 06/02/2011 02/01/2014 AUG 2012 31 10 06/02/2011 02/01/2014 SEP 2012 30 10 06/02/2011 02/01/2014 OCT 2012 31 10 06/02/2011 02/01/2014 NOV 2012 30 10 06/02/2011 02/01/2014 DEC 2012 31 10 06/02/2011 02/01/2014 JAN 2013 31 10 06/02/2011 02/01/2014 FEB 2013 28 10 06/02/2011 02/01/2014 MAR 2013 31 10 06/02/2011 02/01/2014 APR 2013 30 10 06/02/2011 02/01/2014 MAY 2013 31 10 06/02/2011 02/01/2014 JUN 2013 30 10 06/02/2011 02/01/2014 JUL 2013 31 10 06/02/2011 02/01/2014 AUG 2013 31 10 06/02/2011 02/01/2014 SEP 2013 30 10 06/02/2011 02/01/2014 OCT 2013 31 10 06/02/2011 02/01/2014 NOV 2013 30 10 06/02/2011 02/01/2014 DEC 2013 31 10 06/02/2011 02/01/2014 JAN 2014 31 10 06/02/2011 02/01/2014 FEB 2014 0 11 10/11/2012 05/09/2013 OCT 2012 21 11 10/11/2012 05/09/2013 NOV 2012 30 11 10/11/2012 05/09/2013 DEC 2012 31 11 10/11/2012 05/09/2013 JAN 2013 31 11 10/11/2012 05/09/2013 FEB 2013 28 11 10/11/2012 05/09/2013 MAR 2013 31 11 10/11/2012 05/09/2013 APR 2013 30 11 10/11/2012 05/09/2013 MAY 2013 8 99 12/15/2013 12/15/2013 DEC 2013 1 Number of cases read: 83 Number of cases listed: 83 ============================= APPENDIX: Test data, and code ============================= * C:\Documents and Settings\Richard\My Documents . * \Technical\spssx-l\Z-2014\ . * 2014-02-05 ldonaghey- . * Re Determining number of days used per month.SPS . * In response to follow-up posting . * Date: Wed, 5 Feb 2014 11:34:18 -0800 . * From: ldonaghey <[hidden email]> . * Subject: Re: Determining number of days used per month with a . * beginning & ending date . * To: [hidden email] . * . * See original posting . * Date: Sat, 1 Feb 2014 20:12:44 -0800 . * From: ldonaghey <[hidden email]> . * Subject: Determining number of days used per month with a . * beginning & ending date . * To: [hidden email] . * for which I wrote a response dated Mon, 3 Feb 2014, posting code . * saved as . * C:\Documents and Settings\Richard\My Documents . * \Technical\spssx-l\Z-2014\ . * 2014-02-01 ldonaghey- . * Determining number of days used per month.SPS . * This code is a modification of that one. . * From the original posting: . * "Anyone have any ideas on how to calculate the # of days in each . * month between the beg_date & End_date." . * From the follow-up: . * "I just verified that the length of stay is the difference . * between the Beg Date and the End Date except if the dates are . * the same, then length of stay should be 1 day." . * Scratch file, in which to unroll to one record per id per . * calendar month FILE HANDLE By_Month /NAME='C:\Documents and Settings\Richard\My Documents' + '\Temporary\SPSS\' + '2014-02-05 ldonaghey-' + 'Re Determining number of days used per month' + ' - ' + 'ByMonth.SAV'. * Test data, from original post, but adding ................. . * patient 99 with a one-day stat: ................. . NEW FILE. DATA LIST LIST /id Beg_Date End_Date (F3, ADATE10, ADATE10). BEGIN DATA 1 04/05/2013 10/05/2013 3 05/01/2013 12/15/2013 11 10/11/2012 05/09/2013 4 08/16/2013 12/25/2013 5 07/14/2013 08/25/2013 8 08/04/2013 11/28/2013 9 11/12/2012 01/15/2014 10 06/02/2011 02/01/2014 99 12/15/2013 12/15/2013 END DATA. SORT CASES BY id Beg_Date End_Date. DATASET NAME TestData WINDOW=FRONT. LIST. * Compute total length of stay for every patient. ... . DATASET ACTIVATE TestData WINDOW=FRONT. DATASET COPY Length_of_Stay. DATASET ACTIVATE Length_of_Stay WINDOW=FRONT. * For this project, a 'stay' spans from the beginning date ... . * up to, but not including, the ending date; except that if ... . * the beginning and ending date are the same, that day *is* ... . * included. ... . NUMERIC #End_Of_Stay (ADATE10). COMPUTE #End_Of_Stay = MAX(Beg_Date, End_Date-TIME.DAYS(1)). NUMERIC LenOfStay (F5). VAR LABEL LenOfStay 'Length of stay, days'. COMPUTE LenOfStay = CTIME.DAYS(#End_Of_Stay-Beg_Date) + 1. LIST. * Allocate all days of stay among calendar months. ... . DATASET ACTIVATE TestData WINDOW=FRONT. DATASET COPY WorkDSet. DATASET ACTIVATE WorkDSet WINDOW=FRONT. NUMERIC #End_Of_Stay (ADATE10). COMPUTE #End_Of_Stay = MAX(Beg_Date, End_Date-TIME.DAYS(1)). NUMERIC CalMonth (MOYR8) NUMERIC Days (F3). VAR LABEL CalMonth 'Calendar month' Days 'Days of stay during calendar month'. NUMERIC #Yr# #Mo# (F4). NUMERIC #NextMon (MOYR8). NUMERIC #End_Of_Mon (ADATE10) /* Last day of current month */. COMPUTE #Yr# = XDATE.YEAR (Beg_Date). COMPUTE #Mo# = XDATE.MONTH(Beg_Date). COMPUTE CalMonth = DATE.MOYR(#Mo#,#Yr#). LOOP. * Get *a* day in the next month, then the *first* day ... . . COMPUTE #NextMon = CalMonth + TIME.DAYS(32). . COMPUTE #Yr# = XDATE.YEAR (#NextMon). . COMPUTE #Mo# = XDATE.MONTH(#NextMon). . COMPUTE #NextMon = DATE.MOYR (#Mo#,#Yr#). * The last day in THIS month is the day before the ... . * first day in the NEXT month ... . . COMPUTE #End_Of_Mon = #NextMon - TIME.DAYS(1). * Days of stay in this month extends from first day of ... . * stay, but not earlier than the beginning of the month; ... . * through last day of stay, but not laer than end of the ... . * month. ... . . COMPUTE Days = CTIME.DAYS(MIN(#End_Of_Stay,#End_Of_Mon) - MAX(Beg_Date,CalMonth)) + 1. . XSAVE OUTFILE = By_Month /KEEP=id Beg_Date End_Date CalMonth Days. . COMPUTE CalMonth = #NextMon. END LOOP IF #NextMon GT End_Date. EXECUTE. GET FILE = By_Month. DATASET NAME By_Month WINDOW=FRONT. * Confirm that total days matches length of stay ... . AGGREGATE OUTFILE=* /BREAK=id /TotDays 'Total days, across all months' = SUM(Days). FORMATS TotDays (F5). MATCH FILES /FILE=Length_of_Stay /FILE=* /BY id. LIST. * Summarize days by month, over all patients ... . DATASET ACTIVATE By_Month WINDOW=FRONT. AGGREGATE OUTFILE=* /BREAK = CalMonth /NumPats 'Number of patients present in this month' = NU /TotDays 'Total days of stay, over all patients' = SUM(Days). FORMATS NumPats (F3) /TotDays (F5). LIST. * List days by patient and month ... . DATASET ACTIVATE By_Month WINDOW=FRONT. 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 |
Re: Determining number of days used per month with a beginning & ending date
|
|
In reply to this post by ldonaghey
Here's a buglet in what I just posted. If you look at the listing of
(total) days by month, you'll see FEB 2014 1 0 which comes from patient #10: 10 06/02/2011 02/01/2014 FEB 2014 0 That's actually right: the ending date is, indeed, the first of February 2014, but it doesn't count as part of the stay. But it's undesirable to report zero-day months. The fix, I believe, is to change the close of the loop that allocates days among months, from END LOOP IF #NextMon GT End_Date. to END LOOP IF #NextMon GT #End_Of_Stay. ===================== 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 |
|
|
In reply to this post by ldonaghey
Thank you to everyone for your help. The last code posted did everything I needed.
|
|
|
In reply to this post by Richard Ristow
I'm not sure what I'm doing wrong but when I run the code against my own file I don't get the same results. I have your exact code except that I'm getting my own file instead of creating one within the syntax.
I replaced the NEW FILE with the following and ran your exact syntax: GET FILE='C:\SPSS\ALC\SPSS Data Files\ALC Days.sav'. DATASET NAME TestData WINDOW=FRONT. When I run it adds the extra day at the end. I copied the data from my file into the syntax as a NEW FILE and that worked. I can't figure out why its not producing the same results with an external file. Here are my results using my EXTERNAL file (everything is correct except the last month): 1 04/08/2013 02/08/2014 Apr 2013 23 1 04/08/2013 02/08/2014 May 2013 31 1 04/08/2013 02/08/2014 Jun 2013 30 1 04/08/2013 02/08/2014 Jul 2013 31 1 04/08/2013 02/08/2014 Aug 2013 31 1 04/08/2013 02/08/2014 Sep 2013 30 1 04/08/2013 02/08/2014 Oct 2013 31 1 04/08/2013 02/08/2014 Nov 2013 30 1 04/08/2013 02/08/2014 Dec 2013 31 1 04/08/2013 02/08/2014 Jan 2014 31 1 04/08/2013 02/08/2014 Feb 2014 8 Results using NEW FILE within syntax: 1 04/08/2013 02/08/2014 Apr 2013 23 1 04/08/2013 02/08/2014 May 2013 31 1 04/08/2013 02/08/2014 Jun 2013 30 1 04/08/2013 02/08/2014 Jul 2013 31 1 04/08/2013 02/08/2014 Aug 2013 31 1 04/08/2013 02/08/2014 Sep 2013 30 1 04/08/2013 02/08/2014 Oct 2013 31 1 04/08/2013 02/08/2014 Nov 2013 30 1 04/08/2013 02/08/2014 Dec 2013 31 1 04/08/2013 02/08/2014 Jan 2014 31 1 04/08/2013 02/08/2014 Feb 2014 7 I've defined the dates in the external file as ADATE10. |
|
|
I'm going to attach my external file.ALC_Days.sav
|
Re: Determining number of days used per month with a beginning & ending date
|
|
In reply to this post by ldonaghey
At 03:05 PM 2/8/2014, you wrote:
>I'm not sure what I'm doing wrong but when I run the code against my own file >I don't get the same results. I've got your file and I'm looking at it; am just starting to run tests. Will let you know as soon as I learn anything. -Richard ===================== 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 |
Re: Determining number of days used per month with a beginning & ending date
|
|
In reply to this post by ldonaghey
At 03:05 PM 2/8/2014, ldonaghey wrote:
>When I run the code against my own file I don't get the same results. >I replaced the NEW FILE with the following and ran your exact syntax: >GET > FILE='C:\SPSS\ALC\SPSS Data Files\ALC Days.sav'. >DATASET NAME TestData WINDOW=FRONT. > >When I run it adds the extra day at the end [of each stay]. I >copied the data from my file into the syntax as a NEW FILE and that >worked. I can't figure out why >its not producing the same results with an external file. It worked because the date values you copied into the syntax weren't the same as the values in the external file, though they looked the same. Here's a few lines of the listing of your ALC_days file, but with the dates' formats changed to date-time form: FORMATS Beg_Date End_Date (DATETIME22). LIST. List |-----------------------------|---------------------------| |Output Created |08-FEB-2014 17:53:47 | |-----------------------------|---------------------------| [DataSet1] C:\Documents and Settings\Richard\My Documents\Technical\spssx-l\Z-2014\2014-02-08 ldonaghey-ALC_days.SAV id Beg_Date End_Date 1 08-APR-2013 00:00:00 08-FEB-2014 14:25:21 2 12-APR-2013 00:00:00 08-FEB-2014 14:25:21 3 05-DEC-2013 00:00:00 08-FEB-2014 14:25:21 4 05-DEC-2013 00:00:00 08-FEB-2014 14:25:21 5 05-DEC-2013 00:00:00 08-FEB-2014 14:25:21 6 05-DEC-2013 00:00:00 08-FEB-2014 14:25:21 7 09-JAN-2014 00:00:00 08-FEB-2014 14:25:21 8 14-MAY-2012 00:00:00 08-FEB-2014 14:25:21 ... ALL the ending dates in your file are 14:25:21 on 8 Feb 2014. (It looks like something's really wrong with the file.) But when you copied the displayed values into the file and ran them, you copied them as pure date values, and got what you expected. (The ending dates were then dates, although probably wrong.) I'll bet your lengths of stay are NOT one day too long, but 14 hours, 25 minutes, and 21 seconds too long; but I've used an integer format for the number of days, and the displayed value is rounded up. I'll send a code fix, to remove the time portion, in a following note; but of course there's no way to get the right length of stay if the ending date is bad. Goodness knows what that 14:25:21 is about, but I can change the code to strip it off. The code I'm sending you, below, strips the time portion off *both* the beginning date and the end date, just in case -- like this: NUMERIC #Beg_Of_Stay #End_Of_Stay (ADATE10). COMPUTE #Beg_Of_Stay = XDATE.DATE(Beg_Date). COMPUTE #End_Of_Stay = MAX(#Beg_Of_Stay, XDATE.DATE(End_Date)-TIME.DAYS(1)). ===================== 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 |
Re: Determining number of days used per month with a beginning & ending date
|
|
In reply to this post by ldonaghey
At 03:08 PM 2/8/2014, ldonaghey wrote:
>I'm going to attach my external file. ALC_Days.sav ><http://spssx-discussion.1045642.n5.nabble.com/file/n5724382/ALC_Days.sav> Sometimes one can miss the screamingly obvious. All the End_Date values in that file are 08-FEB-2014 14:25:21.447001. That is the middle of the afternoon, today. If your SPSS is running on a system that keeps Eastern Standard Time, that's about an hour and a half before you wrote me (see time at the head of this note). On the other hand, if your system keeps Pacific time, which you appear to be on (time-stamp on your note is Sat, 8 Feb 2014 12:08:37 -0800), that date is almost 2 1/2 hours AFTER you wrote. Anyhow, it seems very likely that whatever went wrong with the file happened today. It's almost as if the End_Date variable got overwritten with the value of system variable $TIME, though that's not the easiest thing to do by accident. , identical to the microsecond. ===================== 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 |
|
|
I know what I did wrong. I was trying to replicate real data.
The file that I will be importing into SPSS will have end dates that are missing because the patient is still in hospital, so I was assigning the current date. I thought I had used the XDATE.DATE to create the end date (guess not). Then I changed the date formats to ADATE10 so the error wasn't obvious. I'm still fairly new to SPSS (1 1/2 years) and I'm pretty much self taught. Lesson learned, "check the date formats. I knew it was something silly. Much appreciated! |
Re: Determining number of days used per month with a beginning & ending date
Administrator
|
In reply to this post by Richard Ristow
Richard,
My code doesn't use calendar information aside from testing Feb for leap years. Could make it pretty general by substituting IF M=2 days=28. IF M=2 AND ANY(y,1992,1996,2000,2004, 2008, 2012, 2016,2020,2024) days=29. With IF (M EQ 2) days=MAX(28,(MOD(y,4) EQ 0)*29)). No need to make things more complicated than necessary. I know there are exceptions to this, but irrelevant until 2100 ;-) http://en.wikipedia.org/wiki/Century_leap_year David
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
Re: Determining number of days used per month with a beginning & ending date
|
Administrator
|
FWIW: Here is my code with the various mods incorporated.
DATA LIST LIST /id (F2) Beg_Date (ADATE) End_Date (ADATE) . BEGIN DATA 1 04/05/2013 10/05/2013 3 05/01/2013 12/15/2013 11 10/11/2012 05/09/2013 4 08/16/2013 12/25/2013 5 07/14/2013 08/25/2013 8 08/04/2013 11/28/2013 9 11/12/2012 01/15/2014 10 06/02/2011 02/01/2014 END DATA. DATASET NAME raw. COMPUTE Merge=1. COMPUTE @=1. DATASET DECLARE agg. AGGREGATE OUTFILE agg / BREAK / min_date=MIN(Beg_Date) / max_date=MAX(End_Date). DATASET ACTIVATE agg . LOOP Y=XDATE.YEAR(min_date) TO XDATE.YEAR(max_date). + LOOP M=1 TO 12. + IF ANY(M,1,3,5,7,8,10,12) days = 31. + IF ANY(M,4,6,9,11) days = 30. + IF (M EQ 2) days = MAX(28,(MOD(y,4) EQ 0)*29). + XSAVE OUTFILE 'calender' / KEEP Y M days. + END LOOP. END LOOP. EXECUTE. GET FILE 'calender'. DATASET NAME calender. DATASET ACTIVATE calender. COMPUTE YM=Y+M/100. ALTER TYPE YM (A8). COMPUTE YM=CONCAT("v",LTRIM(YM)). EXECUTE. DELETE VARIABLES Y M . CASESTOVARS INDEX=YM. COMPUTE merge=1. MATCH FILES / FILE raw / TABLE * / BY merge. COMPUTE @1=1. VARSTOCASES / MAKE days FROM @ TO @1 / INDEX=Source(days) / KEEP=id Beg_Date End_Date / NULL=KEEP. SELECT IF INDEX(Source,"@")=0. SELECT IF RANGE(DATE.MOYR(NUMBER(CHAR.SUBSTR(Source,7,2),F2.0), NUMBER(CHAR.SUBSTR(Source,2,4),F4.0)), DATE.MOYR(XDATE.MONTH(Beg_Date),XDATE.YEAR (Beg_Date)), DATE.MOYR(XDATE.MONTH(End_Date),XDATE.YEAR (End_Date))) . SORT CASES BY id Source. MATCH FILES / FILE * / FIRST=@top / LAST=@Bot / BY ID. IF @Top days = days - XDATE.MDAY(Beg_Date) + 1. IF @Bot days = XDATE.MDAY(End_Date). IF @Top AND @Bot days=1. EXECUTE. DELETE VARIABLES @Top @Bot . DATASET CLOSE agg. DATASET CLOSE raw. CASESTOVARS / ID=id / INDEX=Source / GROUPBY=VARIABLE.
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
Re: Determining number of days used per month with a beginning & ending date
|
|
Shalom
Here is anther code to do that job DATASET CLOSE all. DATA LIST LIST /id (F2) Beg_Date (ADATE) End_Date (ADATE) . BEGIN DATA 1 04/05/2013 10/05/2013 3 05/01/2013 12/15/2013 11 10/11/2012 05/09/2013 4 08/16/2013 12/25/2013 5 07/14/2013 08/25/2013 8 08/04/2013 11/28/2013 9 11/12/2012 01/15/2014 10 06/02/2011 02/01/2014 END DATA. DATASET NAME orig . SORT CASES by id. compute days=datediff(End_Date,Beg_Date,'days') . LOOP i=1 to days. compute monthdays=sum(monthdays,1) . compute date1=DATESUM(Beg_Date,i,'days') . do if xdate.mday(date1) eq 1 or i eq days . if xdate.mday(date1) gt 1 and i eq days monthdays=sum(monthdays,1) . XSAVE OUTFILE 'tmp1' . compute monthdays=0. end if. end loop . do if xdate.mday(End_Date) eq 1 . compute monthdays=1. compute date1=DATESUM(End_Date,1,'months') . XSAVE OUTFILE 'tmp2' . end if. execute . add files file='tmp1' / file='tmp2' /DROP=days i . formats Beg_Date End_Date date1(edate10) / date1(MOYR8) . SORT CASES by id date1 . DATASET NAME result . Hillel Vardi On 09-Feb-14 12:33 PM, David Marso wrote: > FWIW: Here is my code with the various mods incorporated. > DATA LIST LIST /id (F2) Beg_Date (ADATE) End_Date (ADATE) . > BEGIN DATA > 1 04/05/2013 10/05/2013 > 3 05/01/2013 12/15/2013 > 11 10/11/2012 05/09/2013 > 4 08/16/2013 12/25/2013 > 5 07/14/2013 08/25/2013 > 8 08/04/2013 11/28/2013 > 9 11/12/2012 01/15/2014 > 10 06/02/2011 02/01/2014 > END DATA. > > DATASET NAME raw. > COMPUTE Merge=1. > COMPUTE @=1. > DATASET DECLARE agg. > AGGREGATE OUTFILE agg > / BREAK > / min_date=MIN(Beg_Date) > / max_date=MAX(End_Date). > DATASET ACTIVATE agg . > LOOP Y=XDATE.YEAR(min_date) TO XDATE.YEAR(max_date). > + LOOP M=1 TO 12. > + IF ANY(M,1,3,5,7,8,10,12) days = 31. > + IF ANY(M,4,6,9,11) days = 30. > + IF (M EQ 2) days = MAX(28,(MOD(y,4) EQ 0)*29). > + XSAVE OUTFILE 'calender' > / KEEP Y M days. > + END LOOP. > END LOOP. > EXECUTE. > > GET FILE 'calender'. > DATASET NAME calender. > DATASET ACTIVATE calender. > COMPUTE YM=Y+M/100. > ALTER TYPE YM (A8). > COMPUTE YM=CONCAT("v",LTRIM(YM)). > EXECUTE. > DELETE VARIABLES Y M . > CASESTOVARS INDEX=YM. > > COMPUTE merge=1. > MATCH FILES > / FILE raw > / TABLE * > / BY merge. > > COMPUTE @1=1. > VARSTOCASES > / MAKE days FROM @ TO @1 > / INDEX=Source(days) > / KEEP=id Beg_Date End_Date > / NULL=KEEP. > SELECT IF INDEX(Source,"@")=0. > SELECT IF RANGE(DATE.MOYR(NUMBER(CHAR.SUBSTR(Source,7,2),F2.0), > NUMBER(CHAR.SUBSTR(Source,2,4),F4.0)), > DATE.MOYR(XDATE.MONTH(Beg_Date),XDATE.YEAR (Beg_Date)), > DATE.MOYR(XDATE.MONTH(End_Date),XDATE.YEAR (End_Date))) . > SORT CASES BY id Source. > MATCH FILES > / FILE * > / FIRST=@top > / LAST=@Bot > / BY ID. > IF @Top days = days - XDATE.MDAY(Beg_Date) + 1. > IF @Bot days = XDATE.MDAY(End_Date). > IF @Top AND @Bot days=1. > EXECUTE. > DELETE VARIABLES @Top @Bot . > DATASET CLOSE agg. > DATASET CLOSE raw. > > CASESTOVARS > / ID=id > / INDEX=Source > / GROUPBY=VARIABLE. > > > David Marso wrote >> Richard, >> My code doesn't use calendar information aside from testing Feb for leap >> years. >> Could make it pretty general by substituting >> IF M=2 days=28. >> IF M=2 AND ANY(y,1992,1996,2000,2004, 2008, 2012, 2016,2020,2024) days=29. >> With >> IF (M EQ 2) days=MAX(28,(MOD(y,4) EQ 0)*29)). >> No need to make things more complicated than necessary. >> I know there are exceptions to this, but irrelevant until 2100 ;-) >> http://en.wikipedia.org/wiki/Century_leap_year >> David >> Richard Ristow wrote >>> At 11:12 PM 2/1/2014, ldonaghey wrote: >>> >>>> Anyone have any ideas on how to calculate the # of days in each >>>> month between the beg_date& End_date. >>> The general idea of looping through calendar months, calculating the >>> number of days in each, is the right one; but I think some of the >>> code has made that harder than it is. You can loop by calendar months >>> without putting any calendar information in the code, by taking advantage >>> of, >>> >>> . Given a date, you can find the calendar month (and, equivalently, >>> the first day of the calendar month) by extracting year and month, >>> and computing >>> DATE.DMY(1,month, year). >>> >>> . Given a calendar month (that is, given its first day), the date 32 >>> days later always falls in the next calendar month. You can then use >>> the above logic to calculate the first day in the next calendar month. >>> >>> . The last day of the current calendar month is one day prior to the >>> first day of the next calendar month. >>> >>> . Given a calendar month and a time interval, the portion of the >>> interval falling in that month begins with the beginning of the >>> interval, but not earlier than the first day of the month; and ends >>> with the end of the interval, but not later than the last day of the >>> month. >>> >>> Like this (with the data from the original posting): >>> >>> |-----------------------------|---------------------------| >>> |Output Created |03-FEB-2014 16:45:57 | >>> |-----------------------------|---------------------------| >>> [TestData] >>> >>> id Beg_Date End_Date >>> >>> 1 04/05/2013 10/05/2013 >>> 3 05/01/2013 12/15/2013 >>> 11 10/11/2012 05/09/2013 >>> 4 08/16/2013 12/25/2013 >>> 5 07/14/2013 08/25/2013 >>> 8 08/04/2013 11/28/2013 >>> 9 11/12/2012 01/15/2014 >>> 10 06/02/2011 02/01/2014 >>> >>> Number of cases read: 8 Number of cases listed: 8 >>> C >>> >>> >>> DATASET COPY WorkDSet. >>> DATASET ACTIVATE WorkDSet WINDOW=FRONT. >>> NUMERIC CalMonth (MOYR8) >>> NUMERIC Days (F3). >>> >>> VAR LABEL CalMonth 'Calendar month' >>> Days 'Days of stay during calendar month'. >>> >>> NUMERIC #Yr# #Mo# (F4). >>> NUMERIC #NextMon (MOYR8). >>> NUMERIC #LastDay (ADATE10) /* Last day of current month */. >>> >>> >>> COMPUTE #Yr# = XDATE.YEAR (Beg_Date). >>> COMPUTE #Mo# = XDATE.MONTH(Beg_Date). >>> >>> COMPUTE CalMonth = DATE.DMY(1,#Mo#,#Yr#). >>> >>> STRING @Sp (A2) /* Just a space for the output listing */. >>> COMPUTE @Sp= ' '. >>> >>> LOOP. >>> * Get *a* day in the next month, then the *first* day ... >>> . COMPUTE #NextMon = CalMonth + TIME.DAYS(32). >>> . COMPUTE #Yr# = XDATE.YEAR (#NextMon). >>> . COMPUTE #Mo# = XDATE.MONTH(#NextMon). >>> . COMPUTE #NextMon = DATE.DMY(1,#Mo#,#Yr#). >>> >>> * The last day in THIS month is the day before the ... >>> * first day in the NEXT month ... >>> . COMPUTE #LastDay = #NextMon - TIME.DAYS(1). >>> >>> . COMPUTE Days = CTIME.DAYS(MIN(End_Date,#LastDay) >>> - MAX(Beg_Date,CalMonth)) >>> + 1. >>> >>> . XSAVE OUTFILE = Unroll >>> /KEEP=id @Sp CalMonth Days Beg_Date End_Date. >>> . COMPUTE CalMonth = #NextMon. >>> END LOOP IF #NextMon GT End_Date. >>> EXECUTE. >>> >>> GET FILE = Unroll. >>> LIST. >>> >>> List >>> |-----------------------------|---------------------------| >>> |Output Created |03-FEB-2014 16:45:58 | >>> |-----------------------------|---------------------------| >>> C:\Documents and Settings\Richard\My Documents >>> \Temporary\SPSS\ >>> 2014-02-01 ldonaghey-Determining number of days used per month - >>> ByMonth.SAV >>> [Blank lines inserted manually] >>> id @Sp CalMonth Days Beg_Date End_Date >>> >>> 1 APR 2013 26 04/05/2013 10/05/2013 >>> 1 MAY 2013 31 04/05/2013 10/05/2013 >>> 1 JUN 2013 30 04/05/2013 10/05/2013 >>> 1 JUL 2013 31 04/05/2013 10/05/2013 >>> 1 AUG 2013 31 04/05/2013 10/05/2013 >>> 1 SEP 2013 30 04/05/2013 10/05/2013 >>> 1 OCT 2013 5 04/05/2013 10/05/2013 >>> >>> 3 MAY 2013 31 05/01/2013 12/15/2013 >>> 3 JUN 2013 30 05/01/2013 12/15/2013 >>> 3 JUL 2013 31 05/01/2013 12/15/2013 >>> 3 AUG 2013 31 05/01/2013 12/15/2013 >>> 3 SEP 2013 30 05/01/2013 12/15/2013 >>> 3 OCT 2013 31 05/01/2013 12/15/2013 >>> 3 NOV 2013 30 05/01/2013 12/15/2013 >>> 3 DEC 2013 15 05/01/2013 12/15/2013 >>> >>> 11 OCT 2012 21 10/11/2012 05/09/2013 >>> 11 NOV 2012 30 10/11/2012 05/09/2013 >>> 11 DEC 2012 31 10/11/2012 05/09/2013 >>> 11 JAN 2013 31 10/11/2012 05/09/2013 >>> 11 FEB 2013 28 10/11/2012 05/09/2013 >>> 11 MAR 2013 31 10/11/2012 05/09/2013 >>> 11 APR 2013 30 10/11/2012 05/09/2013 >>> 11 MAY 2013 9 10/11/2012 05/09/2013 >>> >>> 4 AUG 2013 16 08/16/2013 12/25/2013 >>> 4 SEP 2013 30 08/16/2013 12/25/2013 >>> 4 OCT 2013 31 08/16/2013 12/25/2013 >>> 4 NOV 2013 30 08/16/2013 12/25/2013 >>> 4 DEC 2013 25 08/16/2013 12/25/2013 >>> >>> 5 JUL 2013 18 07/14/2013 08/25/2013 >>> 5 AUG 2013 25 07/14/2013 08/25/2013 >>> >>> 8 AUG 2013 28 08/04/2013 11/28/2013 >>> 8 SEP 2013 30 08/04/2013 11/28/2013 >>> 8 OCT 2013 31 08/04/2013 11/28/2013 >>> 8 NOV 2013 28 08/04/2013 11/28/2013 >>> >>> 9 NOV 2012 19 11/12/2012 01/15/2014 >>> 9 DEC 2012 31 11/12/2012 01/15/2014 >>> 9 JAN 2013 31 11/12/2012 01/15/2014 >>> 9 FEB 2013 28 11/12/2012 01/15/2014 >>> 9 MAR 2013 31 11/12/2012 01/15/2014 >>> 9 APR 2013 30 11/12/2012 01/15/2014 >>> 9 MAY 2013 31 11/12/2012 01/15/2014 >>> 9 JUN 2013 30 11/12/2012 01/15/2014 >>> 9 JUL 2013 31 11/12/2012 01/15/2014 >>> 9 AUG 2013 31 11/12/2012 01/15/2014 >>> 9 SEP 2013 30 11/12/2012 01/15/2014 >>> 9 OCT 2013 31 11/12/2012 01/15/2014 >>> 9 NOV 2013 30 11/12/2012 01/15/2014 >>> 9 DEC 2013 31 11/12/2012 01/15/2014 >>> 9 JAN 2014 15 11/12/2012 01/15/2014 >>> >>> 10 JUN 2011 29 06/02/2011 02/01/2014 >>> 10 JUL 2011 31 06/02/2011 02/01/2014 >>> 10 AUG 2011 31 06/02/2011 02/01/2014 >>> 10 SEP 2011 30 06/02/2011 02/01/2014 >>> 10 OCT 2011 31 06/02/2011 02/01/2014 >>> 10 NOV 2011 30 06/02/2011 02/01/2014 >>> 10 DEC 2011 31 06/02/2011 02/01/2014 >>> 10 JAN 2012 31 06/02/2011 02/01/2014 >>> 10 FEB 2012 29 06/02/2011 02/01/2014 >>> 10 MAR 2012 31 06/02/2011 02/01/2014 >>> 10 APR 2012 30 06/02/2011 02/01/2014 >>> 10 MAY 2012 31 06/02/2011 02/01/2014 >>> 10 JUN 2012 30 06/02/2011 02/01/2014 >>> 10 JUL 2012 31 06/02/2011 02/01/2014 >>> 10 AUG 2012 31 06/02/2011 02/01/2014 >>> 10 SEP 2012 30 06/02/2011 02/01/2014 >>> 10 OCT 2012 31 06/02/2011 02/01/2014 >>> 10 NOV 2012 30 06/02/2011 02/01/2014 >>> 10 DEC 2012 31 06/02/2011 02/01/2014 >>> 10 JAN 2013 31 06/02/2011 02/01/2014 >>> 10 FEB 2013 28 06/02/2011 02/01/2014 >>> 10 MAR 2013 31 06/02/2011 02/01/2014 >>> 10 APR 2013 30 06/02/2011 02/01/2014 >>> 10 MAY 2013 31 06/02/2011 02/01/2014 >>> 10 JUN 2013 30 06/02/2011 02/01/2014 >>> 10 JUL 2013 31 06/02/2011 02/01/2014 >>> 10 AUG 2013 31 06/02/2011 02/01/2014 >>> 10 SEP 2013 30 06/02/2011 02/01/2014 >>> 10 OCT 2013 31 06/02/2011 02/01/2014 >>> 10 NOV 2013 30 06/02/2011 02/01/2014 >>> 10 DEC 2013 31 06/02/2011 02/01/2014 >>> 10 JAN 2014 31 06/02/2011 02/01/2014 >>> 10 FEB 2014 1 06/02/2011 02/01/2014 >>> >>> Number of cases read: 82 Number of cases listed: 82 >>> >>> AGGREGATE OUTFILE=* >>> /BREAK = CalMonth >>> /TotDays 'Total days of stay, over all patients' >>> = SUM(Days). >>> . >>> FORMATS TotDays (F5). >>> >>> LIST. >>> >>> List >>> |-----------------------------|---------------------------| >>> |Output Created |03-FEB-2014 16:45:58 | >>> |-----------------------------|---------------------------| >>> CalMonth TotDays >>> >>> JUN 2011 29 >>> JUL 2011 31 >>> AUG 2011 31 >>> SEP 2011 30 >>> OCT 2011 31 >>> NOV 2011 30 >>> DEC 2011 31 >>> JAN 2012 31 >>> FEB 2012 29 >>> MAR 2012 31 >>> APR 2012 30 >>> MAY 2012 31 >>> JUN 2012 30 >>> JUL 2012 31 >>> AUG 2012 31 >>> SEP 2012 30 >>> OCT 2012 52 >>> NOV 2012 79 >>> DEC 2012 93 >>> JAN 2013 93 >>> FEB 2013 84 >>> MAR 2013 93 >>> APR 2013 116 >>> MAY 2013 133 >>> JUN 2013 120 >>> JUL 2013 142 >>> AUG 2013 193 >>> SEP 2013 180 >>> OCT 2013 160 >>> NOV 2013 148 >>> DEC 2013 102 >>> JAN 2014 46 >>> FEB 2014 1 >>> >>> Number of cases read: 33 Number of cases listed: 33 >>> ================================= >>> APPENDIX: Test data, and all code >>> ================================= >>> * C:\Documents and Settings\Richard\My Documents . >>> * \Technical\spssx-l\Z-2014\ . >>> * 2014-02-01 ldonaghey-Determining number of days used per month.SPS . >>> >>> * In response to posting . >>> * Date: Sat, 1 Feb 2014 20:12:44 -0800 . >>> * From: ldonaghey< >>> laurinda.donaghey@ >>> > . >>> * Subject: Determining number of days used per month with a . >>> * beginning& ending date . >>> * To: >>> SPSSX-L@.UGA >>> . >>> >>> * To determine the number of days falling into each calendar month . >>> >>> * Scratch file, in which to unroll to one record per id per . >>> * calendar month >>> >>> FILE HANDLE Unroll >>> /NAME='C:\Documents and Settings\Richard\My Documents' + >>> '\Temporary\SPSS\' + >>> '2014-02-01 ldonaghey-' + >>> 'Determining number of days used per month' + >>> ' - ' + >>> 'ByMonth.SAV'. >>> >>> >>> * Test data, from original post: ................. . >>> NEW FILE. >>> DATA LIST LIST >>> /id Beg_Date End_Date >>> (F3, ADATE10, ADATE10). >>> BEGIN DATA >>> 1 04/05/2013 10/05/2013 >>> 3 05/01/2013 12/15/2013 >>> 11 10/11/2012 05/09/2013 >>> 4 08/16/2013 12/25/2013 >>> 5 07/14/2013 08/25/2013 >>> 8 08/04/2013 11/28/2013 >>> 9 11/12/2012 01/15/2014 >>> 10 06/02/2011 02/01/2014 >>> END DATA. >>> DATASET NAME TestData WINDOW=FRONT. >>> LIST. >>> >>> DATASET COPY WorkDSet. >>> DATASET ACTIVATE WorkDSet WINDOW=FRONT. >>> >>> NUMERIC CalMonth (MOYR8) >>> NUMERIC Days (F3). >>> >>> VAR LABEL CalMonth 'Calendar month' >>> Days 'Days of stay during calendar month'. >>> >>> NUMERIC #Yr# #Mo# (F4). >>> NUMERIC #NextMon (MOYR8). >>> NUMERIC #LastDay (ADATE10) /* Last day of current month */. >>> >>> >>> >>> COMPUTE #Yr# = XDATE.YEAR (Beg_Date). >>> COMPUTE #Mo# = XDATE.MONTH(Beg_Date). >>> >>> COMPUTE CalMonth = DATE.DMY(1,#Mo#,#Yr#). >>> >>> STRING @Sp (A2) /* Just a space for the output listing */. >>> COMPUTE @Sp= ' '. >>> >>> LOOP. >>> * Get *a* day in the next month, then the *first* day ... >>> . COMPUTE #NextMon = CalMonth + TIME.DAYS(32). >>> . COMPUTE #Yr# = XDATE.YEAR (#NextMon). >>> . COMPUTE #Mo# = XDATE.MONTH(#NextMon). >>> . COMPUTE #NextMon = DATE.DMY(1,#Mo#,#Yr#). >>> >>> * The last day in THIS month is the day before the ... >>> * first day in the NEXT month ... >>> . COMPUTE #LastDay = #NextMon - TIME.DAYS(1). >>> >>> . COMPUTE Days = CTIME.DAYS(MIN(End_Date,#LastDay) >>> - MAX(Beg_Date,CalMonth)) >>> + 1. >>> >>> . XSAVE OUTFILE = Unroll >>> /KEEP=id @Sp CalMonth Days Beg_Date End_Date. >>> . COMPUTE CalMonth = #NextMon. >>> END LOOP IF #NextMon GT End_Date. >>> EXECUTE. >>> >>> GET FILE = Unroll. >>> LIST. >>> >>> AGGREGATE OUTFILE=* >>> /BREAK = CalMonth >>> /TotDays 'Total days of stay, over all patients' >>> = SUM(Days). >>> . >>> FORMATS TotDays (F5). >>> >>> LIST. >>> >>> ===================== >>> 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 > > > > > ----- > Please reply to the list and not to my personal email. > Those desiring my consulting or training services please feel free to email me. > --- > "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." > Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" > -- > View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Determining-number-of-days-used-per-month-with-a-beginning-ending-date-tp5724275p5724393.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 |
Re: Determining number of days used per month with a beginning & ending date
|
|
In reply to this post by ldonaghey
At 11:28 PM 2/8/2014, ldonaghey wrote:
>I know what I did wrong. I was trying to replicate real data. > >The file that I will be importing into SPSS will have end dates that >are missing because the patient is still in hospital, so I was >assigning the current date. I thought I had used the XDATE.DATE to >create the end date (guess not). Then I changed the date formats >to ADATE10 so the error wasn't obvious. Two things. First, would you like the changes to strip off the time part of your beginning and ending dates? Or is that not going to be a problem, going forward? Second, I know it's just for testing, but it can be dangerous to put a date in a file when it isn't going to be the right date. An alternative would be to leave those dates missing, and have the code fill in the current date AND an indication that, as of that date, the stay was incomplete. I don't know how you're going to use this information -- I worry about calculating provisional and then final lengths of stay, and then double-counting some of the days by adding both of those two. One possibility would be not to count days for the current month, or to count them and flag the count as 'provisional'; you know that all the days there will ever be in *previous* months, have occurred. ===================== 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 |
Re: Determining number of days used per month with a beginning & ending date
|
|
In reply to this post by David Marso
David -
We could entertain the list with a religious war on coding styles, but there's little point. We both have very pronounced styles that we aren't going to change much. One could make arguments for either style, but in the end they *are* styles, and we are both going to write the way we do.. Anyway, at 12:31 AM 2/9/2014, you wrote: >My code doesn't use calendar information aside from testing Feb for >leap years. I wouldn't have said that. Your code has hard-coded the number of days in the months, which I would call calendar information. In fact, you build a whole dataset called 'calendar'. >No need to make things more complicated than necessary. Indeed. That's why I used SPSS's calendar functions: I could write code that took no cognizance of the lengths of months, or the breaks between years, or leap years. I've never claimed prizes for compact code, but I think this time mine is pretty tight. Below is the core, to calculate total days of stay by calendar month, and I think it's economical in both lines and operations -- even if I could have shortened COMPUTE #Yr# = XDATE.YEAR (Beg_Date). COMPUTE #Mo# = XDATE.MONTH(Beg_Date). COMPUTE CalMonth = DATE.MOYR(#Mo#,#Yr#). to COMPUTE CalMonth = DATE.MOYR(XDATE.MONTH(Beg_Date) ,XDATE.YEAR (Beg_Date)). Live long, and prosper. -Richard =========================================== Core code follows. The result is total days of stay by calendar month, in 'long' form =========================================== NUMERIC #End_Of_Stay (ADATE10). COMPUTE #End_Of_Stay = MAX(Beg_Date, End_Date-TIME.DAYS(1)). NUMERIC CalMonth (MOYR8) NUMERIC Days (F3). NUMERIC #Yr# #Mo# (F4). NUMERIC #NextMon (MOYR8). NUMERIC #End_Of_Mon (ADATE10). COMPUTE #Yr# = XDATE.YEAR (Beg_Date). COMPUTE #Mo# = XDATE.MONTH(Beg_Date). COMPUTE CalMonth = DATE.MOYR(#Mo#,#Yr#). LOOP. . COMPUTE #NextMon = CalMonth + TIME.DAYS(32). . COMPUTE #Yr# = XDATE.YEAR (#NextMon). . COMPUTE #Mo# = XDATE.MONTH(#NextMon). . COMPUTE #NextMon = DATE.MOYR (#Mo#,#Yr#). . COMPUTE #End_Of_Mon = #NextMon - TIME.DAYS(1). . COMPUTE Days = CTIME.DAYS(MIN(#End_Of_Stay,#End_Of_Mon) - MAX(Beg_Date,CalMonth)) + 1. . XSAVE OUTFILE = By_Month /KEEP=id Beg_Date End_Date CalMonth Days. . COMPUTE CalMonth = #NextMon. END LOOP IF #NextMon GT #End_Of_Stay. EXECUTE. GET FILE = By_Month. DATASET NAME By_Month WINDOW=FRONT. AGGREGATE OUTFILE=* /BREAK = CalMonth /NumPats = NU /TotDays = SUM(Days). ===================== 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 |
Re: Determining number of days used per month with a beginning & ending date
|
Administrator
|
"Your code has hard-coded the number of days in the months"
I don't see that as any problem. After all, unless some new naked emperor shows up and decides to strip a day from July, Aug or Sept and add one to November it is very unlikely to change. Before writing my 'hard coded' version I had thought to use the first day of mo -86400 to get the last day of the preceding month but realized that why bother since the days for all months except Feb are the same from year to year. Anyhow, I don't see that our styles of coding are usually all that different from one another. For the most part we agree on what few standards that exist for creating SPSS coding solutions. You tend to provide a lot more comments. I deliberately omit them in some unrealistic hope that people will RTFM! --
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
|
|
In reply to this post by Richard Ristow
Everything is good, now that I know what I did wrong. I'm going create a variable to identify those records that there was is ending date. All is good. Thanks again for all your help.
|
Re: Determining number of days used per month with a beginning & ending date
|
Administrator
|
In reply to this post by David Marso
One more variation using MATRIX rather than XSAVE to build the long file.
This is obviously NICE because we don't have to write ANYTHING to disk ;-) --------------------------- DATA LIST LIST /id (F2) Beg_Date (ADATE) End_Date (ADATE) . BEGIN DATA 1 04/05/2013 10/05/2013 3 05/01/2013 12/15/2013 11 10/11/2012 05/09/2013 4 08/16/2013 12/25/2013 5 07/14/2013 08/25/2013 8 08/04/2013 11/28/2013 9 11/12/2012 01/15/2014 10 06/02/2011 02/01/2014 END DATA. COMPUTE Merge=1. COMPUTE @=1. DATASET NAME raw. DATASET DECLARE agg. COMPUTE yr_start = XDATE.YEAR(Beg_Date). COMPUTE yr_end = XDATE.YEAR(End_Date). AGGREGATE OUTFILE agg/BREAK/yr_start=MIN(yr_start)/yr_end=MAX(yr_end). DATASET DECLARE calender . DATASET ACTIVATE agg . /* Following eliminates the need to write anything to disk */. MATRIX. GET yrs /FILE * . SAVE {KRONEKER(T({yrs(1):yrs(2)}),MAKE(12,1,1)), KRONEKER(MAKE((yrs(2)-yrs(1)+1),1,1),T({1:12})), KRONEKER(MAKE((yrs(2)-yrs(1)+1),1,1),T({31,28,31,30,31,30,31,31,30,31,30,31}))} /OUTFILE calender /VARIABLES yr mo days. END MATRIX. /* REPLACES */. /* LOOP Y=XDATE.YEAR(min_date) TO XDATE.YEAR(max_date). /* + LOOP M=1 TO 12. /* + IF ANY(M,1,3,5,7,8,10,12) days = 31. /* + IF ANY(M,4,6,9,11) days = 30. /* + IF (M EQ 2) days = MAX(28,(MOD(y,4) EQ 0)*29). /* + XSAVE OUTFILE 'calender' /* / KEEP Y M days. /* + END LOOP. /* END LOOP. /* EXECUTE. /* GET FILE 'calender'. DATASET CLOSE agg. DATASET ACTIVATE calender . IF (mo EQ 2 AND MOD(yr,4) EQ 0 ) days=29. COMPUTE YM=Y+M/100. ALTER TYPE YM (A8). COMPUTE YM=CONCAT("v",LTRIM(YM)). EXECUTE. DELETE VARIABLES Y M . CASESTOVARS INDEX=YM. COMPUTE merge=1. MATCH FILES / FILE raw / TABLE * / BY merge. COMPUTE @1=1. VARSTOCASES / MAKE days FROM @ TO @1 / INDEX=Source(days) / KEEP=id Beg_Date End_Date / NULL=KEEP. SELECT IF INDEX(Source,"@")=0. SELECT IF RANGE(DATE.MOYR(NUMBER(CHAR.SUBSTR(Source,7,2),F2.0), NUMBER(CHAR.SUBSTR(Source,2,4),F4.0)), DATE.MOYR(XDATE.MONTH(Beg_Date),XDATE.YEAR (Beg_Date)), DATE.MOYR(XDATE.MONTH(End_Date),XDATE.YEAR (End_Date))) . SORT CASES BY id Source. MATCH FILES / FILE * / FIRST=@top / LAST=@Bot / BY ID. IF (@Top ) days = days - XDATE.MDAY(Beg_Date) + 1. IF (@Bot ) days = XDATE.MDAY(End_Date). IF (@Top AND @Bot) days=1. EXECUTE. DELETE VARIABLES @Top @Bot . DATASET CLOSE agg. DATASET CLOSE raw. CASESTOVARS / ID=id / INDEX=Source / GROUPBY=VARIABLE.
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
Re: Determining number of days used per month with a beginning & ending date
|
|
At 11:32 AM 2/12/2014, David Marso wrote:
>One more variation using MATRIX rather than XSAVE to build the long file. >This is obviously NICE because we don't have to write ANYTHING to disk It does have that advantage. It still seems to me that building a list of months and joining it to the input is giving yourself more trouble than necessary; anyway, you've seen my solution, using SPSS's calendar and never building such a list. I do write a scratch file. You may have a bug. Your code to handle the initial and final months in which days occur is >IF (@Top ) days = days - XDATE.MDAY(Beg_Date) + 1. >IF (@Bot ) days = XDATE.MDAY(End_Date). >IF (@Top AND @Bot) days=1. First, remember the ending date is not part of the stay unless it's also the beginning date. (The original poster said that, some time down in the discussion.) So the second line should be IF (@Bot ) days = XDATE.MDAY(End_Date) - 1. (That still has the buglet I originally had in my solution: if a multiple-day stay ends on the first of a month, it will report the patient was there in that month, for '0 days'.) And the third line will always give one day for any stay that begins and ends in the same calendar month, even if that stay is multiple days. I think you want, IF (@Top AND @Bot) days=MAX(1,XDATE.MDAY(End_Date)-XDATE.MDAY(Beg_Date)). ===================== 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 |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |