topic Re: Combine date and time columns into yyyy-mm-dd hh:mm:ss.ms in SAS Programming

Combine date and time columns into yyyy-mm-dd hh:mm:ss.ms

hhchenfx — Thu, 27 Oct 2022 11:59:05 GMT

Hi All,

My data has a date column and time column.

I would like to create a column datetime with format yyyy-mm-dd hh:mm:ss.ms and export to csv.

Clearly, my time doesn't has millisecond and 00000 is fine.

Can you please help me to get the code?

Thanks,

HHC

data have;
input date time;
informat time time11.;
format date date9. time time11.;
datalines;

20130730 4:00:00
20130130 5:15:00
run;

proc format;
picture mydt   (default=24)
low-high='%Y-%0m-%0d %0H:%0M:%0S.0000' (datatype=datetime)
;
run;

data have;
   input date time;
   informat date yymmdd10. time time11.;
   format date date9. time time11.;
datalines;
20130730 4:00:00
20130130 5:15:00
run;

data want; set have;
   dt = dhms(date,0,0,time);
   format dt mydt.;
run;

Re: Combine date and time columns into yyyy-mm-dd hh:mm:ss.ms

Kurt_Bremser — Thu, 27 Oct 2022 05:26:45 GMT

You do not have a date in your dataset. Did you miss the correct informat in your data step?

Re: Combine date and time columns into yyyy-mm-dd hh:mm:ss.ms

ballardw — Thu, 27 Oct 2022 06:25:34 GMT

See if this gets you started:

proc format;
picture mydt   (default=23)
low-high='%Y-%0m-%0d %0H:%0M:%0S.000' (datatype=datetime)
;
run;

data have;
   input date time;
   informat date yymmdd10. time time11.;
   format date date9. time time11.;
   dt = dhms(date,0,0,time);
   format dt mydt.;
datalines;
20130730 4:00:00
20130130 5:15:00
run;

What is going on: Read proc format documentation on the Picture statement. Your desired datetime appearance is kind of non-standard so you have to roll your own with proc format. The MYdt format will display any fractional seconds as .000 so if you actually have values to display good luck. The directives in the Picture statement, those things starting with % are case sensitive so read the documentation. This is one of the few places that the quotes need to be single quotes around the string of directives. If you use double quotes you will get a bunch of errors from the macro language processor. The characters other than the %, directive letter and 0 in between are all displayed literally.

Note that you have a choice between a 24 hour clock, used, and 12 hour. Also the choices shown place a 0 before some of the values when they would be less than 10, so you get 2022-08-03 for the date portion instead of 2022-8-3 and similar for the hour, minute and seconds.

There is a similar format supplied by SAS, E8601DTw.d but it places a T between the date and time portions instead of a space.

The code also shows how to read shown values as the believed date.

Re: Combine date and time columns into yyyy-mm-dd hh:mm:ss.ms

hhchenfx — Thu, 27 Oct 2022 12:01:02 GMT

Great post, @ballardw !
Thank you,
HHC