Max date for id

[hk2013]

I have data where i have ID and date and value  and need to find max date for each month for ID 

ID      Date               value 

1      1/20/2018            45

1      1/31/2018           100

1      2/20/2018            20

1      2/25/2018            87

2      1/17/2018            36

2      1/27/2018             45

2      2/02/2018             54

2      2/28/2018             68

what i want my data to look like

ID      Date               value

1      1/31/2018           100

1      2/25/2018            87

2      1/27/2018             45

2      2/28/2018             68

[art297]

Here is one way:

  input ID Date : mmddyy10. value;
  format Date date9.;
  cards;
1      1/20/2018            45
1      1/31/2018           100
1      2/20/2018            20
1      2/25/2018            87
2      1/17/2018            36
2      1/27/2018             45
2      2/02/2018             54
2      2/28/2018             68
;

data need;
  set have;
  month=month(date);
  day=day(date);
  year=year(date);
run;

proc sort data=need;
  by id year month descending day;
run;

proc sort data=need out=want nodupkey;
  by id year month;
run;

Art, CEO, AnalystFinder.com

[novinosrin]

data have;
 input ID Date : mmddyy10. value;
  format Date date9.;
  cards;
1      1/20/2018            45
1      1/31/2018           100
1      2/20/2018            20
1      2/25/2018            87
2      1/17/2018            36
2      1/27/2018             45
2      2/02/2018             54
2      2/28/2018             68
;


proc sql;
create table want as
select a.*
from have a, (select id,max(date) as max_date from have group by id, month(date)) b
where a.id =b.id  and a.date=b.max_date;
quit;

[novinosrin]

data have;
 input ID Date : mmddyy10. value;
  format Date date9.;
  cards;
1      1/20/2018            45
1      1/31/2018           100
1      2/20/2018            20
1      2/25/2018            87
2      1/17/2018            36
2      1/27/2018             45
2      2/02/2018             54
2      2/28/2018             68
;

proc sql;
create table want as
select id, date,  value
from have
group by id, month(date)
having max(date)=date;
quit;

[mkeintz]

Your data are already apparently sorted by ID and DATE.  You need a way to (1) determine if the record-in-hand is the last record for an id, or (2) the date of the next record is not the same month as current date:

data have;
  input id date :mmddyy10. value;
  format date date9.;
datalines;
1      1/20/2018            45
1      1/31/2018           100
1      2/20/2018            20
1      2/25/2018            87
2      1/17/2018            36
2      1/27/2018             45
2      2/02/2018             54
2      2/28/2018             68
run;

data want (drop=nxt_date);
  set have nobs=nrecs;
  by id;
  if _n_<nrecs then set have (firstobs=2 keep=date rename=(date=nxt_date));
  if last.id or intck('month',date,nxt_date)>0;
run;

The "set have (firstobs=2) provides a way to look ahead by one observation, to compare nxt_date to date.   It is the result clause of an "if _n_<nrecs" statement because without the IF test it would attempt to prematurely read beyond the end of data set HAVE, and the last obs of HAVE would not be output.

[novinosrin]

data have;
 input ID Date : mmddyy10. value;
  format Date date9.;
  cards;
1      1/20/2018            45
1      1/31/2018           100
1      2/20/2018            20
1      2/25/2018            87
2      1/17/2018            36
2      1/27/2018             45
2      2/02/2018             54
2      2/28/2018             68
;


data _null_;
if _n_=1 then do;
if 0 then set have;
dcl hash H (ordered:'y') ;
   h.definekey  ("id","month") ;
   h.definedata ("id","date","value") ;
   h.definedone () ;
end;
set have end=last;
month=month(date);
h.replace();
if last then h.output(dataset:'want');
run;