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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: