date and time format

[Smitha9]

Hi I have a dataset A with this format

11JAN21:13:15:00.

12JUL02:12:30:02

I want to convert this to separate column of date and time(am and pm).

Date                          Time

01/11/2021            1:15:00pm

07/12/2002         12:30:02am

[LinusH]

Use the datepart and timepart functions respectively.

[SASJedi · Accepted solution]

Try the DATEPART and TIMEPART functions:

data want;
input DateTimeValue:datetime.;
Date=datepart(DateTimeValue);
Time=timepart(DateTimeValue);
format DateTimeValue datetime. Date mmddyy10. time timeampm.;
datalines;
11JAN21:13:15:00
12JUL02:12:30:02
;

Result:

Obs	DateTimeValue	Date	Time
1	11JAN21:13:15:00	01/11/2021	1:15:00 PM
2	12JUL02:12:30:02	07/12/2002	12:30:02 PM

[Tom]

Assuming you have a NUMERIC variable that has a count of seconds that has the DATETIME16. format attached to it so that the numbers are displayed as the strings you showed.

Then just use the DATEPART() and TIMEPART() functions to generate new variables that contain days and seconds.  You can then attach appropriate formats to those new variables to have the values displayed in the style you showed.

Let's make a sample dataset with a datetime variable.

data have;
  datetime=datetime();
  format datetime datetime19.;
run;

Now we can make a new dataset that adds the new date and time variables.

data want;
  set have;
  date=datepart(datetime);
  time=timepart(datetime);
  format date mmddyy10. time timeampm. ;
run;

Result

Obs               datetime          date       time

 1      05JUN2023:09:36:06    06/05/2023    9:36:06 AM

[irohitsharma535]

Hey, you can easily achieve this transformation using SAS or Python, depending on your preference.

In SAS:

You can use the INPUT function to read the original datetime format and then split it into date and time columns:

sas

data B; set A; format Date mmddyy10. Time timeampm.; datetime = input(OriginalColumn, datetime20.); Date = datepart(datetime); Time = timepart(datetime); run;

In Python (Pandas):

Using Python, you can achieve the same with datetime and strftime:

python

import pandas as pd data = {'OriginalColumn': ['11JAN21:13:15:00', '12JUL02:12:30:02']} df = pd.DataFrame(data) # Convert to datetime df['Datetime'] = pd.to_datetime(df['OriginalColumn'], format='%d%b%y:%H:%M:%S') # Extract Date and Time df['Date'] = df['Datetime'].dt.strftime('%m/%d/%Y') df['Time'] = df['Datetime'].dt.strftime('%I:%M:%S%p') print(df[['Date', 'Time']])

Handling date and time can sometimes cause issues with formats like leap years or AM/PM shifts. That could be tricky, especially with February's 28-29 day inconsistency. It was fixed with an additional C++ class in  Timechart Work Time.