Help using Base SAS procedures

SQL Date Formatting

Reply
Frequent Contributor
Posts: 117

SQL Date Formatting

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;

Super User
Posts: 17,780

Re: SQL Date Formatting

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;

Frequent Contributor
Posts: 110

Re: SQL Date Formatting

proc sql;

create table data.test as

select       * from connection to odbc

(

select     ID,

             DATE AS CREATION_DATE format=date20.,

from         maintable

);

quit;

Super User
Posts: 17,780

Re: SQL Date Formatting

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

Respected Advisor
Posts: 3,887

Re: SQL Date Formatting

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;

Respected Advisor
Posts: 4,641

Re: SQL Date Formatting

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;

PG

PG
Ask a Question
Discussion stats
  • 5 replies
  • 447 views
  • 0 likes
  • 5 in conversation