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?
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.