DATA Step, Macro, Functions and more

Converting numeric date and time to SAS datetime22.3

Reply
Contributor
Posts: 29

Converting numeric date and time to SAS datetime22.3

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.

CreateDateCreateTimeCreateDateTime
2014080614574906AUG2014:14:57:49.000
2014080516432205AUG2014:16:43:22.000
201408018563101AUG2014:08:56:31.000
201408018501901AUG2014:08:50:19.000
2014080413342204AUG2014: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.

Super User
Super User
Posts: 7,392

Re: Converting numeric date and time to SAS datetime22.3

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;

New Contributor
Posts: 2

Re: Converting numeric date and time to SAS datetime22.3

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.

Super User
Super User
Posts: 7,392

Re: Converting numeric date and time to SAS datetime22.3

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.

Contributor
Posts: 29

Re: Converting numeric date and time to SAS datetime22.3

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.

Contributor
Posts: 29

Re: Converting numeric date and time to SAS datetime22.3

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;

Super User
Super User
Posts: 7,392

Re: Converting numeric date and time to SAS datetime22.3

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.

Ask a Question
Discussion stats
  • 6 replies
  • 773 views
  • 5 likes
  • 3 in conversation