BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PhatRam33
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

Try

 

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

PG

View solution in original post

5 REPLIES 5
PGStats
Opal | Level 21

Try

 

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

PG
PhatRam33
Fluorite | Level 6

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

 

 

PGStats
Opal | Level 21

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
PhatRam33
Fluorite | Level 6

Many thanks! Worked like a charm!

ballardw
Super User

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.

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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