Full Join-Calculate Sums but not enough joining keys

Zeus_Olympous — Tue, 25 Jul 2017 16:40:31 GMT

Dear all,

I have two datasets, HAVE and TEMP (quite many observations)

HAVE has the variables

FileNO

PRODUCT

Date_PMT

PMT_AMOUNT

TEMP has the same as TEMP variables plus two more

FileNO

PRODUCT

Date_PMT

PMT_AMOUNT

STATUS PRD

PMT_DTL_NO

The issue is that vars STATUSPRD and PMTDTLNO do not exist in HAVE (then I would have one-to-one maching)

My goal is to compare "Total PMT AMOUNT" by PRODUCT and by QTR_YR (can be derived from var Date_PMT)
from both Datasets.

I tried a FULL JOIN but one-to -many joins occur.

Then I run PROC TABULATE but now I have distorted "Total PMTAMOUNT" due to one-to -many joins.

I have attached a png that shows various cases (before the FULL JOIN)

Any suggestions /hints will be more than welcome.

Thanking you in advance.

Re: Full Join-Calculate Sums but not enough joining keys

HB — Tue, 25 Jul 2017 16:59:49 GMT

Are the file number and the product number the same for a given record across HAVE and TEMP? If so, then is it as simple as a compound key?

select

have.fileno,

have.product.

have.date_pmt,

have.pmt_amount,

temp.date_pmt,

temp.pmt_amount

from

have inner join temp on have.fileno = temp.fileno and have.product = temp.product