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;
... View more