topic Re: Maximum date for each months in SAS Programming

Maximum date for each months

newuser2022 — Wed, 25 Oct 2023 09:39:31 GMT

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

Re: Maximum date for each months

PaigeMiller — Wed, 25 Oct 2023 09:49:40 GMT

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;

Re: Maximum date for each months

Kurt_Bremser — Wed, 25 Oct 2023 10:00:14 GMT

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

Re: Maximum date for each months

PaigeMiller — Wed, 25 Oct 2023 11:18:15 GMT

@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?

Re: Maximum date for each months

Kurt_Bremser — Wed, 25 Oct 2023 12:22:57 GMT

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

Re: Maximum date for each months

mkeintz — Thu, 26 Oct 2023 01:08:53 GMT

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?

Re: Maximum date for each months

Patrick — Thu, 26 Oct 2023 02:11:49 GMT

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;