02-17-2016 02:43 PM
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.
02-17-2016 03:39 PM - edited 02-17-2016 03:49 PM
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.)
02-17-2016 03:57 PM
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;
02-17-2016 07:18 PM
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.
Need further help from the community? Please ask a new question.