Help using Base SAS procedures

Merging/ Joining Two SAS datasets

Reply
Occasional Contributor
Posts: 5

Merging/ Joining Two SAS datasets

I have two SAS datasets that I wish to merge and get the resultant data as in dataset3 below:

Dataset1(Sorted on URN)

URNPost_DatePay_amount
12110-Mar-15100
12113-Mar-15100

Dataset 2(Sorted on URN)

URNTrans_DatePay_Amount
12111-Mar-15100
12114-Mar-15100

Dataset3(Resultant Dataset)

URNTrans_DatePay_AmountPost_Date
12111-Mar-1510010-Mar-15
12114-Mar-1510013-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.

Super User
Posts: 10,500

Re: Merging/ Joining Two SAS datasets

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?

Occasional Contributor
Posts: 5

Re: Merging/ Joining Two SAS datasets

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

Super User
Posts: 9,681

Re: Merging/ Joining Two SAS datasets

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

Ask a Question
Discussion stats
  • 3 replies
  • 193 views
  • 0 likes
  • 3 in conversation