When I convert data from Excel, Access, or other database software, the date values are not the same as SAS date values, so when calculations are done, the resulting numbers are much larger than expected.

When SAS reads date values from database software such as Excel or Access, it has to assume that the values are actualy date/time values -- not only a number of days, but hours, minutes and seconds as well. The "usual" SAS date value represents the number of days after (or before) 01 January 1960. SAS datetime values, such as those resulting from date values in Excel and Access, represent the number of seconds after (or before) 01 January 1960, and these, obviously, are much larger numbers.

You can convert these values in different ways, depending on how you want to use them. You can extract the DATE portion of a DATETIME value, then use the result as you would any SAS DATE value, or you can do your calculations with the original values then compensate by dividing by the necessary day, month, or year values.

For the sake of example, we'll say that we want to know the number of years between two date points. Our original Excel or Access database contains a Date of Birth (DOB) variable and a Date of Visit (VISIT) variable, and we want to know how old each person was at the time of VISIT. Let's use ACCDATA as our data set name in the example:

ACCDATA (as it appears formatted using PROC PRINT):

  ID         DOB                  VISIT
  1   09MAY1951:00:00:00    03JUN2000:00:00:00
  2   29FEB1976:00:00:00    12APR2001:00:00:00
  3   31OCT1938:00:00:00    30SEP2001:00:00:00
  4   12AUG1925:00:00:00    28JAN2000:00:00:00
  5   01JAN1964:00:00:00    24DEC2001:00:00:00

One solution is to convert the DOB and VISIT DATETIME variables into DATE variables, and then calculate your AGE variable by dividing the result by 365.25 (the average number of days in a year, correcting for Leap Year). This can be done with the DATEPART function. The formats are optional, but helpful in viewing the output.

data agecalc1; set accdata; 
  format dob visit datetime18. 
         dobnew visitnew mmddyy8.;
  dobnew=datepart(dob);
  visitnew=datepart(visit);
  age = (visitnew - dobnew) / 365.25;
proc print;
run;

The resulting data, shown by PROC PRINT, looks like:

ID               DOB              VISIT     DOBNEW   VISITNEW     AGE

 1  09MAY51:00:00:00   03JUN00:00:00:00   05/09/51   06/03/00  49.0705
 2  29FEB76:00:00:00   12APR01:00:00:00   02/29/76   04/12/01  25.1170
 3  31OCT38:00:00:00   30SEP01:00:00:00   10/31/38   09/30/01  62.9158
 4  12AUG25:00:00:00   28JAN00:00:00:00   08/12/25   01/28/00  74.4613
 5  01JAN64:00:00:00   24DEC01:00:00:00   01/01/64   12/24/01  37.9795

Another solution is to calculate the AGE variable from the original DOB and VISIT variables (in DATETIME format), and divide by the number of seconds in a year, which is

60*60*24*365.25 = 31557600

Here's the example, and the output:

data agecalc1; set accdata;
  format dob visit datetime18.
  age = (visit - dob) / 31557600;
proc print;
run;

ID                   DOB                 VISIT      AGE

 1      09MAY51:00:00:00      03JUN00:00:00:00    49.0705
 2      29FEB76:00:00:00      12APR01:00:00:00    25.1170
 3      31OCT38:00:00:00      30SEP01:00:00:00    62.9158
 4      12AUG25:00:00:00      28JAN00:00:00:00    74.4613
 5      01JAN64:00:00:00      24DEC01:00:00:00    37.9795