topic Re: Display last 12 quarters in SAS VA in SAS Visual Analytics

Display last 12 quarters in SAS VA

qiaojinggg — Mon, 20 Jun 2016 06:02:06 GMT

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')

Re: Display last 12 quarters in SAS VA

AndrewHowell — Mon, 20 Jun 2016 06:14:38 GMT

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')

Re: Display last 12 quarters in SAS VA

qiaojinggg — Mon, 20 Jun 2016 08:08:43 GMT

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?

Re: Display last 12 quarters in SAS VA

qiaojinggg — Tue, 21 Jun 2016 01:24:39 GMT

@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! 🙂

Re: Display last 12 quarters in SAS VA

AndrewHowell — Tue, 21 Jun 2016 02:15:36 GMT

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.

Re: Display last 12 quarters in SAS VA

qiaojinggg — Tue, 21 Jun 2016 05:38:20 GMT

@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 🙂