Desktop productivity for business analysts and programmers

Subtracting Triple variable

Reply
Occasional Contributor
Posts: 12

Subtracting Triple variable

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;

 

IDStart DateEnd DateDifference
1234527-Jan-1527-Apr-15.
123454-Oct-166-Jan-17526
1234511-Apr-1711-Jul-1795
Super User
Posts: 2,049

Re: Subtracting Triple variable

Posted in reply to kfbaker0206

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

Occasional Contributor
Posts: 12

Re: Subtracting Triple variable

Posted in reply to novinosrin

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.

IDStart DateEnd DateDifference
123454-Sep-149-Mar-15 
1234517-May-1726-Nov-17800
6789020-Jul-149-Oct-14 
6789010-Oct-1628-Dec-16 
6789010-Oct-161-Jan-17732
Super User
Posts: 2,049

Re: Subtracting Triple variable

Posted in reply to kfbaker0206

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;

?

 

 

Occasional Contributor
Posts: 12

Re: Subtracting Triple variable

Posted in reply to novinosrin

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

Respected Advisor
Posts: 3,261

Re: Subtracting Triple variable

Posted in reply to kfbaker0206

@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;
--
Paige Miller
Occasional Contributor
Posts: 12

Re: Subtracting Triple variable

Posted in reply to PaigeMiller

It is showing up as blanks.

Respected Advisor
Posts: 3,261

Re: Subtracting Triple variable

Posted in reply to kfbaker0206

Show us your code.

 

Show us your SASLOG.

 

Show us the output data set.

--
Paige Miller
SAS Employee
Posts: 19

Re: Subtracting Triple variable

Posted in reply to kfbaker0206

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;

Ask a Question
Discussion stats
  • 8 replies
  • 142 views
  • 0 likes
  • 4 in conversation