subtract the values in a dataset

Accepted Solution Solved
Reply
Highlighted
Occasional Contributor
Posts: 7
Accepted Solution

subtract the values in a dataset

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

Accepted Solutions
Solution
Thursday
Respected Advisor
Posts: 2,812

Re: subtract the values in a dataset

[ Edited ]
Posted in reply to kfbaker0206

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


All Replies
PROC Star
Posts: 1,569

Re: subtract the values in a dataset

Posted in reply to kfbaker0206

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

Occasional Contributor
Posts: 7

Re: subtract the values in a dataset

Posted in reply to novinosrin

No they are 2,3,4,5

Solution
Thursday
Respected Advisor
Posts: 2,812

Re: subtract the values in a dataset

[ Edited ]
Posted in reply to kfbaker0206

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
PROC Star
Posts: 1,569

Re: subtract the values in a dataset

Posted in reply to kfbaker0206

Well then, I would also go with the same assumption Smiley Happy

 

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;
Trusted Advisor
Posts: 1,309

Re: subtract the values in a dataset

[ Edited ]
Posted in reply to kfbaker0206

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;

  

Occasional Contributor
Posts: 7

Re: subtract the values in a dataset

This works also! Thank you!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 95 views
  • 3 likes
  • 4 in conversation