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;
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;
proc sql;
create table data.test as
select * from connection to odbc
(
select ID,
DATE AS CREATION_DATE format=date20.,
from maintable
);
quit;
Will that work within the connection to ODBC, I thought that had to be native SQL in that section?
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.