DATA Step, Macro, Functions and more

Choosing the Previous Quarter End using INTNX

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Choosing the Previous Quarter End using INTNX

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; 

Accepted Solutions
Solution
‎06-09-2017 11:31 AM
PROC Star
Posts: 556

Re: Choosing the Previous Quarter End using INTNX

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

View solution in original post


All Replies
Super User
Posts: 10,552

Re: Choosing the Previous Quarter End using INTNX

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.

Super User
Posts: 17,963

Re: Choosing the Previous Quarter End using INTNX

[ Edited ]

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;

 

 

 

Trusted Advisor
Posts: 1,631

Re: Choosing the Previous Quarter End using INTNX

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.

Contributor
Posts: 27

Re: Choosing the Previous Quarter End using INTNX

Thank you!!
Contributor
Posts: 27

Re: Choosing the Previous Quarter End using INTNX

Thanks!!
Solution
‎06-09-2017 11:31 AM
PROC Star
Posts: 556

Re: Choosing the Previous Quarter End using INTNX

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

Re: Choosing the Previous Quarter End using INTNX

Thanks! Worked!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 168 views
  • 5 likes
  • 5 in conversation