Date format when importing via ODBC

[Walternate]

Hi all,

I'm using ODBC to import a dataset into SAS (not sure what the original format is).

Any date variables that I import are formated as dd/mon/yyyyhh:mm:ss (ie, there's a time stamp).

When I try to reformat the dates to remove the time stamp, I get no error message but the dates are replaced with a series of asterisks.

Is there a good way to reformat the dates/help SAS recognize the variable as a date?

Thanks!

[LinusH]

Easiest is to use the datepart() function.

[cov_derek · Accepted solution]

Without seeing your data, I think you are getting a datetime value (seconds since midnight, 1960JAN01), not a date value (days since 1960JAN01), a difference of a factor of 86400). If you try to translate this value using a date format, the value will be so far into the future that the format cannot display it.

Use the datetime15. format to determine if it is importing correctly, and you should see the value as follows: 05SEP2013:10:48  In order to get the date from that, create a new variable using the DATEPART() function.

date_i_want = DATEPART(value_i_imported);

Hope this helps,

Derek

[Walternate]

I forgot to mention in my original post that the time stamps were composed entirely of zeroes.

When I tried the datetime15. format, I basically got the same dates with the 00:00 time stamp. Then when I used the datepart function and reformatted, it returned the correct dates.

Thanks!

[cov_derek]

The reason why this method works is that the values you are importing are measured in seconds, while you are wanting a measure in days. It doesn't matter that the time stamps are composed of zeroes. You can only "cut off" the time stamp in SAS with the DATEPART() function, which essentially applies the following formula: FLOOR(seconds/86400) to convert from seconds to days.

[jakarman]

You got the datetime from SAS I read so you are lucky the transformation with ODBC suceeded with no problems.

The thing you are asking is the fundamentals of working wiht datetime date en time in the SAS environment.

The best is to start wiht the concepts on that. SAS(R) 9.4 Language Reference: Concepts

the datepart function is the recalculation of an original datetime value. But you have to know and validate those types being just numbers.

[LinusH]

We could also mention the possibility to override the default SAS/ACCESS data type mapping. When reading from the source DB, you can use the DBSASTYPE data set option:

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Second Edition

And for out processing (create/alter table) DBTYPE:

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Second Edition

The a bit annoying thing about these is that you can't set a default mapping schema per data type, you must specify it for each column.