topic number to DATE conversion in SAS Programming

number to DATE conversion

SASAna — Fri, 30 Dec 2016 19:25:29 GMT

Hi SAS USers,

I need some help with converting this below formatted complete_date to 'DATE' type to insert into database.

I tried

new_complete_date = input(put(datepart(complete_date),8.),YYMMDD10.) it did not worked.

new_complete_datetime = input(put(datepart(complete_date),8.),DATETIME20.)

format new_complete_date YYMMDD10. new_complete_datetime DATETIME20.;

Variable name type length format informat

Complete_date Num 8 DATETIME20. DATETIME20.

Thanks,

Ana

Re: number to DATE conversion

Reeza — Fri, 30 Dec 2016 19:36:19 GMT

Post what your data looks like.

You can't use datepart on a character variable, so break it into steps to see which functions work.

Edit: I misread your question. Use datepart() alone, the database should understand the data.

Re: number to DATE conversion

Patrick — Sat, 31 Dec 2016 00:00:29 GMT

Which SAS version? Which Database and which DB version?

According to your code your source variable "complete_date" contains a SAS DateTime value. Do you want to load the DateTime value or only the Date part?

As an example for Oracle:

DATE in Oracle contains a value which corresponds to a SAS DateTime value and the SAS/Access engine will do the conversion for you when loading into Oracle.

Because SAS doesn't have specific data types for Date and DateTime, the only way the SAS/Access engine can determine that a SAS numeric variable actually contains a Date or DateTime value is via the format applied to the variable. I've made with Oracle the experience that things don't work properly with all formats (which is a flaw) and I make always sure that I'm using Datetime20. or Date9. - you can simply use a data step Format statement or Proc Datasets Modify to change the permanent format on a variable.

Re: number to DATE conversion

SASAna — Thu, 05 Jan 2017 16:13:53 GMT

Thanks Reeza, It worked with just datetime() and inserted appropriate date time in database.