DATA Step, Macro, Functions and more

Full Join-Calculate Sums but not enough joining keys

Reply
Contributor
Posts: 45

Full Join-Calculate Sums but not enough joining keys

Dear all,

 

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

 

HAVE has the variables

 

FileNOPRODUCTDate_PMTPMT_AMOUNT

 

TEMP has the same as TEMP variables plus two more

 

FileNOPRODUCTDate_PMTPMT_AMOUNT STATUS PRDPMT_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.

 

2017-07-25_181403.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Regular Contributor
Regular Contributor
Posts: 162

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

Posted in reply to Zeus_Olympous

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

 

 

Ask a Question
Discussion stats
  • 1 reply
  • 69 views
  • 0 likes
  • 2 in conversation