topic Re: To find out previous date out of one set. in SAS Data Management

To find out previous date out of one set.

Sasforme — Tue, 27 Feb 2018 10:44:49 GMT

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

Re: To find out previous date out of one set.

PeterClemmensen — Tue, 27 Feb 2018 11:01:10 GMT

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;

Re: To find out previous date out of one set.

Patrick — Tue, 27 Feb 2018 12:47:21 GMT

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;

Re: To find out previous date out of one set.

Ksharp — Tue, 27 Feb 2018 12:44:34 GMT

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;

Re: To find out previous date out of one set.

Sasforme — Tue, 27 Feb 2018 13:02:15 GMT

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

Re: To find out previous date out of one set.

Sasforme — Wed, 28 Feb 2018 10:08:19 GMT

@PeterClemmensen - thank you so much it worked.