Desktop productivity for business analysts and programmers

Subtracting 2 dates in large data set

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Subtracting 2 dates in large data set

As part of a large data set, 2 of my columns contain dates that I want to subtract to get the difference in number of days

 

For example (imagine 1000+ rows):

Person         Column B     Date_Start    Date_End    Column E

   A                                        1/1/17          1/9/17

   B                                        3/3/17          3/6/17

   C                                        2/7/17          2/12/17

   D                                        8/8/17          8/8/17

   E                                        9/1/17          9/30/17

 

How do I subtract Date_End from Date_Start columns to get:

 

Date_Difference

           8

           3

 

           5

           0

          29

 

I've tried proc sql where I've assigned the following, but this doesn't seem right:

     min(Date_Start) as Date_Start,
     max(Date_End) as Date_End,
     max(Date_End) - min(Date_Start) as Date_Difference,

 Also had a go with (preferred code layout):

data timing;
set timing;
by Person;
   Date_Difference = intck('Date_Difference', Date_Start, Date_End);
   put Date_Difference = ;
run;

Any help with the coding would be appreciated!


Accepted Solutions
Solution
‎07-12-2017 09:02 PM
PROC Star
Posts: 284

Re: Subtracting 2 dates in large data set

  select Date_End - Date_Start as Date_Difference,

the above code should work

View solution in original post


All Replies
Solution
‎07-12-2017 09:02 PM
PROC Star
Posts: 284

Re: Subtracting 2 dates in large data set

  select Date_End - Date_Start as Date_Difference,

the above code should work

Super User
Posts: 19,172

Re: Subtracting 2 dates in large data set

Dates can be subtracted directly to get the number of days, either in SQL or the data step. If you're going to use INTCK, the interval, which is the first parameter is 'DAY'

Super User
Posts: 5,370

Re: Subtracting 2 dates in large data set

If your dates are actually character strings rather than true SAS dates, you would need:

 

data want;

set timing;

date_difference = input(Date_End, mmddyy8.) - input(Date_Begin, mmddyy8.);

run;

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 176 views
  • 1 like
  • 4 in conversation