BookmarkSubscribeRSS Feed
qiaojinggg
Fluorite | Level 6

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

 

5 REPLIES 5
AndrewHowell
Moderator
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
Fluorite | Level 6

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
Fluorite | Level 6

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

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
Fluorite | Level 6

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 2001 views
  • 0 likes
  • 2 in conversation