topic Re: JOIN MAX DATE AMONG 2 DATA SET in SAS Programming

JOIN MAX DATE AMONG 2 DATA SET

Kumar1 — Thu, 17 Sep 2015 19:41:41 GMT

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?

Re: JOIN MAX DATE AMONG 2 DATA SET

Steelers_In_DC — Thu, 17 Sep 2015 20:04:44 GMT

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;

Re: JOIN MAX DATE AMONG 2 DATA SET

Ksharp — Fri, 18 Sep 2015 06:00:54 GMT

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;