DATA Step, Macro, Functions and more

Difference between Two dates

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Difference between Two dates

Hi...!

 

I have data set of 10,000 entries and I want to calculate the date difference between two date column's and the date format is in DD-MM-YYY.

 

I have tried.

 

Data = AAA;

set = bb;

avg_date=start_date - end_date;

lable avg_date = "Average_Date";

run;

 

and the results was blank columns.

 


Accepted Solutions
Solution
‎03-13-2017 08:28 AM
Super User
Super User
Posts: 7,407

Re: Difference between Two dates

Please post test data in the form of a datastep.  We cannot see what you are working with.  I would suspect that start_date and end_date are character in your data, hence you wouldn't be able to subtract them.  Make sure you use SAS numeric dates and then it works fine:

data test;
  length start_date end_date $20;
  start_date="01-02-2011";
  end_date="04-05-2011";
  sdate=input(start_date,ddmmyy10.);
  edate=input(end_date,ddmmyy10.);
  avg_date=edate-sdate;
  format sdate edate date9.;
run;

Note that you would want to take start from end not the other way round.  Also note that avg_date will contain the number of days difference between the two.

View solution in original post


All Replies
Solution
‎03-13-2017 08:28 AM
Super User
Super User
Posts: 7,407

Re: Difference between Two dates

Please post test data in the form of a datastep.  We cannot see what you are working with.  I would suspect that start_date and end_date are character in your data, hence you wouldn't be able to subtract them.  Make sure you use SAS numeric dates and then it works fine:

data test;
  length start_date end_date $20;
  start_date="01-02-2011";
  end_date="04-05-2011";
  sdate=input(start_date,ddmmyy10.);
  edate=input(end_date,ddmmyy10.);
  avg_date=edate-sdate;
  format sdate edate date9.;
run;

Note that you would want to take start from end not the other way round.  Also note that avg_date will contain the number of days difference between the two.

Occasional Contributor
Posts: 12

Re: Difference between Two dates

Hi... Thanks for the info.

 

And what would be the reason for showing blanks in the output....?

date.jpg

Super User
Super User
Posts: 7,407

Re: Difference between Two dates

If your actual numeric dates (either) are missing then the result will be missing.

Super User
Posts: 6,946

Re: Difference between Two dates

These are not blanks, these are missing numeric values. Doing computations where at least one of the inputs is missing usually results in missing values.

You should see corresponding NOTEs in the log of the data step that created this dataset.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 10,508

Re: Difference between Two dates

Additonal to the conversion comments posted by others, if your data is not a valid date then the conversion (input statement) to date value would fail. If the day of the month isn't valid such as 30 February then that is an invalid date. If your process or data has some behavior such as replacing 99 or 00 for an actually missing(not originally recorded) day or month then that is not a valid date for the ddmmyy informat. If some of the values are actually mm-dd-yyyy then when input using ddmmyy some are likely to be invalid dates for ddmmyy.

 

If you have some dates that may be invalid for ddmmyy you should have some log enteries similar to this:

NOTE: Invalid argument to function INPUT at line 47 column 9.

x=10-27-27 y=. _ERROR_=1 _N_=1

 

Otherwise we kind of need to see some of your specific values that are yielding the missing values, both start and end.

 

Super User
Posts: 6,946

Re: Difference between Two dates

A difference in dates can only be calculated if the dates are stored as SAS date values (numeric, counting the days from 1960-01-01, with a date format for display). If your dates are stored in strings, you need to convert to SAS dates first.

So you need to inspect your data.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 193 views
  • 2 likes
  • 4 in conversation