BookmarkSubscribeRSS Feed
kfbaker0206
Fluorite | Level 6

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
8 REPLIES 8
novinosrin
Tourmaline | Level 20

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

kfbaker0206
Fluorite | Level 6

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
novinosrin
Tourmaline | Level 20

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
Fluorite | Level 6

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

PaigeMiller
Diamond | Level 26

@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
kfbaker0206
Fluorite | Level 6

It is showing up as blanks.

PaigeMiller
Diamond | Level 26

Show us your code.

 

Show us your SASLOG.

 

Show us the output data set.

--
Paige Miller
jebjur
SAS Employee

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 957 views
  • 0 likes
  • 4 in conversation