Desktop productivity for business analysts and programmers

Max date for id

Reply
Contributor
Posts: 42

Max date for id

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

Super User
Posts: 8,213

Re: Max date for id

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

 

Super User
Posts: 2,046

Re: Max date for id

[ Edited ]
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;
Super User
Posts: 2,046

Re: Max date for id

Posted in reply to 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;
Trusted Advisor
Posts: 1,387

Re: Max date for id

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.

Super User
Posts: 2,046

Re: Max date for id

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;
Ask a Question
Discussion stats
  • 5 replies
  • 230 views
  • 0 likes
  • 4 in conversation