topic Re: Separate date from time in a character column in SAS Programming

Separate date from time in a character column

nazmul — Sat, 17 Feb 2018 20:08:00 GMT

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

10/29/2014 20:05:00

Re: Separate date from time in a character column

kiranv_ — Sat, 17 Feb 2018 20:23:27 GMT

one way to do

time = input(scan(date,2, ' '),time8.);
format time time8.;

Re: Separate date from time in a character column

Tom — Sat, 17 Feb 2018 20:29:00 GMT

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.

Re: Separate date from time in a character column

novinosrin — Sat, 17 Feb 2018 20:41:03 GMT

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;

Re: Separate date from time in a character column

nazmul — Sat, 17 Feb 2018 20:49:15 GMT

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