Display last 12 quarters in SAS VA

[qiaojinggg]

Hi, I'm trying to display only the last 12 quarters of data in a bar chart form in SAS VA. I came up with a SQL expression using INTNX function that returns me the quarters of DTE_CANCEL in YYQ format. However, I'm not sure how to filter to display only the most recent 12 quarters in Report Designer.  

I'm quite new to SAS VA so any help/idea will be greatly appreciated. Thank you 🙂

INTNX('Qtr', BP13_CANCEL_APPLICATION.DTE_CANCEL, 0,'b')

[AndrewHowell]

INTNX('Qtr', BP13_CANCEL_APPLICATION.DTE_CANCEL, 0,'b')

This takes you to the beginning ('b') of the DTE_CANCEL variable's current (0) time period ('qtr').

To get to the beginning of 12 quarters prior to the DTE_CANCEL:

INTNX('Qtr', BP13_CANCEL_APPLICATION.DTE_CANCEL, -12,'b')

[qiaojinggg]

Hi thanks for your answer, however it didnt give me the last 12 quarters, instead it generated some random quarters... 

How can I filter to just display data from last 5 years or last 12 quarters?

[qiaojinggg]

@AndrewHowell Hi thanks for your answer. There was a change of requirement and I'm trying to chart the last 5 years & last 8 quarters & last 12 months now. 

I tried this SQL expression, which outputs me 2011 (last 5 years). However, it is in DATE format, may I know how to use this to filter out last 5 years inside Report Designer? 

INTNX('year', BP13_CANCEL_APPLICATION.DTE_CANCEL, -5,'b')

Can i do the same for last 8 qtr & last 12 months?

Thank you very much! 🙂

[AndrewHowell]

Ok, just to be specific:

• INTNX will not return a year such as 2011.
• INTNX returns a SAS date value - a count of days since 1/1/1960. For example, today (21/06/2016) is 20626
• You can optionally (and typically) apply a format to date values.

data _null_;
       	today=Today();
       	Start_Of_Five_Years_Ago=INTNX('YEAR', Today, -5, 'B');
       	Start_Of_Eight_Qtrs_Ago=INTNX('QTR', Today, -8, 'B');
       	Start_Of_Twelve_Months_Ago=INTNX('MONTH', Today, -12, 'B');
       	* format _ALL_ date9.;
       	put _ALL_;
run;

Yields:

• today=20626
• Start_Of_Five_Years_Ago=18628
• Start_Of_Eight_Qtrs_Ago=19814
• Start_Of_Twelve_Months_Ago=20240

If you uncomment the format statement, then the values remain the same, but are displayed formatted:

• today=21JUN2016
• Start_Of_Five_Years_Ago=01JAN2011
• Start_Of_Eight_Qtrs_Ago=01APR2014
• Start_Of_Twelve_Months_Ago=01JUN2015

"B" will return the beginning of any period, "E" the end, "M" the middle, and "S" the same as the date value passed to the INTNX() function.

[qiaojinggg]

@AndrewHowell May i know where to put that code? Do I put create a new column and put it under the SQL expression?

I managed to somehow filter and chart out last 5 yr/8qtr/12mth, by using INTNX function eg 

INTNX('year', today(), -5, 'b')

which outputs me 2011 in SAS DATE/YEAR format. After that I added this advanced filter in the respective bar chart in Report Designer.

'DTE_CANCEL'n >= 'last 5 years'n

May I know if this will do the trick? I will need to generate report every period (year, qtr. mth).  Will the last 5 years be updated accordingly every period (eg 2012 - 2017; 2013-2018...)?

Thank you very much for your help 🙂