topic Re: rounding up dates with rounding unit in New SAS User

rounding up dates with rounding unit

elolvido — Mon, 15 Jun 2020 14:18:23 GMT

Hello,

I'm fairly new to SAS coming from R and am adapting someone else's previous code for my project. In a large dataset with dates, we must round the date to the nearest quarter, which was previously done as

data xyz;
set xyz;
date_rounded=round(date,90);
format date_rounded DATE11.;
run;

This works great, and I want to do the exact same thing, but only allowing upwards rounding (so if they are even a couple days into quarter 2, it will be rounded to quarter 3's date). I don't see an obvious way to do this since the CEIL function doesn't allow for a second argument specifying the rounding unit. However, this dataset is massive and covers over 15 years, so rather than macgyvering a clumsy solution using my limited SAS, I am hoping there is a simple/efficient way to do this.

Please let me know if you have any suggestions!

Re: rounding up dates with rounding unit

Kurt_Bremser — Mon, 15 Jun 2020 14:15:32 GMT

This is done with the INTNX() function:

date_rounded = intnx('quarter',date,0,'e');

which gives you the correct calendar end date for the quarter.

Re: rounding up dates with rounding unit

ballardw — Mon, 15 Jun 2020 14:20:25 GMT

The question is what result you actually want as you don't say.

The proper function would likely be the INTNX function.

Result= intnx('quarter', datevariable,0,'B') would provide the first day (begining) of the calendar quarter the date occurs in.

Result= intnx('quarter',datevaraible,0,'E') would provide the last day (end) of the calendar quarter the date occurs in.

Result = intnx('quarter',datevaraible, 1,'B') would provide the first day of the next calendar quarter. I think this might be the one you want but provided the others just in case.

If your variable is actually a datetime you would use DTQUARTER instead. It is amazing how many people do not differentiate between "date" and "datetime" values.

Reason to use INTNX is that it will account for possibly issues with leap days.

And the companion function INTCK returns number of intervals between to dates, time or datetime values.

Re: rounding up dates with rounding unit

elolvido — Mon, 15 Jun 2020 14:23:18 GMT

Thanks very much for the prompt reply and an effective solution!

Re: rounding up dates with rounding unit

elolvido — Mon, 15 Jun 2020 14:24:20 GMT

Ah yes, it is the end date I am rounding up to. Variable is a date not a datetime. Thanks very much for the solution and breakdown!