Choosing the Previous Quarter End using INTNX

[camfarrell25]

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; 

[ballardw]

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.

[Reeza]

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;

[PaigeMiller]

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.

[camfarrell25]

Thank you!!

[camfarrell25]

Thanks!!

[PeterClemmensen · Accepted solution]

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

[camfarrell25]

Thanks! Worked!