Subtracting Triple variable

[kfbaker0206]

Hello I have the same ID variable that has different start dates and end dates. I want to subtract the last (3rd) start date from the first end date (last.startdate-first.enddate). Please see table below. It is only subtracting the second start date from the first end date. I am using EG 7.15. SAS code is preferred. The dates that I want subtracted are in bold.

data want;
  set test ;
  by ID;
  difference=startDate-lag(endDate);
  if first.ID then difference=.;
  run;

ID	Start Date	End Date	Difference
12345	27-Jan-15	27-Apr-15	.
12345	4-Oct-16	6-Jan-17	526
12345	11-Apr-17	11-Jul-17	95

[novinosrin]

do you just want the difference between max(startdate)-min(enddate) ?

[kfbaker0206]

Yes based on id. For instance if an ID is listed twice, then subtract the last start date from first end date. If the id is listed 3 times, the third start date-first end date. Items I want subtracted are listed in bold. Thanks.

ID	Start Date	End Date	Difference
12345	4-Sep-14	9-Mar-15
12345	17-May-17	26-Nov-17	800
67890	20-Jul-14	9-Oct-14
67890	10-Oct-16	28-Dec-16
67890	10-Oct-16	1-Jan-17	732

[novinosrin]

Do you like Proc SQL

data have;
input ID	StartDate :date7.	EndDate	:date7.;
format StartDate	EndDate	date7.; 
cards;
12345	4-Sep-14	9-Mar-15	 
12345	17-May-17	26-Nov-17	800
67890	20-Jul-14	9-Oct-14	 
67890	10-Oct-16	28-Dec-16	 
67890	10-Oct-16	1-Jan-17	732
;

proc sql;
create table want as
select *,ifn(enddate=max(enddate),max(StartDate)-min(EndDate),.) as diff
from have
group by id
order by id, startdate;
quit;

?

[kfbaker0206]

No. I don't know how to locate the directory of the file so I can put it after infile.  

[PaigeMiller]

---

@kfbaker0206 wrote:

Yes based on id. For instance if an ID is listed twice, then subtract the last start date from first end date. If the id is listed 3 times, the third start date-first end date. Items I want subtracted are listed in bold. Thanks.

ID	Start Date	End Date	Difference
12345	4-Sep-14	9-Mar-15
12345	17-May-17	26-Nov-17	800
67890	20-Jul-14	9-Oct-14
67890	10-Oct-16	28-Dec-16
67890	10-Oct-16	1-Jan-17	732

---

data want;
    set have;
    by id;
    retain first_end_date;
    if first.id then first_end_date=end_date;
    if last.id then difference=start_date-first_end_date;
run;

[kfbaker0206]

It is showing up as blanks.

[PaigeMiller]

Show us your code.

Show us your SASLOG.

Show us the output data set.

[jebjur]

Does this give you the results you want?

data test;
input ID StDate :date9. EndDate :date9.;
format StDate date9. EndDate date9.;
cards;
12345 27Jan2015 27Apr2015
12345 4Oct2016 6Jan2017
12345 11Apr2017 11Jul2017
;
run;

data want (drop=dt1);
set test ;
by ID;
retain dt1;
if first.ID then dt1=EndDate;
  if last.ID then do;
    difference=StDate-dt1;
    output;
 end;
run;