To find out previous date out of one set.

[Sasforme]

Hello everyone,

I have the following input dataset which I created out

Of the requirements I got. For each id , I want to output the previous value of date based on last date in that id. The dataset is sorted on Id ascending and date ascending. I have used lag function to get previous date but here the requirement is to output date just one previous to the last entry of date for that id.

Any suggestions or help will be of great help for me.

Id.             Date

S001         1998/01/23        

S001         1999/01/24

S001         2000/01/25

S001         2012/09/25

S002        1988/01/23   

S002           1999/01/24

S002            2000/01/24

S002            2014/01/24

S002            2015/01/24

Output should be -

S001         2000/01/25

S002            2014/01/24

[PeterClemmensen]

Like this?

data have;
input Id$ Date:yymmdd10.;
format date yymmdd10.;
datalines;
S001 1998/01/23
S001 1999/01/24
S001 2000/01/25
S001 2012/09/25
S002 1988/01/23
S002 1999/01/24
S002 2000/01/24
S002 2014/01/24
S002 2015/01/24
;

data want;
 set have;
 by Id;
 date=ifn(first.Id, ., lag1(date));
 if last.id;
run;

[Patrick]

The following code will return all rows with the 2nd latest date per ID.

data have;
  input Id$ Date:yymmdd10.;
  format date yymmdd10.;
  someOtherVar=_n_;
  datalines;
S001 1998/01/23
S001 1999/01/24
S001 2000/01/25
S001 2000/01/25
S001 2012/09/25
S002 1988/01/23
S002 1999/01/24
S002 2000/01/24
S002 2014/01/24
S002 2015/01/24
S002 2015/01/24
;
run;

/* option 1 */
proc sql;
  create table want as
    select *
    from
      (
        select *
        from have
        group by id
        having date ne max(date) 
      )
    group by id
    having date = max(date) 
    ;
quit;

/* option 2*/
proc sort data=have out=want2;
  by id descending date;
run;

data want2(drop=_:);
  set want2;
  by id descending date;
  if first.id then _counter=1;
  else if first.date then _counter+1;
  if _counter=2 then output;
run;

[Ksharp · Accepted solution]

IF you have other variables need to retain.

data have;
input Id$ Date:yymmdd10.;
format date yymmdd10.;
datalines;
S001 1998/01/23
S001 1999/01/24
S001 2000/01/25
S001 2012/09/25
S002 1988/01/23
S002 1999/01/24
S002 2000/01/24
S002 2014/01/24
S002 2015/01/24
;
data temp;
 set have;
 by id;
 if last.id then delete;
run;
data want;
 set temp;
 by id;
 if last.id;
run;

[Sasforme]

Thanks for your reply.
What is the meaning of date = ifn? I am feeling this for first time

[Sasforme]

@PeterClemmensen - thank you so much it worked.