topic Re: Merging/ Joining Two SAS datasets in SAS Procedures

Merging/ Joining Two SAS datasets

mmehr3 — Sun, 12 Apr 2015 19:40:48 GMT

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.

Re: Merging/ Joining Two SAS datasets

ballardw — Mon, 13 Apr 2015 15:26:54 GMT

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?

Re: Merging/ Joining Two SAS datasets

mmehr3 — Mon, 13 Apr 2015 19:09:37 GMT

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

Re: Merging/ Joining Two SAS datasets

Ksharp — Tue, 14 Apr 2015 14:17:38 GMT

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