BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
camfarrell25
Quartz | Level 8

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; 
1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20
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

7 REPLIES 7
ballardw
Super User

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
Super User

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
Diamond | Level 26

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.

--
Paige Miller
PeterClemmensen
Tourmaline | Level 20
data _null_;
   format end_last_q date9.;
   end_last_q = intnx('qtr', today(), -1, 'end');
   put end_last_q;
run;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 12291 views
  • 8 likes
  • 5 in conversation