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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.