topic Re: datepart() returns zero in SAS Data Management

datepart() returns zero

tellmeaboutityo — Mon, 09 Mar 2020 20:26:33 GMT

I'm working with a datetime variable, but I want to reduce it to at least a day/month/year variable, or preferably monthyear, so that I can then select only distinct cases of the reduced format. Basically, I want to recode the dates into season/year variables for many observations. However, the datepart() function doesn't seem to be working for me.

The code I'm using is this, where raisedate is the original date variable:

data want; data have;

datetime = raisedate;
format datetime dateampm.;
datejulian = raisedate;
format datejulian julian.;
date = datepart(raisedate);
run;

Produces this:

id raisedate datetime datejulian datepart

1 21AUG2014 01JAN60:05:32:36 AM 14233 0
2 27APR2017 01JAN60:05:48:56 AM 17117 0
3 21AUG2014 01JAN60:05:32:36 AM 14233 0
4 21AUG2014 01JAN60:05:32:36 AM 14233 0
5 21AUG2014 01JAN60:05:32:36 AM 14233 0
6 21AUG2014 01JAN60:05:32:36 AM 14233 0
7 21AUG2014 01JAN60:05:32:36 AM 14233 0
8 21AUG2014 01JAN60:05:32:36 AM 14233 0
9 12MAR2019 01JAN60:06:00:20 AM 19071 0
10 26APR2019 01JAN60:06:01:05 AM 19116 0

So, for some reason I can reformat the date time, but the datepart() function isn't working.

Perhaps related to this issue, is that if I select distinct on the raisedate variable (i.e. without an id variable), I still get multiple indistinct entries, such as from above, even with the various formats broken out:

21AUG2014 01JAN60:05:32:36 AM 14233 0
21AUG2014 01JAN60:05:32:36 AM 14233 0
21AUG2014 01JAN60:05:32:36 AM 14233 0
21AUG2014 01JAN60:05:32:36 AM 14233 0
21AUG2014 01JAN60:05:32:36 AM 14233 0
21AUG2014 01JAN60:05:32:36 AM 14233 0

Clearly, they are not obviously distinct depending on format. I've ran into issues with lack of rounding hidden from user view before in SAS, but I'm not sure if that's the issue here, or how I would "round" a date to resolve it.

Any recommendations for what to do? Many thanks!

Re: datepart() returns zero

JackHamilton — Mon, 09 Mar 2020 20:41:15 GMT

Raisedate contains date values, so when you run the datepart function on it you get a datetime in the morning of January 1, 1960.

You should read some SAS Global Forum papers on date and datetime values and functions.

There are several ways to handle the problem you describe, but one way is to use the intnx function to create a new variable containing the start date value for the period you want.

For example,

first_of_month = intnx('month', raisedate, 0);

format first_of_month monyy7.;

You can also get the first day of the quarter, year, etc.

Also, I recommend against using 2-digit years.

Re: datepart() returns zero

art297 — Mon, 09 Mar 2020 21:10:22 GMT

I'm guessing that where your code reads 'data want; data have;' you actually ran 'data want; set have'.

Your raisedate variable appears to be a datetime variable that is formatted as date9.

Given your example results all of the date variables will have values of 0 as that is the value of the data 01jan1960.

In the following example, I changed the dates so that the date variable will have some variance:

data have;
  informat raisedate datetime21.;
  format raisedate date9.;
  input raisedate;
  cards;
01JAN1960:05:32:36 AM
02JAN1960:05:48:56 AM
03JAN1960:05:32:36 AM
04JAN1960:05:32:36 AM
05JAN1960:05:32:36 AM
06JAN1960:05:32:36 AM
07JAN1960:05:32:36 AM
08JAN1960:05:32:36 AM
09JAN1960:06:00:20 AM
10JAN1960:06:01:05 AM
;
run;

data want;
  set have;
  datetime = raisedate;
  format datetime dateampm.;
  datejulian = raisedate;
  format datejulian julian.;
  date = datepart(raisedate);
run;

Art, CEO, AnalystFinder.com

Re: datepart() returns zero

ballardw — Mon, 09 Mar 2020 22:01:29 GMT

I would strongly suggest NOT creating some not-quite-a-date value to indicate the start of a month or calendar quarter but instead use an actual date value as @art297 suggests. Not actual date values will have issues with graphing as the interval between Dec and Jan is extremely uneven if you use 201912 (year month) followed by 202001.

If you leave it as a date then manipulations such as intervals can still be done with the data functions and formats can create a lot of date groupings just by changing the format. Formats with 201912 are kind of stuck (though you can have that appearance for the value with YYMMn6).

Re: datepart() returns zero

Tom — Mon, 09 Mar 2020 22:32:02 GMT

Dates are stored as number of days. Datetime is stored as number of seconds. To convert from DATETIME vales to DATE values all the DATEPART() function essentially doing is taking the remainder after integer division by (24 hours)*(60 minutes)*(60 seconds).

So when you take the datepart of values like 19,956 (which is 21st of Auguest in 2014) then you get zero (which is the first day of 1960).

1220  data test;
1221    date='21AUG2014'd;
1222    datetime=date;
1223    put 'COMMA12.    ' (date datetime) (= comma12.);
1224    put 'YYMMDD10.   ' (date datetime) (= yymmdd10.);
1225    put 'DATETIME20. ' (date datetime) (= datetime20.);
1226  run;

COMMA12.    date=19,956 datetime=19,956
YYMMDD10.   date=2014-08-21 datetime=2014-08-21
DATETIME20. date=01JAN1960:05:32:36 datetime=01JAN1960:05:32:36

What are you trying to do?