BookmarkSubscribeRSS Feed
Kumar1
Calcite | Level 5

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?

2 REPLIES 2
Steelers_In_DC
Barite | Level 11

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;

Ksharp
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 389 views
  • 0 likes
  • 3 in conversation