04-12-2015 03:40 PM
I have two SAS datasets that I wish to merge and get the resultant data as in dataset3 below:
Dataset1(Sorted on URN)
Dataset 2(Sorted on URN)
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.
04-13-2015 11:26 AM
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?
04-13-2015 03:09 PM
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
04-14-2015 10:17 AM
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;