Difference between Two dates

[Anil9]

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.

[RW9 · Accepted solution]

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.

[Anil9]

Hi... Thanks for the info.

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

[RW9]

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

[Kurt_Bremser]

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.

[ballardw]

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.

[Kurt_Bremser]

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.