topic How to convert character to date in PROC SQL in SAS Programming

How to convert character to date in PROC SQL

PhatRam33 — Wed, 17 Feb 2016 19:43:24 GMT

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.

Re: How to convert character to date in PROC SQL

PGStats — Wed, 17 Feb 2016 20:08:28 GMT

Try

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

Re: How to convert character to date in PROC SQL

PhatRam33 — Wed, 17 Feb 2016 20:49:35 GMT

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.)

Re: How to convert character to date in PROC SQL

PGStats — Wed, 17 Feb 2016 20:57:34 GMT

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;

where datepart(input(createtime, anydtdtm.)) between '01JAN2016'd and '17FEB2016'd;

Re: How to convert character to date in PROC SQL

PhatRam33 — Wed, 17 Feb 2016 21:02:51 GMT

Many thanks! Worked like a charm!

Re: How to convert character to date in PROC SQL

ballardw — Thu, 18 Feb 2016 00:18:41 GMT

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.