BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Walternate
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
cov_derek
Fluorite | Level 6

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

6 REPLIES 6
LinusH
Tourmaline | Level 20

Easiest is to use the datepart() function.

Data never sleeps
cov_derek
Fluorite | Level 6

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
Obsidian | Level 7

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
Fluorite | Level 6

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
Barite | Level 11

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 --<-----
LinusH
Tourmaline | Level 20

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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