Can qtr function taking macro

[leisas]

Basically, I need to get a quarter of the year, for instance, the third quarter (number 3) of this year.

If I use data step, it works fine

data quatr;

x=%sysfunc(putn("&sysdate9"d,5.));

q=qtr(x)-1;

RUN;

result q is 3

What I really need is a macro of quarter so I can innovate in my program. I coded as

%LET QT=QTR(%sysfunc(putn("&sysdate9"d,5.)))-1;
%put qt=&qt;

result in log

qt=QTR(19289)-1

My question is if data step is the only way to use QTR function, how to create macro of qtr? Thanks!

[Haikuo · Accepted solution]

%LET QT=%eval(%sysfunc(QTR(%sysfunc(putn("&sysdate9"d,5.))))-1);

%put qt=&qt;

Haikuo

[leisas]

Got it. Thanks a lot!

[Tom]

No need to use PUTN on date literals. 

%let qt=%eval(%sysfunc(qtr("&sysdate9"d))-1);

But the %EVAL() is needed because the original requester had done it that way.  Not sure why they want to number quarters from 0 to 3 instead of from 1 to 4.

[leisas]

qtr("&sysdate9"d ) returnes the current quarter num which is 4, but I need the third quarter, so need -1.

[Tom]

If you want to find the previous quarter then you should look into using the INTNX function.

For example what will happen when the current date is in January?  Do you want to get 0 or 4 as the returned quarter number?

%let refdate=01JAN2012;

%let prev_qtr=%sysfunc(intnx(qtr,"&refdate"d,-1),qtr.);

%put &prev_qtr;

[leisas]

Tom,

Your most recent code really help me with the question I had for a quite long time. I had the problem if the date went back across "Year". Now you resolved the problem. Thank you SO much!

[leisas]

Tom,

I see your macro returns previous year, month or quarter correctly by using date function. Any way I can get by 'yyyymm' or 'mm/dd/yyyy'?

Tom wrote:

If you want to find the previous quarter then you should look into using the INTNX function.
For example what will happen when the current date is in January?  Do you want to get 0 or 4 as the returned quarter number?

%let refdate=01JAN2012;
%let prev_qtr=%sysfunc(intnx(qtr,"&refdate"d,-1),qtr.);
%put &prev_qtr;

For instance, our data provide date formatted as 'yyyymm' - '201209'  or 'mm/dd/yyyy' - '09/01/2012'. Can I get the same date formate one year backward?, say ''201110'

or '09/01/2011'? from some macros or functions?

[Linlin]

is this what you want?

69  %let refdate=09/01/2012;

270

271  %let prev_year=%sysfunc(intnx(year,%sysfunc(inputn(&refdate,mmddyy10.)),-1),year.);

272

273  %put &prev_year;

2011

[leisas]

No. I used the similar code before, only returned yyyy which in this case is 2011. I hope to get '09/01/2011' or '201109' for instance.

[Linlin]

299  %let refdate=09/01/2012;

300

301  %let prev_year=%sysfunc(intnx(year,%sysfunc(inputn(&refdate,mmddyy10.)),-1,s),mmddyy10.);

302

303  %put &prev_year;

09/01/2011

[leisas]

Thanks, Linlin.

It worked correctly!  Just a thought if I use date( ) or today( ) as cutoff date instead of specific date(09/01/2012), so no need to change date every time later on. What function or macro do you suggest to use?

[Linlin]

312  %let prev_year=%sysfunc(intnx(year,%sysfunc(today()),-1,s),mmddyy10.);

313

314  %put &prev_year;

10/25/2011

[leisas]

It worked perfectly! Thanks, Linlin!

[Tom]

%let prev_qtr=%sysfunc(intnx(qtr,"&refdate"d,-1),qtr.);

Break down the statement into its pieces so you can see how to make the changes you want.  The second argument to the %SYSFUNC() function is the format that you want to use in creating the new macro variable.  So QTR. format returns the quarter number.  You could use YEAR. to get just the year.  Or DATE9 . You could use MMDDYY10. format to get 09/01/2012.  You could use YYMMN6. to get it as 201209.

Now if you want to change how the input macro variable is formatted then you need to change how you are passing in the second parameter of the INTNX() function call. The line above is using a date literal to feed the date value into the INTNX() function.  Date literals require ddMONyy or ddMONyyyy format.  But you can use another nested %SYSFUNC() macro call to allow you to use the INPUTN() function to convert strings in other date formats into date values.  For example you could use %sysfunc(inputn(09/01/2012,mmddyy10.)).

To read 201209 as a valid date is a little harder.  Try this.

%let refdate=201209 ;

%put %sysfunc(inputn(&refdate.01,yymmdd8.));

Here are examples using three input formats: 25OCT2012, 10/25/2012 and 201210 and outputting using YEAR., QTR., DATE9., MMDDYY10., and YYMMN6. formats.

%let refdate=&sysdate9;

%put &refdate;

%put %sysfunc(intnx(qtr,"&refdate"d,-1),year.);

%put %sysfunc(intnx(qtr,"&refdate"d,-1),qtr.);

%put %sysfunc(intnx(qtr,"&refdate"d,-1),date9.);

%put %sysfunc(intnx(qtr,"&refdate"d,-1),mmddyy10.);

%put %sysfunc(intnx(qtr,"&refdate"d,-1),yymmn6.);

%let refdate=10/25/2012;

%put &refdate;

%put %sysfunc(intnx(qtr,%sysfunc(inputn(&refdate,mmddyy10.)),-1),year.);

%put %sysfunc(intnx(qtr,%sysfunc(inputn(&refdate,mmddyy10.)),-1),qtr.);

%put %sysfunc(intnx(qtr,%sysfunc(inputn(&refdate,mmddyy10.)),-1),date9.);

%put %sysfunc(intnx(qtr,%sysfunc(inputn(&refdate,mmddyy10.)),-1),mmddyy10.);

%put %sysfunc(intnx(qtr,%sysfunc(inputn(&refdate,mmddyy10.)),-1),yymmn6.);

%let refdate=201210;

%put &refdate;

%put %sysfunc(intnx(qtr,%sysfunc(inputn(&refdate.01,yymmdd8.)),-1),year.);

%put %sysfunc(intnx(qtr,%sysfunc(inputn(&refdate.01,yymmdd8.)),-1),qtr.);

%put %sysfunc(intnx(qtr,%sysfunc(inputn(&refdate.01,yymmdd8.)),-1),date9.);

%put %sysfunc(intnx(qtr,%sysfunc(inputn(&refdate.01,yymmdd8.)),-1),mmddyy10.);

%put %sysfunc(intnx(qtr,%sysfunc(inputn(&refdate.01,yymmdd8.)),-1),yymmn6.);