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
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.
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.
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;
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.
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.
Thanks for the replay.I will try.
Best,
Siva
Thanks .this is what i want.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.