topic Re: SAS Date Format Required in SAS Enterprise Guide

SAS Date Format Required

saikiran_nemani — Wed, 15 Feb 2023 09:28:17 GMT

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

Re: SAS Date Format Required

PeterClemmensen — Wed, 15 Feb 2023 09:33:38 GMT

What does 44673.77414351851 represent?

Re: SAS Date Format Required

saikiran_nemani — Wed, 15 Feb 2023 09:38:35 GMT

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.

Re: SAS Date Format Required

Kurt_Bremser — Wed, 15 Feb 2023 10:17:46 GMT

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.

Re: SAS Date Format Required

MayurJadhav — Wed, 15 Feb 2023 10:47:44 GMT

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

Re: SAS Date Format Required

PaigeMiller — Wed, 15 Feb 2023 11:51:11 GMT

@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;