Converting numeric date and time to SAS datetime22.3

[GenDemo]

Hi

I am having a lot of difficulty with converting a numerical date and time to a SAS datetime format.

In my data, I have two columns that represent the date and time numerically. I would like to convert and combine these into a new variable with a datetime22.3 format. below is an example of what my data looks like. The first two columns are the date and time, respectively (in numerical format). The third column (datetime22.3 format) is what I would like to create in a data step so that I can do date manipulations on it.

CreateDate	CreateTime	CreateDateTime
20140806	145749	06AUG2014:14:57:49.000
20140805	164322	05AUG2014:16:43:22.000
20140801	85631	01AUG2014:08:56:31.000
20140801	85019	01AUG2014:08:50:19.000
20140804	133422	04AUG2014:13:34:22.000

I know that one way to do this is to convert the date and time to string and put the "-" & ":" in, by means of a substr and concatenate functions. However, I want to know if there is a quick way do convert this directly in, say 2 or 3 steps.

I would appreciate the help.

[RW9]

Hi,

Personally, I think the biggest issue here is having the date and time as numeric, mostly the time variable.  The reason being is that time actually doesn't realy exist in this term, SAS stores the value as number of seconds from a certain point, in a numeric, whereas your numeric indicates the time.  Thus somehow you will need to interpret the numeric into the component parts and let SAS calculate seconds from to get a proper time value.

data have;

  createdate=20140806; createtime=145749; output;

  createdate=20140805; createtime=164322; output;

  createdate=20140801; createtime=85631; output;

run;

data want;

  set have;

  inter=put(createtime,z6.);  /* In this instance I keep this part separate for clarity. */

  creationdatetime=dhms(input(put(createdate,8.),yymmdd8.),input(substr(inter,1,2),best.),input(substr(inter,3,2),best.),

                        input(substr(inter,5,2),best.));

  format creationdatetime datetime22.3;

run;

[Abenamor]

Hi,

I need to convert the format date "dd-mm-yyyy" to format "dd-mm-yyyy hh:mm:ss"

which format should I use in sas campaign studio.

I would appreciate the help.

[RW9]

You shouldn't jump anothers question.  As for your issue, I don't know campaign studio at all, however in base SAS you would do:

new_datetime_variable=dhms(date_variable,0,0,0);

Then format as you want from the list of formats available or create a user defined one:

Dates, Times, and Intervals : SAS Date, Time, and Datetime Values

As you only have a date I have assumed 0 hours, 0 mins, 0 seconds for the time part which is required in a datetime format.

[GenDemo]

Yes please don't jump other people questions.

The thread becomes very difficult to read and the thread usually tends to go completely off topic and the question never gets answered - as happens in many other threads.

[GenDemo]

Thank you very much.

Your solution is not exactly what I was hoping for, but it is much shorter than what I have. I appreciate your help.

Just for interest, what I did:

data want2 (drop= time_str time_str2);

set have;

/*Application Datetime*/

format createdate2 date9. createtime2 time8. creationdatetime datetime22.3;

createdate2 = input(put(createdate,12.),b8601da.);

time_str= compress(input(put(createtime,12.),$12.));

if length(time_str) = 5 then do;

     time_str2 = compress("0"||substr(time_str,1,1) ||":"|| substr(time_str,2,2) ||":"|| substr(time_str,4,2));

     createtime2 = input(put(time_str2,$8.),time8.);

   end;

  else do;

     time_str2 = compress(substr(time_str,1,2) ||":"|| substr(time_str,3,2) ||":"|| substr(time_str,5,2));

     createtime2 = input(put(time_str2,$8.),time8.);

   end;

creationdatetime = input(compress(put(createdate2, date9.)||":"||put(createtime2, time.)),datetime22.3);

run;

[RW9]

I would just re-iterate, its best to keep data in SAS under SAS structure.  E.g. put times into time formats.  You can then change the display format of the underlying number, and perform other calculations quite easily.  Just makes working with it so much easier.