SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

To find out previous date out of one set.

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

To find out previous date out of one set.

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


Accepted Solutions
Solution
‎02-27-2018 08:09 AM
Super User
Posts: 10,681

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

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;

View solution in original post


All Replies
PROC Star
Posts: 1,209

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

[ Edited ]

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;
Respected Advisor
Posts: 4,665

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

[ Edited ]

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;
Solution
‎02-27-2018 08:09 AM
Super User
Posts: 10,681

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

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;
New Contributor
Posts: 3

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

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

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

@draycut - thank you so much it worked.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 213 views
  • 2 likes
  • 4 in conversation