MATCHing a date range to a date

[Nico Munting]

At the moment I am facing a challenge for which I have not been able to
find an answer yet. I am hoping that someone reading this list will be able
to help me.

I have a dataset with estimates and a dataset with actual values
encountered. These datasets contain the following data:

ESTIMATES

dt_frm       dt_to         profile   estim
1-JAN-2004   31-DEC-2004         1    0.87
1-JAN-2004   31-DEC-2004         2    0.45
1-JAN-2005    5-JUN-2005         1    0.77
1-JAN-2005   30-JUN-2005         2    0.48


ACTUAL_VALUES

date          profile   actual
31-JAN-2004         1     0.95
30-JUN-2004         1     0.85
31-JAN-2004         2     0.40
30-JUN-2004         2     0.50
25-FEB-2005         1     0.80
31-MAR-2005         2     0.49


The from and to dates in the datafile containing the estimates are unique
on a given date and would not overlap in combination with a profile.

In order to determine to what estimate a certain actual value should be
compared with I wanted to do a match on profile with a condition that DATE
should lie between the dates in DT_FRM and DT_TO. Is there a way in which I
could write SPSS-code doing this type of join. I have been trying to come
up with a way that could do the join automatically, but I have not been
able to solve this problem.

Any help would be appreciated!

Regards,

Nico Munting

[Albert-Jan Roskam]

Hi Nico!

How do you know when two records from actual and
estimated values are corresponding records? Are they
sorted in such a way that record #1,2,3,n in Estimated
corresponds to record #1,2,3,n in Actual? Quite scary,
as it could really mess up things when you sort the
data.

I am going to assume you named your datasets
'estimated' and 'actual'.

dataset activate estimated.
compute casenum = $casenum.
dataset name estimated.

dataset activate actual.
compute casenum = $casenum.
dataset name actual.

match files
  / file = estimated
  / file = actual
  / by = casenum.
exe.

compute flag = 0.
if (date ge dt_frm and date le dt_to) flag = 1.
value labels flag
   0 'outside range'
   1 'within range'.
fre flag.

Please note that this is not tested and that this
hinges upon the 'casenum' assumption.

Cheers!!
Albert-Jan


--- Nico Munting <[hidden email]> wrote:

> At the moment I am facing a challenge for which I
> have not been able to
> find an answer yet. I am hoping that someone reading
> this list will be able
> to help me.
>
> I have a dataset with estimates and a dataset with
> actual values
> encountered. These datasets contain the following
> data:
>
> ESTIMATES
>
> dt_frm       dt_to         profile   estim
> 1-JAN-2004   31-DEC-2004         1    0.87
> 1-JAN-2004   31-DEC-2004         2    0.45
> 1-JAN-2005    5-JUN-2005         1    0.77
> 1-JAN-2005   30-JUN-2005         2    0.48
>
>
> ACTUAL_VALUES
>
> date          profile   actual
> 31-JAN-2004         1     0.95
> 30-JUN-2004         1     0.85
> 31-JAN-2004         2     0.40
> 30-JUN-2004         2     0.50
> 25-FEB-2005         1     0.80
> 31-MAR-2005         2     0.49
>
>
> The from and to dates in the datafile containing the
> estimates are unique
> on a given date and would not overlap in combination
> with a profile.
>
> In order to determine to what estimate a certain
> actual value should be
> compared with I wanted to do a match on profile with
> a condition that DATE
> should lie between the dates in DT_FRM and DT_TO. Is
> there a way in which I
> could write SPSS-code doing this type of join. I
> have been trying to come
> up with a way that could do the join automatically,
> but I have not been
> able to solve this problem.
>
> Any help would be appreciated!
>
> Regards,
>
> Nico Munting
>

Cheers!
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Did you know that 87.166253% of all statistics claim a precision of results that is not justified by the method employed? [HELMUT RICHTER]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


      ____________________________________________________________________________________
Luggage? GPS? Comic books?
Check out fitting gifts for grads at Yahoo! Search
http://search.yahoo.com/search?fr=oni_on_mail&p=graduation+gifts&cs=bz

[Nico Munting]

The idea was to match an actual value to an estimate that was produced
beforehand. The match should be made based on the profile and the date. For
me the challenge was that the estimates have ranges of dates for which the
estimates are valid and the actual values have a specific date on which
this value was encountered. Therefore, they cannot be directly matched
through a MATCH command.

Raynald Levesque mailed this clever solution to me that solved the problem
for:


Hi Nico,

I note that there is a variable number of actual values for a given
combination of date range and profile.

This syntax does the job (initial sorting of data files is not important).

DATASET CLOSE ALL.

DATA LIST LIST /dt_frm(DATE11) dt_to(DATE11)  profile(F8) estim(F8.2).
BEGIN DATA
1-JAN-2004   31-DEC-2004         1    0.87
1-JAN-2004   31-DEC-2004         2    0.45
1-JAN-2005    5-JUN-2005         1    0.77
1-JAN-2005   30-JUN-2005         2    0.48
END DATA.
DATASET NAME estimates.

DATA LIST LIST /dtActual(DATE11) profile(F8) actual(F8.2).
BEGIN DATA
31-JAN-2004         1     0.95
30-JUN-2004         1     0.85
31-JAN-2004         2     0.40
30-JUN-2004         2     0.50
25-FEB-2005         1     0.80
31-MAR-2005         2     0.49
END DATA.
COMPUTE dt_frm = dtActual .
FORMATS dt_frm(DATE11).

ADD FILES FILE=estimates
    /FILE=* .
SORT CASES BY profile dt_frm.
DO IF profile=LAG(profile) AND RANGE(dtActual, LAG(dt_Frm), LAG(dt_to)) .
-  COMPUTE dt_frm = LAG( dt_Frm).
-  COMPUTE dt_to = LAG( dt_to).
-  COMPUTE estim = LAG(estim).
END IF.
LIST.


* This is the result of the LIST command.

     dt_frm       dt_to             profile     estim    dtActual    actual

01-JAN-2004 31-DEC-2004        1       .87           .       .
01-JAN-2004 31-DEC-2004        1       .87 31-JAN-2004       .95
01-JAN-2004 31-DEC-2004        1       .87 30-JUN-2004       .85
01-JAN-2005 05-JUN-2005        1       .77           .       .
01-JAN-2005 05-JUN-2005        1       .77 25-FEB-2005       .80
01-JAN-2004 31-DEC-2004        2       .45           .       .
01-JAN-2004 31-DEC-2004        2       .45 31-JAN-2004       .40
01-JAN-2004 31-DEC-2004        2       .45 30-JUN-2004       .50
01-JAN-2005 30-JUN-2005        2       .48           .       .
01-JAN-2005 30-JUN-2005        2       .48 31-MAR-2005       .49


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

Regards

Raynald Levesque
www.spsstools.net