Desktop productivity for business analysts and programmers

Find last transaction date

Accepted Solution Solved
Reply
Contributor
Posts: 61
Accepted Solution

Find last transaction date

[ Edited ]

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


Accepted Solutions
Solution
‎09-14-2016 11:40 AM
Super User
Posts: 7,465

Re: Find last transaction date

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 7,465

Re: Find last transaction date

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 61

Re: Last.Transsection Date

 

 

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;

Solution
‎09-14-2016 11:40 AM
Super User
Posts: 7,465

Re: Find last transaction date

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 2,114

Re: Last.Transsection Date

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.

Contributor
Posts: 61

Re: Last.Transsection Date

Thanks for the replay.I will try.

 

Best,

Siva

Contributor
Posts: 61

Re: Last.Transsection Date

Thanks .this is what i want.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 693 views
  • 4 likes
  • 3 in conversation