How to convert character to date in PROC SQL

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

How to convert character to date in PROC SQL

Is there a way to convert a field stored as a character/string and convert it into a date type field?

 

The database I'm pulling from has all date fields stored as character data types.

 

Example of how field is stored:

01/07/2016 17:44:32  Need to convert to date type to display as 07JAN2016.

 

Here was my first atttempt

 

,input(substr(createtime,1,2) || substr(createtime,4,2) || substr(createtime,7,4), 10.) format=Date9. as TEST_DT

 

My end result was 30JAN4895 which is not correct =(.

 

Any help would be greatly appreciated.


Accepted Solutions
Solution
‎02-17-2016 04:03 PM
Respected Advisor
Posts: 4,821

Re: How to convert character to date in PROC SQL

Try

 

input(createtime, anydtdtm.) as test_dt format=dtdate9.

PG

View solution in original post


All Replies
Solution
‎02-17-2016 04:03 PM
Respected Advisor
Posts: 4,821

Re: How to convert character to date in PROC SQL

Try

 

input(createtime, anydtdtm.) as test_dt format=dtdate9.

PG
Contributor
Posts: 23

Re: How to convert character to date in PROC SQL

[ Edited ]

Thank you very much this worked perfectly! I have one follow-up question: If I were to pull for say a specific date range instead of creating another proc sql, would I need to do a similar conversion in my where clause?

 

If so, what would the syntax look like?

 

 

My syntax is probably totally off.. I was thinking something along these lines in my where clause:

input(createtime, anydtdtm.) between ('01JAN2016', dtdate9.) and ('31JAN2016', dtdate9.)

 

 

Respected Advisor
Posts: 4,821

Re: How to convert character to date in PROC SQL

A where clause to select for a date range would look like

 

where input(createtime, anydtdtm.) between '01JAN2016:00:00:00'dt and '17FEB2016:23:59:59'dt; 

or

where datepart(input(createtime, anydtdtm.)) between '01JAN2016'd and '17FEB2016'd;
PG
Contributor
Posts: 23

Re: How to convert character to date in PROC SQL

Many thanks! Worked like a charm!

Super User
Posts: 11,134

Re: How to convert character to date in PROC SQL

It helps to understand how SAS stores dates and datetime values. Dates are numbers of days since 1 Jan 1960. Datetimes are numbers of seconds so confusion between the data you have and how you treat it can get entertaining. The values are simply numerics so date values may be abused in many ways.

 

So what you did was 1) create a string value of 1072016 then 2) input it as a number which when Formatted with DATE9 yielded the results you showed.

 

 

 

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 7824 views
  • 3 likes
  • 3 in conversation