Multi Table interogation

[Asender]

Hi all.

I’m looking for a solution, if one exists, to a problem that I need to solve.

The basic premise behind what I am trying to do is the evaluation of how much money has been paid out under a record number.

This record will pay an amount out from a start date in the past to the current date or a stop date in the past.

I need to evaluate how much has been paid at any given time in the past. This date may change.

The following data is stored:

Current payment rate (stored in amount per annum)
Current frequency of payment.
Start date of record.

The main problem I have encountered is that the data over the course of the contract can and will change. Both frequency and payment amount.

I have constructed databases that contain the values of frequency and amounts over the course of the contracts life.

e.g.

Value per annum data

Record Effective_from Until Value
101 01/01/95 31/12/98 100000.00
101 01/01/99 31/10/02 90000.00
101 01/11/02 29/04/05 0.00

102 01/10/97 29/04/05 50000.00

Frequency Data

Record Effective_from Until Value
101 01/01/95 31/12/96 Monthly
101 01/01/97 29/04/05 Half Year

102 01/10/97 29/04/05 Monthly

This is pretty much where I get stuck and I would appreciate if anyone could offer me some help on where to go now.

I would like to be able to build code that ‘looks up’ information from these tables and manages to calculate what the total payment to the contract is.

This is where my limited experience grounds me. Any help and suggestions will be more than welcome.

Thanks for reading.

 

[ChrisW75]

I'm afraid that there isn't going to be a simple solution to this problem. It would have been easier if the from/to dates inboth datasets had matched up, that way you could join the two tables on record ID and start_date. If you can do that, it'll allow you to cut out some of the code below.
I think the simplest way is to break it down into a few stages.
First you need to join the two tables together using PROC SQL on record ID, using the effective from/to dates to make sure that you only join the correct frequency record to the correct per annum record.
Once you've done that, you should have multiple records for each record ID which show the frequency, date range and amount paid per annum.
The second step then is to calculate the amount paid for that particular record. The calculation will obviously depend on whether the payment is monthly, quarterly etc and you'll need to create a slightly different block of code for each option.
You will now have the same number of records, but each record should be loaded with the amount paid for that period.
The third step is the simplest, a simple Proc Summary by Record ID to sum up the amount paid.

This will be a reasonably long process to set up, I recommend breaking it down in stages and checking that it works after each bit, and that the results make sense. The most dangerous step is the first one as it'll take a little trial and error to get the date ranges matched up correctly. You'll need to experiment a bit, I recommend choosing a set of data which is representative of the most complex possible combination (101 there actually looks like it has everything in it) and track it through each stage to make sure that you have the right result.
If you need further help, post back.
Good luck!

 

[Asender]

Thanks ChrisW75.

I think I see where you are going with this.

I'll have a bash and see where I get.

Thanks again for your help.

Asender.