Selecting the last day of previous quarter, based

问题:

I am trying to find a way to select the date of the last day of the previous quarter, based on the date of today. I have a calendar dimension at my dispoasal that looks like the following observations:

Example of data from the dimension table calendar

The following SAS code has been used to have the data like in the previous image:

data test_last_day_qtr;
    set PRDDMT.TD_ENT_CALENDAR;
    TODAY=today();
    QTR_TODAY=qtr(TODAY);
    SAS_DATE_VALUE=datepart(DT_DATE);
    if QTR_TODAY=1;
run;

Based on the fact that today is 2016/02/25, I should have to have the obeservation of 31DEC2015:00:00:00 as output.

A first idea that I had was to keep only the last observation of each quarter by doing:

data test_last_day_qtr;
    set PRDDMT.TD_ENT_CALENDAR;
    by ID_YEAR ID_QRT ID_MONTH;
    if Last.ID_MONTH;
run;

But this does not give me the result I was hoping for:

Subset of last observation within every quarter

Even if this did gave me the right result, I do not really have an idea on how to progress. Someone else who has an idea?

回答1:

Very simple if you use the intnx function, which is used to move a date forwards or backwards by a given number of periods. Include the optional end keyword to return the last day of the quarter (the default is the beginning).

data _null_;
a = intnx('quarter',today(),-1,'end');
format a date9.;
put a;
run;