topic Re: Max date for id in SAS Enterprise Guide

Max date for id

hk2013 — Fri, 09 Mar 2018 23:11:04 GMT

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

Re: Max date for id

art297 — Fri, 09 Mar 2018 23:22:04 GMT

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

Re: Max date for id

novinosrin — Fri, 09 Mar 2018 23:38:34 GMT

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;

Re: Max date for id

novinosrin — Fri, 09 Mar 2018 23:56:32 GMT

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;

Re: Max date for id

mkeintz — Sat, 10 Mar 2018 17:16:51 GMT

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.

Re: Max date for id

novinosrin — Sat, 10 Mar 2018 18:28:52 GMT

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;