topic Re: Choosing the Previous Quarter End using INTNX in SAS Programming

Choosing the Previous Quarter End using INTNX

camfarrell25 — Fri, 09 Jun 2017 14:46:18 GMT

Hello,

I'm currently trying to set up a process that given the date chosen in the prompt (&ReportDate), it gives me the previous quarter last day.

For example, the prompt allows me to choose either Quarter End (March 31, June 30, September 30, December 30) and then the DateInfLag variables should correspond to the last day of the previous quarter (December 31, March 31, June 30 and September 30, respectively).

Although, the problem is that in the case of June 30 reports, the intnx function brings back "March 30" instead of "March 31".

Any ideas??

proc sql;
select distinct 
/*Reporting Period - One quarter lag*/
compress(tranwrd(put(intnx('qtr',"&ReportDate"d,-1,'same'),yymmdd10.),"-","")),
compress(tranwrd(put(intnx('year',intnx('qtr',"&ReportDate"d,-1,'same'),-1,'same'),yymmdd10.),"-","")),
compress(tranwrd(put(intnx('year',intnx('qtr',"&ReportDate"d,-1,'same'),-2,'same'),yymmdd10.),"-","")),
compress(tranwrd(put(intnx('year',intnx('qtr',"&ReportDate"d,-1,'same'),-3,'same'),yymmdd10.),"-","")),
compress(tranwrd(put(intnx('year',intnx('qtr',"&ReportDate"d,-1,'same'),-4,'same'),yymmdd10.),"-","")),
/*Reporting Year*/
compress(put(year("&ReportDate"d),4.),"-",""), 
substr(compress(tranwrd(put(intnx('year',"&ReportDate"d,-1,'same'),yymmdd10.),"-","")),1,4),
substr(compress(tranwrd(put(intnx('year',"&ReportDate"d,-2,'same'),yymmdd10.),"-","")),1,4),
substr(compress(tranwrd(put(intnx('year',"&ReportDate"d,-3,'same'),yymmdd10.),"-","")),1,4),
substr(compress(tranwrd(put(intnx('year',"&ReportDate"d,-4,'same'),yymmdd10.),"-","")),1,4)
into: DateInflag1, : DateInflag2 , : DateInflag3, : DateInflag4, : DateInflag5, : Year1, :Year2, :Year3, :Year4,:Year5
from _PRODSAVAIL;
quit;

Re: Choosing the Previous Quarter End using INTNX

ballardw — Fri, 09 Jun 2017 15:08:28 GMT

SAME says to return the same day. Try 'E' for end of period.

You can also get rid of a lot of code manipulation if you use the YYMMDDN. format.

And if this is goes into a data set strongly recommend keeping dates as date values not text.

Re: Choosing the Previous Quarter End using INTNX

Reeza — Fri, 09 Jun 2017 15:13:31 GMT

Your INTNX functions are using SAME as the 4 parameter.

Use END to align the dates to the END of the quarter.

Since you're passing JUNE 30th as a report date, it will give you the 30th, the same, of whatever month.

EDIT: I honestly don't know that I've ever seen someone do their dates in SQL. A data _null_ step would probably be easier to read/maintain in my opinion. For example, your source dataset has absolutely no reason to be in that data step but you're forced to use that in SQL. There is no such requirement in a data step, and you can use loops for the reptitive calculations.

data _null_;
 
do i=1 to 4;
    dateinFlag = ...... ;

    Year = year(dateinFlag);

   call symputx("dateinFlag"||i, dateInFlag);
   call symputx('Year'||i, year);

end;
run;

Re: Choosing the Previous Quarter End using INTNX

PaigeMiller — Fri, 09 Jun 2017 15:10:53 GMT

If you use the alignment option of INTNX to be 'END' (or 'e' for short), you should get March 31. That's what I get.

Re: Choosing the Previous Quarter End using INTNX

PeterClemmensen — Fri, 09 Jun 2017 15:15:41 GMT

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

Re: Choosing the Previous Quarter End using INTNX

camfarrell25 — Fri, 09 Jun 2017 15:31:50 GMT

Thank you!!

Re: Choosing the Previous Quarter End using INTNX

camfarrell25 — Fri, 09 Jun 2017 15:32:12 GMT

Thanks! Worked!

Re: Choosing the Previous Quarter End using INTNX

camfarrell25 — Fri, 09 Jun 2017 15:32:19 GMT

Thanks!!