BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kfbaker0206
Fluorite | Level 6

I would like sas to subtract the second start date minus the first end date based on ID. For example there is an ID with the number 1 with two different start dates and end dates. I would like for ID 1 to subtract the SECOND start date (17May2017) from the FIRST end date (09MAR2015). This was from an excel file. Please see file below. I would prefer SAS code as opposed to sql. I am new to sas and I do not know where to begin. Also the original data has about 4K IDs. I have SAS EG 7.15. Any help would be greatly appreciated. Thanks.  

 startYearIDDuplicatesstartDateendDate2nd Start Date-First End Date
120121204Sep201409Mar2015D2-E1
220181 17May201726Nov2017
320122203Jan201415Jun2014D5-E4
420182 26May201708Nov2017
520163203Jan201516Jun2015D7-E6
620173 02May201714Oct2017
1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

Assuming you have only two records for each ID and everything is sorted properly

 

UNTESTED CODE

data want;
    set have;
    by id;
    prev_enddate=lag(enddate);
    if last.id then delta=startdate-prev_enddate;
run;    

  

--
Paige Miller

View solution in original post

6 REPLIES 6
novinosrin
Tourmaline | Level 20

Are your ID values consistently sets of 2 like your sample?

kfbaker0206
Fluorite | Level 6

No they are 2,3,4,5

PaigeMiller
Diamond | Level 26

Assuming you have only two records for each ID and everything is sorted properly

 

UNTESTED CODE

data want;
    set have;
    by id;
    prev_enddate=lag(enddate);
    if last.id then delta=startdate-prev_enddate;
run;    

  

--
Paige Miller
novinosrin
Tourmaline | Level 20

Well then, I would also go with the same assumption 🙂

 

data have;
infile cards truncover;
input obs startYear	ID	Duplicates	(startDate		endDate) (:date9.);
format startDate		endDate date9.;
cards;
1	2012	1	2	04Sep2014	09Mar2015	
2	2018	1	. 	17May2017	26Nov2017
3	2012	2	2	03Jan2014	15Jun2014	
4	2018	2	. 	26May2017	08Nov2017
5	2016	3	2	03Jan2015	16Jun2015
6	2017	3	. 	02May2017
;

proc sql;
create table want as
select *, max(startDate) - min(endDate)+Duplicates-Duplicates as delta
from have
group by id
order by id, startYear;
quit;
mkeintz
PROC Star

This program calculates differences for the entire sequence of records, but sets the difference to missing for the first record of each id:

 

data have;
infile cards truncover;
input obs startYear	ID	Duplicates	(startDate		endDate) (:date9.);
format startDate		endDate date9.;
cards;
1	2012	1	2	04Sep2014	09Mar2015	
2	2018	1	. 	17May2017	26Nov2017
3	2012	2	2	03Jan2014	15Jun2014	
4	2018	2	. 	26May2017	08Nov2017
5	2016	3	2	03Jan2015	16Jun2015
6	2017	3	. 	02May2017
;
data want;
  set have;
  by id;
  difference=startdate-lag(enddate);
  if first.id then difference=.;
run;

  

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
kfbaker0206
Fluorite | Level 6

This works also! Thank you!

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 1597 views
  • 3 likes
  • 4 in conversation