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

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

Mirisage — Thu, 26 Jul 2012 16:55:30 GMT

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

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

tish — Thu, 26 Jul 2012 17:17:48 GMT

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.

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

art297 — Thu, 26 Jul 2012 17:25:12 GMT

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

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

frisco — Thu, 26 Jul 2012 19:14:59 GMT

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

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

Dorota_Jarosz — Thu, 26 Jul 2012 21:21:22 GMT

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

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

Ksharp — Fri, 27 Jul 2012 03:01:26 GMT

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

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

ErikT — Fri, 27 Jul 2012 07:45:56 GMT

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

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

Mirisage — Fri, 27 Jul 2012 13:50:59 GMT

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

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

usser — Thu, 15 Sep 2016 13:53:32 GMT

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

usser — Thu, 15 Sep 2016 13:53:55 GMT

worked for me! Thanks!