How to change the datetime format to date9. in a SAS data set imported from MS Excel?

[Mirisage]

Hi Community,

I have the attached Excel data set.

I imported it as a SAS data set using “File”, “Import Data” menu of SAS.

Two variables (post_date and effective_date) in the imported SAS dataset are in datetime format but I need to change the format into date9. format.

What I have done?

In the import process SAS generated a code. I have taken it from the log. Then I tweaked (highlighted in yellow below), and re-ran.

DATA WORK.post_this1;

    LENGTH

        NUMBER             8

        CODE             $ 3

        NUMBER_2           8

        CURRENCY           8

        AT_NUMBER          8

        ATT_NAME         $ 3

        DUCT_CODE          8

        R_CODE             8

        POST_DATE          8

        EFFECTIVE_DATE     8

        TXN                8

        TYPE             $ 28

        BASIC              8

        OFF                8 ;

    FORMAT

        NUMBER           BEST12.

        CODE             $CHAR3.

        NUMBER_2         BEST12.

        CURRENCY         BEST12.

        AT_NUMBER        BEST12.

        ATT_NAME         $CHAR3.

        DUCT_CODE        BEST12.

        R_CODE           BEST12.

        POST_DATE        DATE9. /*originally this was DATETIME18.*/

        EFFECTIVE_DATE   DATE9. /*originally this was DATETIME18.*/

        TXN              BEST12.

        TYPE             $CHAR28.

        BASIC            COMMA12.

        OFF              COMMA12. ;

    INFORMAT

        NUMBER           BEST12.

        CODE             $CHAR3.

        NUMBER_2         BEST12.

        CURRENCY         BEST12.

        AT_NUMBER        BEST12.

        ATT_NAME         $CHAR3.

        DUCT_CODE        BEST12.

        R_CODE           BEST12.

        POST_DATE        DATETIME18.

        EFFECTIVE_DATE   DATETIME18.

        TXN              BEST12.

        TYPE             $CHAR28.

        BASIC            COMMA12.

        OFF              COMMA12. ;

    INFILE 'T:\SASWork\_TD12012\#LN00044'

        LRECL=84

        ENCODING="WLATIN1"

        TERMSTR=CRLF

        DLM='7F'x

        MISSOVER

        DSD ;

    INPUT

        NUMBER           : BEST32.

        CODE             : $CHAR3.

        NUMBER_2         : BEST32.

        CURRENCY         : BEST32.

        AT_NUMBER        : BEST32.

        ATT_NAME         : $CHAR3.

        DUCT_CODE        : BEST32.

        R_CODE           : BEST32.

        POST_DATE        : BEST32.

        EFFECTIVE_DATE   : BEST32.

        TXN              : BEST32.

        TYPE             : $CHAR28.

        BASIC            : BEST32.

        OFF              : BEST32. ;

RUN;

Question

Formats of post_date and effective_date are not changed to DATE9.

Could someone help me?

Thanks

Mirisage

[tish]

If all you want is the date, associate the ANYDTDTEw. informat with the two datetime input fields. Double check the field widths and set the informat width accordingly.

Otherwise I'm a bit confused because you have two informats specified for each input field. Offhand, I don't know which one is taking effect. I'd limit myself to either the informat statement or the informats associated with the variables on the input statement for clarity.

I hope this helps.

[art297 · Accepted solution]

Mirisage,

You could have imported the datetime variables as dates by using the usedate=yes option on the proc import.

However, you can always convert datetime fields to dates by using the datepart function in a datastep.  e.g.,

data test;

  set test;

  format post_date effective_date date9.;

  post_date=datepart(post_date);

  effective_date=datepart(effective_date);

run;

HTH,

Art

[usser]

worked for me! Thanks!

[frisco]

Please see my NESUG '03 paper (has it been that long?) called "Changing that Pesky datetime to a date" - available at Lex Jansen's most excellent SAS website lexjansen.com

[Dorota_Jarosz]

Mirisage,

A datetime variable stores time in SECONDS since midnight of January 1, 1960. A date variable stores number of DAYS since January 1, 1960.
If you want the date rather than datetime, you must divide the numerical value by (24*60*60) or '24:00't.  If there exists a time part of the datetime variable, you can extract the date by using the datepart() function or by using the integer part of the division result: int(post_date/(24*60*60)).

If you want the time part then you need the remainder from the division by 24 hours.

I hope this helps,

Dorota

[Ksharp]

SAS has already built-in a format DTDATEw. to do this.

data x;
input POST_DATE   :   DATETIME18. ;
_POST_DATE=POST_DATE;
format POST_DATE DATETIME18. _POST_DATE   DTDATE9.;
cards;
12jul2012:10:21:21
;
run;

Ksharp

[ErikT]

Two options come to mind:

1. In the code generated by the file import routine add following two statements after the input statement:

post_date = datepart(post_date);

effective_date = datepart(effective_date);

The datepart function will isolate the date from the date-time combination.

2. change the format in the spreadsheet to "general", rather than date-time. This makes it a basic numeric variable. To convert that into a SAS date you have to take the integer part and subtract 21916 to get the SAS date.

The fraction in the Excel date is the time, e.g. 0.5 is 12 noon. See my paper at SUGI-29 (paper 68-29) for more info on these conversions..

[Mirisage]

Hi Tish, Art, Frisco, Dorota, Ksharp, ErikT,

Thank very much for every one of you for these extremly useful contributions.

These leverages an encyclopedic knowledge on the subject.

Warm regards

Mirisage