Converting Date and Time to Character

[yawen929]

Hello!

For reporting purposes, I am trying to convert numeric datetime records to character. I have the following:

HAVE (Numeric, datetime22.3)	WANT (Character)
19APR2022:00:00:00.000	2022-04-19
11MAY2021:12:24:00.000	2021-05-11T12:24
20APR2022:00:00:00.000	2022-04-20
09MAR2021:10:21:00.000	2021-03-09T10:21
13APR2021:08:25:00.000	2021-04-13T08:25
30MAR2021:12:12:00.000	2021-03-30T12:12
22FEB2024:16:25:00.000	2024-02-22T16:25
12OCT2020:00:00:00.000	2020-10-12

How would I get a character result for the HAVE variable? I have tried:

WANT=put(datepart(HAVE),yymmdd10.);

but this does not include the time part.

I also tried:

WANT=put(datepart(HAVE),datetime22.3);

but it only gives me 01JAN1960:xx:xx:xx.000 for each record, not the actual date.

Is it possible to do this another way? Any guidance would be appreciated. Thank you in advance!

Best,

Yawen

[Patrick]

There is a big list of ready made formats available documented here. If you're dealing with SAS DateTime values then you need to use the formats for SAS DateTime values, if you've got a SAS Date value then use the formats for SAS Date values. The datepart() function allows you to convert a SAS DateTime value to a SAS Date value so you then can use a SAS Date Format for the created SAS Date value.

...And if none of the SAS provided formats return exactly what you want then you can also create your own picture format with date or datetime directives (documented as part of Proc Format docu).

For reporting purpose: I wouldn't convert a SAS Date or DateTime value to a character but just use the format you want for the variable. Using the numerical values with a format also has the advantage that the dates will sort numerically and not alphabetically. If you use character variables then for example August would in some reports appear before June.

Here some demo code to illustrate the concept

data have;
  sas_dt_value='10jan2024'd;
  sas_dttm_value='10jan2024 10:15:45'dt;
run;

proc print data=have;
  var sas_dt_value sas_dttm_value;
  format sas_dt_value date9. sas_dttm_value datetime20.;
run;

proc print data=have;
  var sas_dt_value sas_dttm_value;
  format sas_dt_value mmddyy10. sas_dttm_value NLDATMMD.;
run;

[Tom · Accepted solution]

Since dates are in DAYS and datetimes are in SECONDS take a number of seconds and converting it to a number of days and then treating it as it if was still a number of seconds is why your second attempt is ending up in 1960. 

Today is day number 23,456 and since there are 86,000 seconds in a day that is still early in the morning of the first day.

1    data _null_;
2      today=date();
3      put today= comma10. today= date9. today=tod8. today=datetime19.;
4    run;

today=23,456 today=21MAR2024 today=06:30:56 today=01JAN1960:06:30:56
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

SAS has a format to display datetime values in that ISO 8601 style, E8601DT16.  Example:

5
6    data _null_;
7      now=datetime();
8      put now=comma20. now=datetime19. now=e8601dt19. now=e8601dt16.;
9    run;

now=2,026,602,149 now=21MAR2024:01:02:29 now=2024-03-21T01:02:29 now=2024-03-21T01:02
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

If you want to eliminate the 'T00:00' suffix you could just use TRANWRD().

data have;
  input dt :datetime. expect :$16. ;
  format dt datetime19.;
cards;
19APR2022:00:00:00.000	2022-04-19
11MAY2021:12:24:00.000	2021-05-11T12:24
20APR2022:00:00:00.000	2022-04-20
09MAR2021:10:21:00.000	2021-03-09T10:21
13APR2021:08:25:00.000	2021-04-13T08:25
30MAR2021:12:12:00.000	2021-03-30T12:12
22FEB2024:16:25:00.000	2024-02-22T16:25
12OCT2020:00:00:00.000	2020-10-12
;

data want;
  set have;
  length string $16 ;
  string=tranwrd(put(dt,e8601dt16.),'T00:00',' ');
run;

Result

Obs                     dt    expect              string

 1      19APR2022:00:00:00    2022-04-19          2022-04-19
 2      11MAY2021:12:24:00    2021-05-11T12:24    2021-05-11T12:24
 3      20APR2022:00:00:00    2022-04-20          2022-04-20
 4      09MAR2021:10:21:00    2021-03-09T10:21    2021-03-09T10:21
 5      13APR2021:08:25:00    2021-04-13T08:25    2021-04-13T08:25
 6      30MAR2021:12:12:00    2021-03-30T12:12    2021-03-30T12:12
 7      22FEB2024:16:25:00    2024-02-22T16:25    2024-02-22T16:25
 8      12OCT2020:00:00:00    2020-10-12          2020-10-12

[DrAbhijeetSafai]

---

SAS has a format to display datetime values in that ISO 8601 style, E8601DT16.  Example:

5
6    data _null_;
7      now=datetime();
8      put now=comma20. now=datetime19. now=e8601dt19. now=e8601dt16.;
9    run;

now=2,026,602,149 now=21MAR2024:01:02:29 now=2024-03-21T01:02:29 now=2024-03-21T01:02

 This was wonderful @Tom , thanks!

Thank you.

- Dr. Abhijeet Safai