BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sivastat08
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

6 REPLIES 6
Kurt_Bremser
Super User

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
Pyrite | Level 9

 

 

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

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
Rhodochrosite | Level 12

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
Pyrite | Level 9

Thanks for the replay.I will try.

 

Best,

Siva

sivastat08
Pyrite | Level 9

Thanks .this is what i want.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 6 replies
  • 2351 views
  • 4 likes
  • 3 in conversation