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
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;
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;
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.