data have;
input id $ line startdate $10.;
datalines;
A 1 2015-06-20
A 2 2015-07-31
A 3 2015-08-27
B 1 2016-05-01
B 2 2016-08-02
;
RUN;
what I want:
ID line startdate enddate
A 1 2015-06-20 2015-07-30
A 2 2015-07-31 2015-08-26
A 3 2015-08-27 .
B 1 2016-05-01 2016-08-01
data have;
input id $ line startdate yymmdd10.;
format startdate yymmdd10.;
datalines;
a 1 2015-06-20
a 2 2015-07-31
a 3 2015-08-27
b 1 2016-05-01
b 2 2016-08-02
;
run;
proc sql;
create table want as select h.*,j.startdate-1 as enddate format=yymmdd10.
from have as h left join have as j
on h.id=j.id and h.line=j.line-1;
quit;
data have;
input id $ line startdate yymmdd10.;
format startdate yymmdd10.;
datalines;
a 1 2015-06-20
a 2 2015-07-31
a 3 2015-08-27
b 1 2016-05-01
b 2 2016-08-02
;
run;
proc sql;
create table want as select h.*,j.startdate-1 as enddate format=yymmdd10.
from have as h left join have as j
on h.id=j.id and h.line=j.line-1;
quit;
First thing is to define your date variable to have dates and not character strings.
You can use this trick using an extra SET with FIRSTOBS=2 to create a "look ahead" or "lead" value for start date.
Take care that when you are on the last observation for a group that you not try to use the date from the first observation of the next group by using BY variable processing.
data have;
input id $ line startdate :yymmdd10.;
format startdate yymmdd10.;
datalines;
A 1 2015-06-20
A 2 2015-07-31
A 3 2015-08-27
B 1 2016-05-01
B 2 2016-08-02
;
data want ;
set have ;
by id;
set have(firstobs=2 keep=startdate rename=(startdate=enddate)) have(drop=_all_ obs=1);
if last.id then call missing(enddate);
if not missing(enddate) then enddate=enddate-1;
run;
Obs id line startdate enddate 1 A 1 2015-06-20 2015-07-30 2 A 2 2015-07-31 2015-08-26 3 A 3 2015-08-27 . 4 B 1 2016-05-01 2016-08-01 5 B 2 2016-08-02 .
Thank you Tom and Paige!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.