I have two SAS datasets that I wish to merge and get the resultant data as in dataset3 below:
Dataset1(Sorted on URN)
URN | Post_Date | Pay_amount |
---|---|---|
121 | 10-Mar-15 | 100 |
121 | 13-Mar-15 | 100 |
Dataset 2(Sorted on URN)
URN | Trans_Date | Pay_Amount |
---|---|---|
121 | 11-Mar-15 | 100 |
121 | 14-Mar-15 | 100 |
Dataset3(Resultant Dataset)
URN | Trans_Date | Pay_Amount | Post_Date |
---|---|---|---|
121 | 11-Mar-15 | 100 | 10-Mar-15 |
121 | 14-Mar-15 | 100 | 13-Mar-15 |
The Post date field in Dataset 1 would always be less then or equal to the transdate field in datset 2 but the way I want the the resultant dataset is that the closest Trans_dt value(to the post date) should be combined, like in the resulatnt dataset above the observation in dataset1 with Post_date 10-Mar-15 should be merged with the observation with trans_dt 11-Mar-15 from dataset 2 and NOT with the observation with Trans_dt 14-Mar-15.
PS: the gap between post_date from dataset 1 and trans_date in dataset2 can be anything between 0 to 7 days.
Thanks.
Are there ever different numbers of records for combinations of URN Trans_date and Post_date? For example do you ever have a Trans_date without a post date? or Vice versa?
Are trans_dates ever duplicated for the same URN? How about Post_date?
Yes,there can be different number of records for a combination of URN ,trans_date and post_date.for example a URN may make a payment on march 4th which would make the trans_date as 4-Mar-15,the payment gets posted(or realised) on the system on 5-Mar-15 resulting in a post date of 5th march.
A URN may also make multiple payments of same or different amounts on the same date resulting in trans_dates and post_dates getting duplicated for a URN
The trans_date and post_date fields can never be blank for a URN who has made a payment
The same question as other's .
data data1 ; input urn Post_date : date9. pay_amount ; format Post_date date9.; cards; 121 10mar2015 100 121 13mar2015 100 ; run; data key; input urn Trans_date : date9. pay_amount ; format Trans_date date9.; cards; 121 11mar2015 100 121 14mar2015 100 ; run; data want; set data1(rename=(Post_date=days)) key(rename=(Trans_date=days) in=inb); by urn days; v=lag(urn);d=lag(days);i=lag(pay_amount); if inb and not lag(inb) and urn=lag(urn) ; format d date9.; drop urn pay_amount; run;
Xia Keshan
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.