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

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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;

 


 

View solution in original post

3 REPLIES 3
Reeza
Super User
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
Calcite | Level 5

Ah! Thank you!!

mkeintz
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1021 views
  • 0 likes
  • 3 in conversation