topic Re: Find last transaction date in SAS Enterprise Guide

Find last transaction date

sivastat08 — Wed, 14 Sep 2016 15:39:08 GMT

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

Re: Find last transaction date

Kurt_Bremser — Wed, 14 Sep 2016 15:39:34 GMT

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.

Re: Last.Transsection Date

sivastat08 — Wed, 14 Sep 2016 12:57:19 GMT

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;

Re: Find last transaction date

Kurt_Bremser — Wed, 14 Sep 2016 15:42:42 GMT

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.

Re: Last.Transsection Date

Doc_Duke — Wed, 14 Sep 2016 13:03:21 GMT

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.

Re: Last.Transsection Date

sivastat08 — Wed, 14 Sep 2016 13:05:31 GMT

Thanks for the replay.I will try.

Best,

Siva

Re: Last.Transsection Date

sivastat08 — Wed, 14 Sep 2016 13:09:09 GMT

Thanks .this is what i want.