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

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

Hi... Thanks for the info.

 

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

date.jpg

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Kurt_Bremser
Super User

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
Super User

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
Super User

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.

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
  • 5327 views
  • 2 likes
  • 4 in conversation