topic Re: Subtracting Triple variable in SAS Enterprise Guide

Subtracting Triple variable

kfbaker0206 — Tue, 19 Jun 2018 17:26:04 GMT

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

Re: Subtracting Triple variable

novinosrin — Tue, 19 Jun 2018 17:32:11 GMT

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

Re: Subtracting Triple variable

jebjur — Tue, 19 Jun 2018 17:40:45 GMT

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;

Re: Subtracting Triple variable

kfbaker0206 — Tue, 19 Jun 2018 17:45:24 GMT

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

Re: Subtracting Triple variable

novinosrin — Tue, 19 Jun 2018 17:53:43 GMT

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;

Re: Subtracting Triple variable

PaigeMiller — Tue, 19 Jun 2018 18:01:33 GMT

@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;

Re: Subtracting Triple variable

kfbaker0206 — Tue, 19 Jun 2018 19:43:56 GMT

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

Re: Subtracting Triple variable

kfbaker0206 — Tue, 19 Jun 2018 19:46:59 GMT

It is showing up as blanks.

Re: Subtracting Triple variable

PaigeMiller — Tue, 19 Jun 2018 20:16:34 GMT

Show us your code.

Show us your SASLOG.

Show us the output data set.