JOIN MAX DATE AMONG 2 DATA SET

Reply
New Contributor
Posts: 4

JOIN MAX DATE AMONG 2 DATA SET

DATA A;
INPUT LoanNo APAR ddmmyy8. Name$;
CARDS;
101 12092014 xyz
101 24082015 xyz
101 29092015 xyz
102 22062012 abc
102 02032015 abc
;
RUN;

DATA B;
INPUT LoanNo BillDate ddmmyy8. Amount;
CARDS;
101 09092014 1200
101 29082015 8000
101 14092015 100
102 22062015 8900
102 02032015 2300
;
RUN;

 

Above we have 2 DATA Sets. Amoung both i need maximum date value like below mentioned.

LoanNo Date             Name Price

101        29SEP2015 xyz      .
102        22JUN2015            8900

 

Can anyone help me in above issue?

Valued Guide
Posts: 860

Re: JOIN MAX DATE AMONG 2 DATA SET

Here is a solution:

 

data prep(keep=loanno date name price);
set   a
      b(rename=(amount=price));
by loanno;
date = max(apar,billdate);
format date date9.;
run;

proc sort data=prep;by loanno date;

data want;
set prep;
by loanno date;
if last.loanno;
run;

Super User
Posts: 10,020

Re: JOIN MAX DATE AMONG 2 DATA SET

It is 102 22062015 abc NOT 102 22062012 abc
DATA A;
INPUT LoanNo APAR ddmmyy8. Name$;
format APAR ddmmyy8.;
CARDS;
101 12092014 xyz
101 24082015 xyz
101 29092015 xyz
102 22062015 abc
102 02032015 abc
;
RUN;

DATA B;
INPUT LoanNo BillDate ddmmyy8. Amount;
CARDS;
101 09092014 1200
101 29082015 8000
101 14092015 100
102 22062015 8900
102 02032015 2300
;
RUN;
proc sql;
 create table want as
  select a.*,b.Amount
   from (select * from A group by LoanNo having APAR=max(APAR)) as a
    left join b on a.LoanNo=b.LoanNo and a.APAR=b.BillDate;
quit;
Ask a Question
Discussion stats
  • 2 replies
  • 209 views
  • 0 likes
  • 3 in conversation