DATA Step, Macro, Functions and more

number to DATE conversion

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

number to DATE conversion

 

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


Accepted Solutions
Solution
‎01-05-2017 11:10 AM
Super User
Posts: 17,784

Re: number to DATE conversion

[ Edited ]

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. 

View solution in original post


All Replies
Solution
‎01-05-2017 11:10 AM
Super User
Posts: 17,784

Re: number to DATE conversion

[ Edited ]

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. 

Frequent Contributor
Posts: 84

Re: number to DATE conversion

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

 

 

 

Respected Advisor
Posts: 3,887

Re: number to DATE conversion

[ Edited ]

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. 

 

 

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 212 views
  • 0 likes
  • 3 in conversation