Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 338
Accepted Solution

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

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


Accepted Solutions
Solution
‎07-26-2012 01:25 PM
PROC Star
Posts: 7,363

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

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

View solution in original post


All Replies
Contributor
Posts: 52

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

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.

Solution
‎07-26-2012 01:25 PM
PROC Star
Posts: 7,363

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

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

New Contributor
Posts: 2

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

[ Edited ]
 
New Contributor
Posts: 2

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

worked for me! Thanks!
Contributor
Posts: 53

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

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

Contributor
Posts: 66

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

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

Super User
Posts: 9,681

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

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

Contributor
Posts: 31

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

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..

Super Contributor
Posts: 338

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 57980 views
  • 11 likes
  • 8 in conversation