Find last transaction date

[sivastat08]

DATA WANT;
SET HAVE;
BY DUMPKEY ACCO_NO REPORTDATE PRODUCTIONDATE BALANCE_AMOUNT;

IF LAST.REPORTDATE THEN OUTPUT;
RUN;

 
for the combination of (Acco_NO ReportDate ProductionDate) these variables Last .ReportDate its not happning 

Pls help me to to get the last.ReportDate above mentioned combination.

please find the attached data for referance

[Kurt_Bremser]

Please supply your test data in text form; do a data step with cards like this

data have;
input var1 var2 var3;
cards;
1 2 3
4 5 6
7 8 9
;
run;

Nobody in his right mind opens a Excel file from the web.

[sivastat08]

Data Have;
input DumpKey Acco_NO ReportDate ProductionDate Balance_Amount;
cards;
1210 1210-33340 8/24/2016 20160808 300
1210 1210-33340 8/28/2016 20160808 355
1210 1210-33340 9/4/2016 20160808 222
1210 1210-33340 8/24/2016 20160829 310
1210 1210-33340 8/28/2016 20160829 30
1210 1210-33340 9/4/2016 20160829 550
1210 1022-21650 8/24/2016 20160822 345
1210 1022-21650 8/28/2016 20160822 335
1210 1022-21650 9/4/2016 20160822 60
1210 1022-21650 9/4/2016 20160809 50
;
run;

[Kurt_Bremser · Accepted solution]

See this:

Data Have;
input DumpKey Acco_NO :$10. ReportDate :mmddyy10. ProductionDate :yymmdd8. Balance_Amount;
format ReportDate ProductionDate mmddyy10.;
cards;
1210 1210-33340 8/24/2016 20160808 300
1210 1210-33340 8/28/2016 20160808 355
1210 1210-33340 9/4/2016 20160808 222
1210 1210-33340 8/24/2016 20160829 310
1210 1210-33340 8/28/2016 20160829 30
1210 1210-33340 9/4/2016 20160829 550
1210 1022-21650 8/24/2016 20160822 345
1210 1022-21650 8/28/2016 20160822 335
1210 1022-21650 9/4/2016 20160822 60
1210 1022-21650 9/4/2016 20160809 50
;
run;

proc sort data=have;
by DumpKey Acco_NO ReportDate ProductionDate Balance_Amount;
run;

data want;
set have;
by DumpKey Acco_NO ReportDate ProductionDate Balance_Amount;
if last.ReportDate then output;
run;

proc print data=want noobs;
run;

This gave me the output

Dump                                Production    Balance_
 Key     Acco_NO      ReportDate       Date        Amount

1210    1022-21650    08/24/2016    08/22/2016       345  
1210    1022-21650    08/28/2016    08/22/2016       335  
1210    1022-21650    09/04/2016    08/22/2016        60  
1210    1210-33340    08/24/2016    08/29/2016       310  
1210    1210-33340    08/28/2016    08/29/2016        30  
1210    1210-33340    09/04/2016    08/29/2016       550  

Which means that the condition was true 6 times.

[Doc_Duke]

In order to get last.reportdate for that combination, you have to change the order of the BY statement to put the productiondate before the reportdate.  You also need to sort the data.

[sivastat08]

Thanks for the replay.I will try.

Best,

Siva

[sivastat08]

Thanks .this is what i want.