CODEWITHSUNDEEP
sas
Adam
Adam

Reputation: 351

SAS SQL Date formatting

Just wondering how I can alter the following query to show date in the format I want. I am using SAS to pull this data.

Existing Date format: 15MAR2011:09:05:16.000000

Format I want: 15MAR2011:09:05:16

Query I am using:

proc sql;
create table data.test as
select       * from connection to odbc
(
select     ID,
             DATE AS CREATION_DATE,

from         maintable
);
quit;

Upvotes: 1

Views: 19610

Answers (1)

BellevueBob
BellevueBob

Reputation: 9618

A format affects how SAS displays a variable value. It does not affect the actual value itself.

So, assuming the variable CREATION_DATE is a datetime value, just assign it a format of DATETIME20. to display is as you want:

proc sql; 
   create table data.test as
   select ID, CREATION_DATE format=datetime20.
   from connection to odbc 
   ( select ID, DATE AS CREATION_DATE
     from maintable );
quit;

However, some ODBC interfaces will return your date column as a character string, so you need to be sure it is showing up on the SAS side as a proper datetime value.

Upvotes: 4

Related Questions