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.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.