topic Re: generate previous visit's enddate from next visit's startdate -1 in SAS Programming

generate previous visit's enddate from next visit's startdate -1

fengyuwuzu — Mon, 17 Jun 2019 18:00:55 GMT

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

Re: generate previous visit's enddate from next visit's startdate -1

PaigeMiller — Mon, 17 Jun 2019 18:04:20 GMT

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;

Re: generate previous visit's enddate from next visit's startdate -1

Tom — Mon, 17 Jun 2019 18:18:25 GMT

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             .

Re: generate previous visit's enddate from next visit's startdate -1

fengyuwuzu — Mon, 17 Jun 2019 19:47:02 GMT

Thank you Tom and Paige!