SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Date format when importing via ODBC

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 138
Accepted Solution

Date format when importing via ODBC

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!


Accepted Solutions
Solution
‎09-05-2013 10:52 AM
Contributor
Posts: 23

Re: Date format when importing via ODBC

Posted in reply to Walternate

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

View solution in original post


All Replies
Super User
Posts: 5,441

Re: Date format when importing via ODBC

Posted in reply to Walternate

Easiest is to use the datepart() function.

Data never sleeps
Solution
‎09-05-2013 10:52 AM
Contributor
Posts: 23

Re: Date format when importing via ODBC

Posted in reply to Walternate

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

Frequent Contributor
Posts: 138

Re: Date format when importing via ODBC

Posted in reply to cov_derek

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!

Contributor
Posts: 23

Re: Date format when importing via ODBC

Posted in reply to Walternate

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.

Trusted Advisor
Posts: 3,215

Re: Date format when importing via ODBC

Posted in reply to Walternate

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.

---->-- ja karman --<-----
Super User
Posts: 5,441

Re: Date format when importing via ODBC

Posted in reply to Walternate

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.

Data never sleeps
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 1000 views
  • 6 likes
  • 4 in conversation