date formats for datdif

Reply
New Contributor
Posts: 2

date formats for datdif

I have 3 different date formats I am trying to convert to the same format in order to calculate a difference in days between certain dates.

here is an example of the date format for each column

BRTHDY_DT - 10/27/1986

LAST_D_DT - 11/28/1999 00:00:00

LAST_DT - 11JUL09:00:00:00

LAST_CLOSED_DT - 11JUL10:00:00:00

BTW these dates could also have blanks in their columns not sure if that matters

data main_inactfile;

  set inactfile;

  Brthdy_DT_D = input(BRTHDY_DT, mmddyy10.);

  D_DATE= input(LAST_D_DT, mmddyy10.);

  LAST_DATE = input (LAST_DT, mmddyy10.);

  LAST_CLOSED_DATE = input(LAST_CLOSED_DT, mmddyy10.);

  format Brthdy_DT_D mmddyy10.;

  format D_DATE mmddyy10.;

  format LAST_DATE mmddyy10.;

  format LAST_CLOSED_DATE  mmddyy10.;

run;

When i ran this code it says it changes all BUT LAST_CLAIM_DATE and LAST_CLOSED_DATE is results in just a "." 

proc contents data=main_inact2;

run;

the proc contents shows that it was converted

How do I convert the dates so I can determine the difference in days?

Thank  you!e

Super User
Posts: 19,105

Re: date formats for datdif

Three of your variables are datetime variables not dates.

Try using anydtdtm. format instead or using specific datetime formats in conjunction with the datepart formula which will extract the date portion of the date time variable.

D_DATE= datepart(input(LAST_D_DT, anydtdtm.));

format Brthdy_DT_D D_DATE LAST_DATE LAST_CLOSED_DATE mmddyy10.;

New Contributor
Posts: 2

Re: date formats for datdif

Hi Reeza,

It seemed to have worked but the new column LAST_CLOSED_DATE and LAST_DATE still has a "." instead of the date. The original format of columns  according to SAS are Num 8 DATETIME18 . Does that make a difference?

Super User
Posts: 5,362

Re: date formats for datdif

For any existing variables that are NUM, DATETIME18., you can simply code:

new_variable = datepart(existing_variable);

If the existing variables are character, that's a totally different story.

Good luck.

Occasional Contributor
Posts: 15

Re: date formats for datdif

If you only care about the dates,

data main_inactfile;

    BRTHDY_DT = '10/27/1986';
    LAST_D_DT = '11/28/1999 00:00:00';
    LAST_DT = '11JUL09:00:00:00';
    LAST_CLOSED_DT = '11JUL10:00:00:00';

    Brthdy_DT_D = input(BRTHDY_DT, mmddyy10.);
    D_DATE= input(substr(LAST_D_DT,1,10), mmddyy10.);
    LAST_DATE = input (substr(LAST_DT,1,7), date7.);
    LAST_CLOSED_DATE = input(substr(LAST_CLOSED_DT,1,7), date7.);
run;

proc print width=min data=main_inactfile;
run;

data main_inactfile;
    set main_inactfile;

    format Brthdy_DT_D D_DATE LAST_DATE LAST_CLOSED_DATE mmddyy10.;
run;

proc print width=min data=main_inactfile;
run;

---------------output---------------

SAS dates are the number of days from the reference date 1/1/1960.

The informats convert the character to SAS dates, but the formatting has nothing to do with the actual value contained in the variable.

The formatting only affects the way the variable is displayed.

Here you see that the character dates were converted to SAS dates (the number of days since 1/1/1960) and you can use these to find the differences between them.

But, because they are not formatted, they are not easily understandable as dates.

                                                                                           LAST_
                                                                     Brthdy_        LAST_ CLOSED_
Obs BRTHDY_DT       LAST_D_DT          LAST_DT       LAST_CLOSED_DT    DT_D  D_DATE  DATE   DATE

1  10/27/1986 11/28/1999 00:00:00 11JUL09:00:00:00 11JUL10:00:00:00   9796   14576 18089  18454

So, we format them so that when we print them we can easily recognize them as dates.

Obs    BRTHDY_DT          LAST_D_DT             LAST_DT          LAST_CLOSED_DT

1     10/27/1986    11/28/1999 00:00:00    11JUL09:00:00:00    11JUL10:00:00:00

                                                   LAST_
        Brthdy_                                   CLOSED_
Obs       DT_D         D_DATE      LAST_DATE        DATE

1     10/27/1986    11/28/1999    07/11/2009    07/11/2010

Super User
Posts: 5,362

Re: date formats for datdif

First, note that you have to pay attention to messages in the log.  There should have been messages about invalid data for some of your statements.

I have to assume that your original variables are character, not numeric.  If that is the case,  you did the right thing for BRTHDY_DT and LAST_D_DT.  However, the other two variables have a different way of representing the day, and you have to change your statements accordingly.  This might do the trick:

  LAST_DATE = input (LAST_DT, date7.);

  LAST_CLOSED_DATE = input(LAST_CLOSED_DT, date7.);

There is one more issue, however.  You have data coming in with two-digit years.  Are you sure that 09 is always 2009, and 10 is always 2010?  SAS will have to make a decision for two-digit years.  You can look at how SAS will make that decision by running PROC OPTIONS and looking at the YEARCUTOFF option.  That determines how SAS assigns the century to two-digit years.

Good luck.

Ask a Question
Discussion stats
  • 5 replies
  • 278 views
  • 0 likes
  • 4 in conversation