topic Re: How are dates stored in SAS? in New SAS User

How are dates stored in SAS?

jffeudo86 — Tue, 24 Sep 2019 20:01:12 GMT

I am reading about SAS dates. So SAS dates are stored as numbers, that I understand. My initial thoughts also is that those stored values include time. So if I have a date '31DEC1996'd, I was expecting a stored value of '31DEC1996 00:00:00'dt, time 00:00:00 being implied. It seems that is not the case. It seems it only stores the date itself '31DEC1996'd.

When I try to show the whole datetime value, it shows 01JAN1960 <time>. So is SAS storing 2 values now, one for date and one for time, the time is the correct time of the present day with the date being 01JAN1960?

I am just trying to understand.

Below is my code:

%let mdate = '31DEC1996'd;
%let yymm = %sysfunc(putn(%sysevalf(&mdate), datetime.));

%put &=yymm;

The output is: YYMM=01JAN60:03:45:14

I used mdate to show datetime, so why is it 01JAN1960?

Thank you!

Re: How are dates stored in SAS?

PaigeMiller — Tue, 24 Sep 2019 20:15:31 GMT

Dates are stored as the number of days since 1/1/1960

Date/time values (such as 31DEC1996 23:00:00) are stored as the number of seconds since midnight on 1/1/1960.

Time values are stored as the number of seconds since midnight.

Do not confuse date values with date/time values with time values, these are three distinct things. You can't use a datetime format with date values; you can't use a date format with date/time values; and so on.

Below is my code:

%let mdate = '31DEC1996'd;
%let yymm = %sysfunc(putn(%sysevalf(&mdate), datetime.));

%put &=yymm;

Try this:

%let mdate = 31DEC1996;
%let yymm = %sysfunc(putn("&mdate"d,date.));
%put &=yymm;

Although this is pointless, except as a learning exercise, you could just use &MDATE and not &YYMM.

Similarly if you want a datetime, use a datetime.

%let mdate = 31DEC1996:00:00:00;
%let datetime = %sysfunc(putn("&mdate"dt,datetime.));
%put &=datetime;

Again, pointless, except as a learning exercise.

Lastly, if you want to convert a date to a datetime, use the proper function.

%let mdate=31DEC1996;
%let mdatetime=%sysfunc(dhms("&mdate"d,0,0,0));
%put %sysfunc(putn(&mdatetime,datetime.));

All of this might be easier done in a data step, then you don't need %SYSFUNC().

Re: How are dates stored in SAS?

Tom — Tue, 24 Sep 2019 20:27:30 GMT

SAS stores DATE values as the number of days since 1960 and TIME values as the number of seconds since midnight.

But it looks like you are interested in storing what SAS calls DATETIME values. Other languages might use the term TIMESTAMP such a value. SAS stores DATETIME values as the number of seconds since 1960.

You can use the DATEPART() function to get a date value from a DATETIME value. You can use the TIMEPART() function to get the time of day for that date from the datetime value. Or you could just use arithmetic and divide by the number of seconds in a day. The integer value result is the date and the remainder is the time of day. Note you can use '24:00't to represent the number of seconds in a day.

You can use the DHMS() function to generate a datetime value from Days,Hours,Minutes, and Seconds. Note that the last three are not limited to values between 0 and 24 or 0 and 60. So to create a datetime value from separate date and time values just use DHMS(date,0,0,time).

Note that today's date, '24SEP2019'd, is day number 21,816. Which when treated as a number of seconds is around 6 AM.

Not sure why you are playing with macro code. Much easier to see how SAS works using normal SAS code instead.

2209  data _null_;
2210   now=today();
2211   put 'Raw Number -> ' now= now=comma12. ;
2212   put 'As Date    -> ' now=date9. now=yymmdd10. now=mmddyy10. ;
2213   put 'As Time    -> ' now=time. ;
2214   put 'As Datetime-> ' now=datetime20.;
2215  run;

Raw Number -> now=21816 now=21,816
As Date    -> now=24SEP2019 now=2019-09-24 now=09/24/2019
As Time    -> now=6:03:36
As Datetime-> now=01JAN1960:06:03:36

Since you seem to just want to generate a string value (to the macro processor eveyrthing is character data). You can just tack the zeros on to the end of your string.

%let yymm=%sysfunc(putn(&mdate,date9)):00:00:00 ;

Re: How are dates stored in SAS?

jffeudo86 — Tue, 24 Sep 2019 20:38:58 GMT

I got it now through further reading. I've been used to dealing with dates with implied time 00:00:00.

"THE PROBLEM
Dates stored on databases and even EXCEL spreadsheets are
typically stored as datetime values. It is quite often the case that
minutes and seconds are not relevant or useful as they are often
stored as a default value of midnight. (Something to do with glass
slippers and carriages, no doubt.) Like SAS date values, EXCEL
date values are stored as numbers. However, SAS dates are the
number of days elapsed since Jan 1, 1960, and Excel dates are
the number of days starting at Jan 1, 1900. For example, Day 1 in
SAS is Jan 2, 1960, Day 0 is Jan1, 1960. Day 1 in Excel is Jan 1,
19001"

So there is also a time value in SAS. I don't see yet why would i use/store only time it it's not tied to a date.

Re: How are dates stored in SAS?

jffeudo86 — Tue, 24 Sep 2019 20:44:04 GMT

I'm using macro because that's where I would likely use it. Pass a date to a macro and perform transformation and use it.

Re: How are dates stored in SAS?

jffeudo86 — Tue, 24 Sep 2019 20:46:57 GMT

Thank you! It is merely as a learning exercise. I'm using macro since I would most likely use it, pass a date to a macro and use that date inside the macro.

Re: How are dates stored in SAS?

Reeza — Tue, 24 Sep 2019 20:55:21 GMT

FYI - Here's a great, but longer and in depth, reference for dates and times in SAS
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/ta-p/424354

Re: How are dates stored in SAS?

Tom — Tue, 24 Sep 2019 20:57:19 GMT

@jffeudo86 wrote:

I got it now through further reading. I've been used to dealing with dates with implied time 00:00:00.

"THE PROBLEM
Dates stored on databases and even EXCEL spreadsheets are
typically stored as datetime values. It is quite often the case that
minutes and seconds are not relevant or useful as they are often
stored as a default value of midnight. (Something to do with glass
slippers and carriages, no doubt.) Like SAS date values, EXCEL
date values are stored as numbers. However, SAS dates are the
number of days elapsed since Jan 1, 1960, and Excel dates are
the number of days starting at Jan 1, 1900. For example, Day 1 in
SAS is Jan 2, 1960, Day 0 is Jan1, 1960. Day 1 in Excel is Jan 1,
19001"

So there is also a time value in SAS. I don't see yet why would i use/store only time it it's not tied to a date.

Also note that Excel has other issues with the way it stores dates. It thinks 1900 was a leap year for example. Also since it stores time of the day as a fraction of day getting time of day right can be difficult because of the limitations of storing decimal fractions in binary floating point representation.

Re: How are dates stored in SAS?

Kurt_Bremser — Tue, 24 Sep 2019 21:01:28 GMT

@jffeudo86 wrote:

I don't see yet why would i use/store only time it it's not tied to a date.

Imagine wanting to know the number of accidents happening between 9am and 10am. Datetimes only complicate that. With tools like MDDB's you go even further, you dissect timestamps into years/months/days/hours/minutes/seconds, so you can build a dimensional hierarchy.

Re: How are dates stored in SAS?

PaigeMiller — Tue, 24 Sep 2019 21:30:10 GMT

@jffeudo86 wrote:

I'm using macro because that's where I would likely use it. Pass a date to a macro and perform transformation and use it.

you can do the calculations in a data step, and in that same data step, create a macro variable.