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

Sir , i want to find last.Last.ReportDateIST,

 

when i submit this code its not happning 

 

data have;
input DumpKey AccountNumber DOSDateKey ReportDateIST ISTProductionDateKey BalanceAmount;
cards;
1012 1012-44340 20160723 2016-08-24 20160808 680.00
1012 1012-44340 20160723 2016-09-04 20160829 680.00
1012 1022-50216 20160808 2016-08-28 20160822 260.00
1012 1022-50216 20160808 2016-09-04 20160822 260.00
1012 1022-50216 20160808 2016-09-11 20160822 260.00
1012 1022-50216 20160808 2016-09-18 20160822 260.00
1012 1025-52296 20160613 2016-08-24 20160809 50.00
1012 1025-52296 20160613 2016-08-28 20160824 50.00
1012 1025-52296 20160613 2016-09-04 20160824 50.00
1012 1025-52296 20160613 2016-09-11 20160906 50.00
1012 1028-54976 20160812 2016-09-04 20160829 275.00
1012 1028-54976 20160812 2016-09-11 20160829 275.00
1012 1028-54976 20160812 2016-09-18 20160829 275.00
1012 1030-45842 20160831 2016-09-18 20160912 400.00
1012 1031-9713 20160720 2016-08-24 20160802 260.00
1012 1034-24465 20160817 2016-09-04 20160829 545.00
1012 1034-24465 20160817 2016-09-11 20160906 545.00
1012 1035-55036 20160816 2016-09-04 20160829 600.00
1012 1035-55036 20160816 2016-09-11 20160829 600.00
1012 1035-55036 20160816 2016-09-18 20160829 600.00
1012 1038-52437 20160615 2016-08-24 20160718 150.00
1012 1038-52437 20160615 2016-08-28 20160718 150.00
1012 1038-52437 20160615 2016-09-04 20160718 150.00
1012 1038-52437 20160615 2016-09-11 20160718 150.00
1012 1038-52437 20160615 2016-09-18 20160718 150.00
1012 1051-9319 20160802 2016-08-24 20160816 510.00
1012 1051-9319 20160802 2016-09-04 20160829 510.00
1012 1074-14760 20160816 2016-09-04 20160829 460.00
1012 1074-14760 20160816 2016-09-11 20160906 460.00
1012 1077-11576 20160611 2016-08-24 20160818 75.00
;
run;

 

 

PROC SORT DATA=Have;
BY DUMPKEY ACCOUNTNUMBER DOSDATEKEY REPORTDATEIST ISTPRODUCTIONDATEKEY;
RUN;

 

DATA Want;
SET Have;
BY DUMPKEY ACCOUNTNUMBER DOSDATEKEY REPORTDATEIST ISTPRODUCTIONDATEKEY;

IF LAST.REPORTDATEIST THEN
OUTPUT;
RUN;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

PLEASE take ONE little look at your log. Your have dataset is not created with meaningful dates.

 

I guess your INPUT should look like that:

input DumpKey AccountNumber $ DOSDateKey ReportDateIST :YYMMDD10. ISTProductionDateKey BalanceAmount;

And you won't get less than the original 30 observations, as there are no repeats of all your BY variables.

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User

PLEASE take ONE little look at your log. Your have dataset is not created with meaningful dates.

 

I guess your INPUT should look like that:

input DumpKey AccountNumber $ DOSDateKey ReportDateIST :YYMMDD10. ISTProductionDateKey BalanceAmount;

And you won't get less than the original 30 observations, as there are no repeats of all your BY variables.

sivastat08
Pyrite | Level 9

I have Handled - 

 

ReportDateIST as a character string   $10. format  like "2016-08-24"

 

ISTProductionDateKey as a Numaric  11.  format  like "20160808"

 

how should i resolve this .

 

should i change any format thing.

 

 

Kurt_Bremser
Super User

Since you have several obvious dates in your data, I'd read them all into SAS date values:

 

input DumpKey AccountNumber $ DOSDateKey :yymmdd8. ReportDateIST :YYMMDD10. ISTProductionDateKey :yymmdd8. BalanceAmount;
format DOSDateKey ReportDateIST ISTProductionDateKey yymmddd10.;

This then enables correct sorting etc.

 

What would your wanted output dataset look like?

sivastat08
Pyrite | Level 9

For the combination  (ACCOUNTNUMBER  DOSDATEKEY  REPORTDATEIST  ISTPRODUCTIONDATEKEY), i want to find last Lastreportdate - Last.REPORTDATEIST thats it.

 

Siva

Kurt_Bremser
Super User

Sort

by ACCOUNTNUMBER  DOSDATEKEY  REPORTDATEIST;

and then use

BY ACCOUNTNUMBER  DOSDATEKEY  REPORTDATEIST;
if last.REPORTDATEIST;

in the data step.

If that does not get your intended result, then you need to post the example of your wanted dataset to illustrate your requirement.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 979 views
  • 2 likes
  • 2 in conversation