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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.