BookmarkSubscribeRSS Feed
vomer
Obsidian | Level 7

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;

5 REPLIES 5
Reeza
Super User

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;

sassharp
Calcite | Level 5

proc sql;

create table data.test as

select       * from connection to odbc

(

select     ID,

             DATE AS CREATION_DATE format=date20.,

from         maintable

);

quit;

Reeza
Super User

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

Patrick
Opal | Level 21

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;

PGStats
Opal | Level 21

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

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
  • 5 replies
  • 1117 views
  • 0 likes
  • 5 in conversation