Converting SAS date to datetime format

[abaker_ca]

Hello,

I have a data set imported from Excel where I had to convert a date-time variable to a SAS date because the values coming in were a float that wasn't the correct numeric representation of the date.

I used a solution to go from that float to the correct SAS date which I found on this user group and it worked. However, I don't seem to be able to format the SAS date in datetime. format.  I'm only able to format it as a date.

I need to retain the time portion of the observation also to check on the order of events in my data set to make sure a control is run prior to a test sample.  I know I could perform this operation just using the SAS date variable but I also wanted a variable that displayed the date and time (or just time) to make sure my code is working. 

I've tried to use a variety of put and input statements to go from a SAS date to a datetime. format but I always end up with 01JAN60:06:24:45 as the return value when I try to use those formatting options. Does anyone know what might be happening and how to fix it?

Here's an example where x = a SAS date and xchar returns the date correctly:

data _null_;
x = 23084.51389;
xchar = put(x, date.);
put _all_;
run;

Here's an example where the datetime. format returns the 01JAN60.... value.

data _null_;
x = 23084.51389;
xchar = put(x, datetime.);
put _all_;
run;

Thank you for your response.

[PaigeMiller]

Here's an example where x = a SAS date and xchar returns the date correctly:

data _null_;
x = 23084.51389;
xchar = put(x, date.);
put _all_;
run;

First, you do NOT want dates as character strings. These will not sort properly, and you can't do arithmetic or logical operations on character string dates. Leave the date as numeric, and assign a format to it, like this:

data have;
    x = 23084.51389;
    format x date9.;
run;

Here's an example where the datetime. format returns the 01JAN60.... value.

data _null_;
x = 23084.51389;
xchar = put(x, datetime.);
put _all_;
run;

You can't use a datetime format on a date value. And as stated above, you want to keep the values as numeric so they will sort properly, rather than character strings which will not sort properly and on which you can't do arithmetic or logical operations. Datetimes are the number of seconds since midnight on 01JAN1960, so 23084 seconds after midnight is still 01JAN1960, it is a date value. You don't say what result is desirable here, so maybe you want 23084 to be the date and .51389 to be the time during the day. Is that what you want? If so

data have;
    x = 23084.51389;
    date=floor(x);
    time=24*60*60*(x-date);
    format date date9. time hhmmss8.;
run;   

I leave it as a homework assignment for you to figure out why I multiply by 24*60*60

[abaker_ca]

This is a great solution. Unfortunately, my SAS 9.4 does not recognize the format you suggested -  hhmmss8.

I'm still looking into why. But are there other formats that allow for mathematical operations on dates?

[ballardw]

Repeat: The FORMAT of a variable has no affect on functions to manipulate values.

Formats only apply to display.

Operations depend on the function.

Try the TIME8. format where you used HHMMSS.

[SASKiwi]

The correct format for displaying time as HH:MM:SS is TIMEw.d. Always check the SAS online doc to confirm.

Have you considered fixing the Excel data import itself rather than bandaiding it afterwards?

[Tom]

SAS will import DATE and DATETIME values properly from well formatted Excel spreadsheets.

But if your spreadsheet has a mix of numeric (both SAS and Excel store such values as numbers) and character cells then it will be forced to create a character variable.  Otherwise there is no way to store the character strings.

In that situation you need to convert the resulting CHARACTER value back into a number and adjust for the difference in where they start numbering the days.

So if you have a variable named STRING in a dataset named HAVE that has such character strings then you can convert them to date and or datetime values like this:

data want;
  set have;
   datetime = dhms(input(STRING,32.)+'30DEC1899'd,0,0,0);
   date = datepart(datetime);
   format date date9.  datetime datetime19. ;
run;