topic Re: SQL Date Formatting in SAS Procedures

SQL Date Formatting

vomer — Tue, 14 Aug 2012 16:39:27 GMT

Hi there,

Just wondering how I can alter the following query to show date in the format I want.

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;

Re: SQL Date Formatting

Reeza — Tue, 14 Aug 2012 17:01:39 GMT

You need to add the format in to the date variable, you can do it in the same step or in a different step.

proc sql;

create table data.test as

select ID, creation_date format=date20. from connection to odbc

(

select ID,

DATE AS CREATION_DATE,

from maintable

);

quit;

Re: SQL Date Formatting

sassharp — Tue, 14 Aug 2012 18:46:21 GMT

proc sql;

create table data.test as

select * from connection to odbc

(

select ID,

DATE AS CREATION_DATE format=date20.,

from maintable

);

quit;

Re: SQL Date Formatting

Reeza — Tue, 14 Aug 2012 18:58:44 GMT

Will that work within the connection to ODBC, I thought that had to be native SQL in that section?

Re: SQL Date Formatting

Patrick — Wed, 15 Aug 2012 22:25:01 GMT

As Reeza hints: you need native SQL code inside the pass-through section. But you can of course do the formatting on the SAS side.

The variable DATE seems to contain datetime values so you would need to use a datetime format.

proc sql;

create table data.test as

select ID, DATE AS CREATION_DATE format=datetime21.

from connection to odbc

(

select ID,

DATE

from maintable

);

quit;

Re: SQL Date Formatting

PGStats — Thu, 16 Aug 2012 00:49:14 GMT

Or, if you don't care/want the time information in creation_date :

proc sql;
create table data.test as
select     ID
          ,datepart(IMPORTED_DATETIME) as creation_date format=date20.
from connection to ODBC
     (

          select     ID
                    ,DATE AS IMPORTED_DATETIME
          from maintable
     );
quit;