Maximum date for each months

[newuser2022]

I have a big dataset and which contain id's and daily values for it for a few years. I want to select for each id's the maximum day for each month for this few years. 

ID DATE

2  01JAN202

2  02JAN2020

2  03JAN2020

2  22FEB2020

2  24FEB2020

3  01JAN2020

3 02JAN2020

3 22FEB2020

3 24FEB2020

4 01JAN2020

4 02JAN2020

4 22FEB2021

4 23FEB2021

4 25MAR2021

4 26MAR2021

4 27MAR2021

For the above sample I need to get the data as below:

2  02JAN2020

2  24FEB2020

3 02JAN2020

3 24FEB2020

4 02JAN2020

4 23FEB2021

4 27MAR2021

[PaigeMiller]

Assuming the data are numeric and not character (are they? you didn't say) this is a job for PROC SUMMARY

proc summary data=have nway;
    class id date;
    format date monyy7.;
    var date;
    output out=want max= ;
run;
proc datasets library=work nolist;
    modify want;
    format date date9.;
run;

[Kurt_Bremser]

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

[PaigeMiller]

---

@Kurt_Bremser wrote:

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

---

Doesn't this fail if you have dates in January (or any month) in two different years?

[Kurt_Bremser]

Correct. year(date) should be inserted in the GROUP BY. Or we should use put(date,yymmn6.).

[mkeintz]

Apparently your data is sorted by ID/DATE, and you want the latest date in each month.

Then:

data have;
  input ID DATE :date9. ;
  format date date9. ;
datalines;
2  01JAN2020
2  02JAN2020
2  03JAN2020
2  22FEB2020
2  24FEB2020
3  01JAN2020
3 02JAN2020
3 22FEB2020
3 24FEB2020
4 01JAN2020
4 02JAN2020
4 22FEB2021
4 23FEB2021
4 25MAR2021
4 26MAR2021
4 27MAR2021
run;
data want (drop=nxt_date);
  set have (keep=id);
  by id;
  merge have    have(firstobs=2 keep=date rename=(date=nxt_date));
  if last.id=1 or intck('month',date,nxt_date)>0;
run;

The self-merge in the merge statement provides a way to look ahead one observation (using the FIRSTOBS=2 option), and compare the current date to the following date (nxt_date).

BTW, you included a 03jan2020 in your data for ID=1.  So shouldn't that be the expected value for your result?

[Patrick]

Here a SAS SQL option.

data have;
 input ID DATE:date9.;
 format date date9.;
 datalines;
2 01JAN2020
2 02JAN2020
2 03JAN2020
2 22FEB2020
2 24FEB2020
3 01JAN2020
3 02JAN2020
3 22FEB2020
3 24FEB2020
4 01JAN2020
4 02JAN2020
4 22FEB2021
4 23FEB2021
4 25MAR2021
4 26MAR2021
4 27MAR2021
;

proc sql;
  create table want as
  select id, max(date) as date format=date9.
  from have
  group by id, intnx('month',date,0,'b')
  ;
quit;