BookmarkSubscribeRSS Feed
GenDemo
Quartz | Level 8

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.

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 6667 views
  • 5 likes
  • 3 in conversation