BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
yawen929
Fluorite | Level 6

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.0002022-04-19
11MAY2021:12:24:00.0002021-05-11T12:24
20APR2022:00:00:00.0002022-04-20
09MAR2021:10:21:00.0002021-03-09T10:21
13APR2021:08:25:00.0002021-04-13T08:25
30MAR2021:12:12:00.0002021-03-30T12:12
22FEB2024:16:25:00.0002024-02-22T16:25
12OCT2020:00:00:00.0002020-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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

 

View solution in original post

3 REPLIES 3
Patrick
Opal | Level 21

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;

Patrick_0-1710995922846.png

 

 

Tom
Super User Tom
Super User

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
Lapis Lazuli | Level 10

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

Dr. Abhijeet Safai
Certified Base and Clinical SAS Programmer
Associate Data Analyst
Actu-Real

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 2689 views
  • 4 likes
  • 4 in conversation