BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mirisage
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

9 REPLIES 9
tish
Calcite | Level 5

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
Opal | Level 21

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

frisco
Calcite | Level 5

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 128622 views
  • 17 likes
  • 8 in conversation