matching two files by rates

[jimjohn]

can someone plz help me with this:

i have two different spss data files that i want to combine
File A has the following columns: Ratedate, Rate

File B has the following columns: IssueDate, AsOfDate, .......(and many other columns)

File A contains all possible dates. So any date in File B will be in File A.

I want to combine these two sheets into one and create two new columns: IssueDateRate and AsOfDateRate.

So, for each IssueDate, I want to find the same corresponding date in Ratedate from File A, and then return the rate that corresponds to that date under issueDateRate

And for each AsOfDate, I want to find the corresponding date in Ratedate from File A, and then return the rate that corresponds to that date under AsOfDateRate.

Any ideas how i can do this? Thanks!

[Richard Ristow]

At 04:13 PM 5/6/2008, jimjohn wrote:

>i have two different spss data files that i want to combine
>File A has the following columns: Ratedate, Rate
>
>File B has the following columns: IssueDate, AsOfDate, .......(and
>many other columns)
>
>I want to combine these two sheets into one and create two new columns:
>IssueDateRate and AsOfDateRate. For each IssueDate, I want to find
>the corresponding date in Ratedate from File A, and then return the
>rate that corresponds to that date under issueDateRate
>
>And for each AsOfDate, I want to find the corresponding date in
>Ratedate from File A, and then return the rate that corresponds to
>that date under AsOfDateRate.

The natural way to do this is in two passes. It will be a little slow
if File_B is large, since it takes (depending on the original and
desired sort order of File_B) up to three sort operations. The
following is not tested. And it assumes that File_A is sorted by
RateDate. (If File_B is originally sorted by either date, you can
skip one SORT.)

GET FILE=File_A.
SORT CASES BY IssueDate.
MATCH FILES
   /FILE=*
   /TABLE=File_B/RENAME=(Ratedate  Rate
                        =IssueDate IssueDateRate)
   /BY IssueDate.
SORT CASES BY AsOfDate.
MATCH FILES
   /FILE=*
   /TABLE=File_B/RENAME=(Ratedate  Rate
                        =AsOfDate  AsOfDateRate)
   /BY AsOfDate.
SORT CASES /* again, if necessary */.

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

[jimjohn]

thanks a lot richard! i did try this and it didn't work though. according to the errors provided, the variables didn't exist? i will try it again, but just a quick question, do i have to have both files open in the beginning before i start running this syntax. thx.

Richard Ristow wrote

At 04:13 PM 5/6/2008, jimjohn wrote:

>i have two different spss data files that i want to combine
>File A has the following columns: Ratedate, Rate
>
>File B has the following columns: IssueDate, AsOfDate, .......(and
>many other columns)
>
>I want to combine these two sheets into one and create two new columns:
>IssueDateRate and AsOfDateRate. For each IssueDate, I want to find
>the corresponding date in Ratedate from File A, and then return the
>rate that corresponds to that date under issueDateRate
>
>And for each AsOfDate, I want to find the corresponding date in
>Ratedate from File A, and then return the rate that corresponds to
>that date under AsOfDateRate.

The natural way to do this is in two passes. It will be a little slow
if File_B is large, since it takes (depending on the original and
desired sort order of File_B) up to three sort operations. The
following is not tested. And it assumes that File_A is sorted by
RateDate. (If File_B is originally sorted by either date, you can
skip one SORT.)

GET FILE=File_A.
SORT CASES BY IssueDate.
MATCH FILES
   /FILE=*
   /TABLE=File_B/RENAME=(Ratedate  Rate
                        =IssueDate IssueDateRate)
   /BY IssueDate.
SORT CASES BY AsOfDate.
MATCH FILES
   /FILE=*
   /TABLE=File_B/RENAME=(Ratedate  Rate
                        =AsOfDate  AsOfDateRate)
   /BY AsOfDate.
SORT CASES /* again, if necessary */.

=====================
To manage your subscription to SPSSX-L, send a message to
[e-mail] (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 04:13 PM 5/6/2008, jimjohn wrote:

>>i have two different spss data files that i want to combine

I posted some syntax in response, and at 11:35 AM 5/8/2008, he wrote, off-list:

>i did try this and it didn't work though. according to the errors
>provided, the variables didn't exist?

It's very hard to tell what you ran into. When you're having trouble
with syntax and get error messages, it helps a lot if you post the
exact syntax, and the exact error message.

>i will try it again, but just a quick question, do i have to have
>both files open in the beginning before i start running this syntax.

You have to have both *available*, but not necessarily open. When I
say 'available', I mean that File A can be referred to in SPSS
statements as 'File_A' (in my example syntax, but use whatever name
suits your purposes), and File B can be referred to as 'File_B'. This
works if 'File_A' and 'File_B' are
. Actual file names (with no extensions) in the default SPSS directory
. File handles (see the FILE HANDLE command)
. Datasets, in SPSS 14 and later.

How's it going?

-Good luck to you,
  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

[Richard Ristow]

At 01:03 PM 5/14/2008, [hidden email] wrote, off-list:

>My first file (RatesCofSwap.sav) contains the variable IssueDt (it
>used to be Ratedate but I changed it to be the same variable as in
>the other file), as well as the following other variables: Swaprate, Cofrate.
>
>My second file (3buckets certlevel) contains the following
>variables: IssueDt, AsOfDt, and many other variables.
>
>So I'm trying to use your syntax just to match these two files by
>IssueDt. I know that my first file (RatesCofSwap) contains all the
>dates from 11/01/1994 to 5/6/2008.

The question is, does it contain each date *only once*?

>There may be different cases with the same date in both files,

If that means what it looks like it means, that's your problem. Your
"RatesCofSwap.sav" is the lookup table, and it won't work if it has
more than one record for any date. (If there are duplicates, the
lookup is ambiguous: which of the various matching values do you append?)

>I want the match to do is whatever IssueDt appears in the second
>file (3buckets certlevel), I want to have the correct Swaprate for that date.

Right. But if the value of IssueDt appears more than once in
RatesCofSwap, which Swaprate is correct? (If they're always the same
so there's no real ambiguity, it's easy to use SPSS to eliminate the
duplicates.)

>So I tried the following syntax:
>
>GET FILE='I:\qrst\research_data\Wait and See' +
>            '\200803\certlevel\'               +
>            'Copy of 3buckets certlevel.sav'.
>MATCH FILES
>   /FILE=*
>   /TABLE='I:\qrst\research_data\Wait and See' +
>            '\200803\certlevel\'               +
>            'Copy of RatesCofSwap.sav'
>   /BY IssueDt.
>
>and i get the following error:
>
>File #2
>      KEY:   1E+010
>>Error # 5131
>>Duplicate key on a TABLE file.

Yes, that's what happens if a key value (the date) occurs more than
once in 'Copy of RatesCofSwap'. I'm sorry that SPSS doesn't display
the date value as a date. But, anyway, you need to find and eliminate
multiple record for any date in Copy of 'RatesCofSwap'. If "Swaprate"
has two different values for one day, you'll just have to decide
which to take.

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

[jimjohn]

Thanks so much Richard! That is the problem then, because there can be
the same date appearing more than once in RatesCofSwap. I think i do
know hwo to get rid of the duplicate dates in RatesCofSwap though, so
I'll try that and then I'll see if the syntax works.


Azam


Quoting Richard Ristow <[hidden email]>:

> At 01:03 PM 5/14/2008, [hidden email] wrote, off-list:
>
>> My first file (RatesCofSwap.sav) contains the variable IssueDt (it
>> used to be Ratedate but I changed it to be the same variable as in
>> the other file), as well as the following other variables:
>> Swaprate, Cofrate.
>>
>> My second file (3buckets certlevel) contains the following
>> variables: IssueDt, AsOfDt, and many other variables.
>>
>> So I'm trying to use your syntax just to match these two files by
>> IssueDt. I know that my first file (RatesCofSwap) contains all the
>> dates from 11/01/1994 to 5/6/2008.
>
> The question is, does it contain each date *only once*?
>
>> There may be different cases with the same date in both files,
>
> If that means what it looks like it means, that's your problem. Your
> "RatesCofSwap.sav" is the lookup table, and it won't work if it has
> more than one record for any date. (If there are duplicates, the lookup
> is ambiguous: which of the various matching values do you append?)
>
>> I want the match to do is whatever IssueDt appears in the second
>> file (3buckets certlevel), I want to have the correct Swaprate for
>> that date.
>
> Right. But if the value of IssueDt appears more than once in
> RatesCofSwap, which Swaprate is correct? (If they're always the same so
> there's no real ambiguity, it's easy to use SPSS to eliminate the
> duplicates.)
>
>
>> So I tried the following syntax:
>>
>> GET FILE='I:\qrst\research_data\Wait and See' +
>>           '\200803\certlevel\'               +
>>           'Copy of 3buckets certlevel.sav'.
>> MATCH FILES
>>  /FILE=*
>>  /TABLE='I:\qrst\research_data\Wait and See' +
>>           '\200803\certlevel\'               +
>>           'Copy of RatesCofSwap.sav'
>>  /BY IssueDt.
>>
>> and i get the following error:
>>
>> File #2
>>     KEY:   1E+010
>>> Error # 5131
>>> Duplicate key on a TABLE file.
>
> Yes, that's what happens if a key value (the date) occurs more than
> once in 'Copy of RatesCofSwap'. I'm sorry that SPSS doesn't display the
> date value as a date. But, anyway, you need to find and eliminate
> multiple record for any date in Copy of 'RatesCofSwap'. If "Swaprate"
> has two different values for one day, you'll just have to decide which
> to take.

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