DATA Step, Macro, Functions and more

Separate date from time in a character column

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 78
Accepted Solution

Separate date from time in a character column

Hi,

 

I have a character column with $20. format. This column contains date and time. I want to split the column into two columns with one columns containing only date and the other one only the time. Could anyone please help me?

 

Sample date:

Current format: $20.

 

Date

05/02/2014 04:45:57

05/01/2014 06:00:00

05/01/2014 06:00:00

10/29/2014 20:05:00


Accepted Solutions
Solution
‎02-17-2018 03:47 PM
Super User
Posts: 2,061

Re: Separate date from time in a character column

data have ;
   length dtchar $20 ;
   input dtchar $20.;
cards;
05/02/2014 04:45:57
05/01/2014 06:00:00
05/01/2014 06:00:00
10/29/2014 20:05:00
;

data want;
set have;
_temp=input(dtchar,anydtdtm21.);
date=datepart(_temp);
time=timepart(_temp);
format date date9. time time10.;
drop _:;
run;

View solution in original post


All Replies
PROC Star
Posts: 549

Re: Separate date from time in a character column

one way to do

time = input(scan(date,2, ' '),time8.);
format time time8.;
Super User
Super User
Posts: 8,279

Re: Separate date from time in a character column

[ Edited ]

You can easily use SUBSTR() to pull apart the data if it as clean as your examples.

Let's make some sample data out of your listing.

data have ;
   length dtchar $20 ;
   input dtchar $20.;
cards;
05/02/2014 04:45:57
05/01/2014 06:00:00
05/01/2014 06:00:00
10/29/2014 20:05:00
;

Now to make new character variables use SUBSTR().  If you want you can convert them into actual date and time variables and even combine back together into a datetime variable.

data want ;
  set have ;
  length datechar $10 timechar $8 ;
  datechar = substr(dtchar,1,10);
  timechar = substr(dtchar,12);
  date = input(datechar,mmddyy10.);
  time = input(timechar,time8.);
  dt = dhms(date,0,0,time);
  format date yymmdd10. time time8. dt datetime20. ;
run;

Results

Obs          dtchar            datechar     timechar          date        time                      dt

 1     05/02/2014 04:45:57    05/02/2014    04:45:57    2014-05-02     4:45:57      02MAY2014:04:45:57
 2     05/01/2014 06:00:00    05/01/2014    06:00:00    2014-05-01     6:00:00      01MAY2014:06:00:00
 3     05/01/2014 06:00:00    05/01/2014    06:00:00    2014-05-01     6:00:00      01MAY2014:06:00:00
 4     10/29/2014 20:05:00    10/29/2014    20:05:00    2014-10-29    20:05:00      29OCT2014:20:05:00

 

Note that it is not the FORMAT that matters for variable definitions.  What matters is what LENGTH it is defined to hold. The format attached doesn't matter unless you set it shorter than the length, in which case you might be confused about the variables contents when it prints truncated values.

 

Solution
‎02-17-2018 03:47 PM
Super User
Posts: 2,061

Re: Separate date from time in a character column

data have ;
   length dtchar $20 ;
   input dtchar $20.;
cards;
05/02/2014 04:45:57
05/01/2014 06:00:00
05/01/2014 06:00:00
10/29/2014 20:05:00
;

data want;
set have;
_temp=input(dtchar,anydtdtm21.);
date=datepart(_temp);
time=timepart(_temp);
format date date9. time time10.;
drop _:;
run;
Frequent Contributor
Posts: 78

Re: Separate date from time in a character column

Posted in reply to novinosrin
Thank you everyone for your help. All of you codes work. I selected the most convenient one as the code used date and time functions
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 356 views
  • 1 like
  • 4 in conversation