Date: Mon, 27 May 2002 21:58:25 -0400
Reply-To: Stephen Oakley <soakley@zipcar.com>
Sender: "SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>
From: Stephen Oakley <soakley@zipcar.com>
Subject: Re: occupied time from reservation information
Content-Type: text/plain; charset="iso-8859-1"
Thanks so much Jim..
I think this is the answer, thanks. I have adapted it for the half hour
as follows and am working on the following:
I get a a warning 525 re An attempt was made to store a value into an
element of a vector the subscript of which was missing or otherwise
invalid.
this seems to be related to the number of records..3100-odd reading in
OK of the 3700 in the file...If I limit the number of records it works
without warnings..I havent figured it out yet.
I want the resulting file to have only two variables, the time of the
bin...right now I do one month at a time and convert the "case labels"
created in the flip procedure as follows (this manually add the month
under review...I haven;t figured out how to pass an variable from the
original file into the flipped one)
COMP hour eq date.mdy(5,1,2002)+time.hms(0,NUMBER(SUBSTR(case_lbl,4)
,F8.0)*30,0).
exe. (see the resluts below)
I would love to be able to do multiple months, but haven't figures out
how to restructure a multiple month result (as in the form:
MTH_ST 1.00 2.00 3.00 5.00 6.00
HR_1 .00 .00 .00 .00 1.00
HR_2 .00 .00 .00 .00 1.00
HR_3 .00 .00 .00 1.00 1.00
HR_4 .00 .00 .00 1.00 1.00
HR_5 .00 .00 .00 1.00 1.00
to the two-column form..month_hour and resourcecount
but until then, I will work on batching discrete periods and combining
them.
Thanks again..
S
Sample data:
[resource user start end
108 793 5/1/2002 1:30 5/1/2002 6:00
277 1031 5/1/2002 6:00 5/10/2002 6:30
127 3353 5/1/2002 6:00 5/1/2002 8:30
207 2783 5/1/2002 6:00 5/1/2002 8:00
246 3634 5/1/2002 7:00 5/1/2002 19:00
212 277 5/1/2002 7:00 5/2/2002 6:00
155 1121 5/1/2002 7:00 5/2/2002 7:00
239 3257 5/1/2002 7:00 5/1/2002 12:00
40 1642 5/1/2002 7:00 5/1/2002 12:00
19 407 5/1/2002 7:00 5/1/2002 14:00
113 2009 5/1/2002 7:30 5/1/2002 9:00
87 2286 5/1/2002 7:30 5/1/2002 8:30
27 2064 5/1/2002 8:00 5/1/2002 19:30
148 1772 5/1/2002 8:00 5/1/2002 13:30
119 2665 5/1/2002 8:00 5/1/2002 13:30
226 2240 5/1/2002 8:00 5/1/2002 11:00
16 1789 5/1/2002 8:00 5/1/2002 11:00
...
COMP mth_st eq xdate.month(pickupda).
exe.
VECTOR hr_(1488 F8.0).
***I subtract 1 from the "to" value..this prevents overlap****
LOOP #i=
(ctime.minutes(pickupda-date.mdy(xdate.month(pickupda),1,xdate.year(pick
upda))))/30 to
(ctime.minutes(returnda-date.mdy(xdate.month(pickupda),1,xdate.year(pick
upda))))/30-1.
COMP hr_(#i) eq 1.
END LOOP.
RECODE hr_1 TO hr_1488 (SYSMIS=0).
EXE.
AGGREGATE
/OUTFILE='C:\duration-AGGR.SAV'
/BREAK=mth_st
/hr_1 TO hr_1488 = SUM(hr_1 TO hr_1488).
GET FILE='C:\duration-AGGR.SAV'.
FLIP mth_st hr_1 to hr_1488.
SAVE OUTFILE='C:\duration-flip.SAV'
/COMPRESSED.
*****results:
bin resources
5/1/2002 0:30 0
5/1/2002 1:00 0
5/1/2002 1:30 1
5/1/2002 2:00 1
5/1/2002 2:30 1
5/1/2002 3:00 1
5/1/2002 3:30 1
5/1/2002 4:00 1
5/1/2002 4:30 1
5/1/2002 5:00 1
5/1/2002 5:30 1
5/1/2002 6:00 4
5/1/2002 6:30 3
5/1/2002 7:00 9
5/1/2002 7:30 11
5/1/2002 8:00 16
5/1/2002 8:30 19
5/1/2002 9:00 21
5/1/2002 9:30 22
5/1/2002 10:00 26
5/1/2002 10:30 28
5/1/2002 11:00 29
5/1/2002 11:30 28
5/1/2002 12:00 30
5/1/2002 12:30 28
5/1/2002 13:00 33
5/1/2002 13:30 34
5/1/2002 14:00 30
5/1/2002 14:30 27
5/1/2002 15:00 26
5/1/2002 15:30 29
5/1/2002 16:00 26
5/1/2002 16:30 30
5/1/2002 17:00 35
5/1/2002 17:30 33
5/1/2002 18:00 36
5/1/2002 18:30 38
5/1/2002 19:00 39
5/1/2002 19:30 36
5/1/2002 20:00 33
5/1/2002 20:30 31
5/1/2002 21:00 31
5/1/2002 21:30 30
5/1/2002 22:00 28
5/1/2002 22:30 24
5/1/2002 23:00 24
5/1/2002 23:30 22
5/2/2002 0:00 24
5/2/2002 0:30 24
5/2/2002 1:00 24
5/2/2002 1:30 24
5/2/2002 2:00 26
5/2/2002 2:30 26
5/2/2002 3:00 26
5/2/2002 3:30 26
...
-----Original Message-----
From: Marks, Jim [mailto:jim.marks@lodgenet.com]
Sent: Friday, May 24, 2002 6:41 PM
To: SPSSX-L@LISTSERV.UGA.EDU
Subject: Re: occupied time from reservation information
Hello Stephen and list:
Unless you have a relatively short set of intervals, writing a set of IF
statements will quickly become cumbersome.
I faced a similar problem and used a VECTOR and LOOP syntax to create
the
bins and compute a value if a case was active within the interval. After
that, I used aggregate to get the total in the interval, then FLIP to
create
the final data file. Here is sample syntax, based on minute by minute
resource usage.
DATA LIST FREE /mth_st day_st hour_st mth_end day_end hour_end.
BEGIN DATA.
2 17 7 2 18 21
2 7 52 2 7 57
2 12 0 2 13 22
5 12 11 5 12 11
5 30 22 6 1 20
END DATA.
VECTOR hour_(721 F8.0).
LOOP #i= ((day_st)*24)+hour_st to ((day_end)*24)+hour_end.
COMP hour_(#i) eq 1.
END LOOP.
RECODE hour_1 TO hour_721 (SYSMIS=0).
EXE.
AGGREGATE
/OUTFILE='C:\duration-AGGR.SAV'
/BREAK=mth_st
/hour_1 TO hour_721 = SUM(hour_1 TO hour_721).
GET FILE='C:\duration-AGGR.SAV'.
FLIP mth_st hour_1 to hour_721.
SAVE OUTFILE='C:\duration-flip.SAV'
/COMPRESSED.
A couple of pointers--
This syntax will not handle cases that cross the month boundary-- e.g.
case
5 starts on month 5 and ends on month 6. You can edit the insert a case
that
starts in the first hour of the next month and ends with the original
case.
Also you will need to edit the ending of the original case (I did it
manually, since I only had a few cases.)
The time will be staggered. The variable hour_1 covers from midnight to
1:00
am, not 1:00 am to 2:00 am. I found it easier to work with numeric
variables
created using expressions such as
COMP hour_st eq xdate.hour(datetime).
instead of trying to manipulate data in DATETIME format.
You will need to do some figuring in the LOOP statement to determine how
to
identify half-hour intervals, but this should get you started.
Jim Marks
Market Analyst
LodgeNet Entertainment Corporation
605.988.1616
IMPORTANT NOTICE: This communication (including any attached files) is
intended for the entity to which it is directed and may contain
confidential
and proprietary information of LodgeNet Entertainment Corporation. The
communication is provided under the terms of the nondisclosure agreement
between LodgeNet and the intended recipient. If you are not acting
solely
on behalf of the intended recipient with respect to the receipt of this
communication, you are not authorized to receive, retain, print or
forward
this communication. In that event, you are directed to destroy this
communication (including any attached files), and to advise
ipmanager@lodgenet.com that you received this communication in error.
If
you are authorized to receive this communication on behalf of the
intended
recipient, you are obligated to treat it as confidential and proprietary
information of LodgeNet Entertainment Corporation, in accordance with
the
nondisclosure agreement mentioned above.
Good luck
-----Original Message-----
From: Stephen Oakley [mailto:soakley@zipcar.com]
Sent: Friday, May 24, 2002 3:15 PM
To: SPSSX-L@LISTSERV.UGA.EDU
Subject: Re: occupied time from reservation information
Thanks for all your help on this, but I think I made the problem more
complicated than it is:
What I want to see is for every interval (whether or not use starts on
that interval) how many resources are occupied for that interval.
from this
resource start end
16 3/3/01 5:30 PM 3/3/01 8:30 PM
4 3/3/01 7:00 PM 3/3/01 11:00 PM
14 3/3/01 7:30 PM 3/3/01 11:30 PM
to this
timeslice resources in use
3/3/01 6:00 PM 1
3/3/01 6:30 PM 1
3/3/01 7:00 PM 2
3/3/01 7:30 PM 3
3/3/01 8:00 PM 3
3/3/01 8:30 PM 2
...
I think I will need to create a set of bins for every half hour (or
whatever interval) and then evaluate each reservation against it, a la:
IF start<=timeslice AND end>timeslice THEN 1 ELSE 0.
I keep thinking there is a named procedure for this, but can't remember
what it is.
IF you have any ideas, let me know..thanks
Stephen Oakley
