SAS Date Format Required

[saikiran_nemani]

Hi All,

There is a Issue in Date format as I was unable to make it usable format.

I have tried Proc Format (User Defined) and other SAS Date Functions as well.

I want it in 0000-00-00 00:00:00 this format.

X Variable

44673.77414351851

44747.78288194445

0000-00-00 00:00:00

0000-00-00 00:00:00

[PeterClemmensen]

What does 44673.77414351851 represent?

[saikiran_nemani]

In my excel sheet, it was showing as 22-04-2022 18:34:36.

When I used Proc Import and it showing as a number.

[Kurt_Bremser]

So you run into the usual troubles with Excel crap.

Add '30dec1899'd to the integer part of the number to get the date in SAS, and multiply the fractional part by 86400 to get the time. Use the DHMS function to combine both parts into a datetime, and apply an appropriate format.

You may need to create a custom picture format for this.

[MayurJadhav]

I'm assuming this X variable actually a datetime variable but appears as sas date in numeric.

if so then here is how you can get date and time in desired formatd.

data _null_;
  format dt datetime25.;
  dt=datetime();
  dttm = put(datepart(dt), yymmddd10.)||put(datepart(dt), tod11.);
  put dttm= ;
run;

output:

dttm=2023-02-15   06:24:16

[PaigeMiller]

---

@MayurJadhav wrote:

I'm assuming this X variable actually a datetime variable but appears as sas date in numeric.

if so then here is how you can get date and time in desired formatd.

data _null_;
  format dt datetime25.;
  dt=datetime();
  dttm = put(datepart(dt), yymmddd10.)||put(datepart(dt), tod11.);
  put dttm= ;
run;

output:

dttm=2023-02-15   06:24:16

---

I would recommend doing something else, because the above solution gives you a character string for DTTM. Better would be to use the DHMS function to obtain a numeric date/time value, as already explained above by @Kurt_Bremser . Numeric date/time values can be used in arithmetic or in boolean logic easily, character strings not so much.

data want;
    value=44673.77414351851;
    sas_date_value='30DEC1899'd+floor(value); /* Integer part of 44673.77414351851 */
    sas_time_value=86400*(value-floor(value)); /* Decimal part of 44673.77414351851 */
    sas_date_time_value=dhms(sas_date_value,hour(sas_time_value),minute(sas_time_value),second(sas_time_value));
    format sas_date_value date9. sas_time_value time. sas_date_time_value datetime19.;
run;