Create Dynamic Quarterly Report

[acrosb]

Hi,

At the end of each quarter I want to run my SAS program to create a report of all the sales from the prior quarter. In my dataset ("mydata") I have a timestamp for when the sale occurred ("Sale_Timestamp"), formatted as DATETIME20. If I ran this program today (July 6, 2021), I'd want to get a report for all sales that occurred in Q2 (April 1, 2021 - Jun 30, 2021). I can create the macro variables alright, but when I try to add it to my 'where' statement I get a syntax error (but really I think the error has to do with the format of the macro variables). Can anyone see an error in my code below?

Here is my code:

*prior quarter;
%let qt=%sysfunc(intnx(qtr,%sysfunc(today()),-1),qtr.);

*first day of prior quarter;
%let start_dt=%sysfunc(intnx(qtr,%sysfunc(today()),-1,b),date9.);

*last day of prior quarter;
%let end_dt=%sysfunc(intnx(qtr,%sysfunc(today()),-1,e),date9.);

*Test macro variables;
%put &=qt;*2;
%put &=start_dt;*01APR2021;
%put &=end_dt;*30JUN2021;

data test_Q&qt;
set mydata;
where &start_dt <= datepart(Sale_Timestamp) <= &end_dt;
run;

[Reeza · Accepted solution]

where 01APR2021 <= datepart(Sale_Timestamp) <= 30JUN2021;

That's what your code resolves to and you're correct it's not valid SAS code.

Either change your code to have the quotes and d:

where "&start_dt"d <= datepart(Sale_Timestamp) <= "&end_dt"d;

OR you can remove the format from the macro variable:

*first day of prior quarter;
%let start_dt=%sysfunc(intnx(qtr,%sysfunc(today()),-1,b));

*last day of prior quarter;
%let end_dt=%sysfunc(intnx(qtr,%sysfunc(today()),-1,e));

---

@acrosb wrote:

Hi,

At the end of each quarter I want to run my SAS program to create a report of all the sales from the prior quarter. In my dataset ("mydata") I have a timestamp for when the sale occurred ("Sale_Timestamp"), formatted as DATETIME20. If I ran this program today (July 6, 2021), I'd want to get a report for all sales that occurred in Q2 (April 1, 2021 - Jun 30, 2021). I can create the macro variables alright, but when I try to add it to my 'where' statement I get a syntax error (but really I think the error has to do with the format of the macro variables). Can anyone see an error in my code below?

Here is my code:

*prior quarter;
%let qt=%sysfunc(intnx(qtr,%sysfunc(today()),-1),qtr.);

*first day of prior quarter;
%let start_dt=%sysfunc(intnx(qtr,%sysfunc(today()),-1,b),date9.);

*last day of prior quarter;
%let end_dt=%sysfunc(intnx(qtr,%sysfunc(today()),-1,e),date9.);

*Test macro variables;
%put &=qt;*2;
%put &=start_dt;*01APR2021;
%put &=end_dt;*30JUN2021;

data test_Q&qt;
set mydata;
where &start_dt <= datepart(Sale_Timestamp) <= &end_dt;
run;

---

[acrosb]

Ah! Thank you!!

[mkeintz]

Create two macro variables that look like:

CURR_QTR_DATE_TIME:01JUL21:00:00:00
and
LAST_QTR_DATE_TIME=01APR21:00:00:00

via these statements:

%let last_qtr_date_time=%sysfunc(dhms(%sysfunc(intnx(qtr,%sysfunc(today()),-1,beg)),0,0,0),datetime18.)  ;
%let curr_qtr_date_time=%sysfunc(dhms(%sysfunc(intnx(qtr,%sysfunc(today()),00,beg)),0,0,0),datetime18.)  ;
%put _user_;

Then filter based on

   "&last_qtr_date_time"dt <= my_date_time < "&curr_qtr_date_time"dt ;

Edited note:  Oops!  You're using dates, not datetimes.  Then use 

%let last_qtr_date=%sysfunc(intnx(qtr,%sysfunc(today()),-1,beg),date9.);
%let curr_qtr_date=%sysfunc(intnx(qtr,%sysfunc(today()),00,beg),date9.);

and the filter

  "&last_qtr_date"d <= my_date < "&curr_qtr_date"d;