11-21-2013 01:00 AM
Currently the code below will output the results to the SAS dataset WORK.UOBS_RMTL.
The field SUBMISSION_DATE in WORK.UOBS_RMTL is in text. Is there anyway for SAS to auto pick up as date format?
I prefer not to run another data step to convert text to date using INPUT.
CONNECT to ODBC as oradb (datasrc=UOBS_PROD user=&SUser. password=&SPwd.);
CREATE TABLE WORK.UOBS_RMTL (COMPRESS=YES) AS
FROM connection to oradb
, CONVERT(varchar(10), SUBMISSION_DATE, 126) AS SUBMISSION_DATE
DISCONNECT from oradb;
11-21-2013 01:26 AM
Someone suggested like this:
create table data.test as
select ID, INPUT(CREATION_DATE,datetime20.) AS CREATION_DATE
from connection to odbc
( select ID, DATE AS CREATION_DATE
from maintable );
If I have 12 date columns, it means I have to define 12 times. Quite time-consuming.
Am looking for some lazy coding.
11-21-2013 08:33 AM
I think the only ways that SAS "guesses" at variables types is through proc import or the import wizard.
You could do the conversions in a data step using arrays and a do loop to save a little on the typing.
11-21-2013 09:19 AM
I had this problem in connecting to newer SQL server database and the solution was to get an updated version of the odbc interface. The problem there was that the database had a new date field datatype. In the old odbc interface the field was processed as a text field and in the new one it was processed as sas datetime field.
11-21-2013 09:51 AM
In the sample code you provided you are converting data type from date to character. If you just select the field you need without conversion:
you will have SUBMISSION_DATE as date if it is date in database.