1

as title says I have an excel file i'm importing into SAS that has 2016/06/15 as the date format. I need to convert it to datetime so i can upload it to SQL.

If I upload 2016/06/15 into SQL (datetime) it gives me some random Jan 1960 date. So I'm guessing SQL requires the time in it too.

What I need is the code to format 2016/06/15 into datetime in SAS programming.

Thank you!

mitoKon
  • 51
  • 3
  • 9

1 Answers1

3

You can use the function dhms() to convert a date to datetime. Example:

data _null_;
  my_date = date();
  my_datetime = dhms(my_date, 0, 0, 0);
  put my_date date9.
      my_datetime datetime22.;
run;

Output:

02AUG2016    02AUG2016:00:00:00

If you are using ODBC passthrough to insert it into SQL, see this answer (https://stackoverflow.com/a/24044451/214994) for tips on how to do that.

Community
  • 1
  • 1
Robert Penridge
  • 8,424
  • 2
  • 34
  • 55
  • Hello, thanks for reply. If I replace date() with "06/15/2016" which is what the SAS data set looks like when i import the excel "2016/06/15". My output is still: 01JAN1960 01JAN1960:00:00:51 – mitoKon Aug 02 '16 at 14:32
  • @mitoKon There are a couple of ways to create date values in SAS. The easiest are the `mdy(6,15,2016)` function, or defining a date literal `"06/15/2016"d`. Note the `d` after the closing quote on the date literal. – Robert Penridge Aug 02 '16 at 14:36
  • Sorry I'm really bad with formatting in SAS.. I have input the my_date = "06/05/2016"d; and i get error : ERROR: Invalid date/time/datetime constant "06/05/2016"d. ERROR 77-185: Invalid number conversion on "06/05/2016"d. – mitoKon Aug 02 '16 at 14:38
  • Oh sorry - my mistake... the format is actually `"15JUN2016"d`. Switching between languages I always forget that part... – Robert Penridge Aug 02 '16 at 14:52
  • Thanks I solved it. I just used the my_datetime = dhms(my_date, 0, 0, 0); line and put my column as my_date. thanks for your help! – mitoKon Aug 02 '16 at 14:53