DATA Step, Macro, Functions and more

Can qtr function taking macro

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

Can qtr function taking macro

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!


Accepted Solutions
Solution
‎10-23-2012 03:19 PM
Respected Advisor
Posts: 3,156

Re: Can qtr function taking macro

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

%put qt=&qt;

Haikuo

View solution in original post


All Replies
Solution
‎10-23-2012 03:19 PM
Respected Advisor
Posts: 3,156

Re: Can qtr function taking macro

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

%put qt=&qt;

Haikuo

Contributor
Posts: 24

Re: Can qtr function taking macro

Got it. Thanks a lot!

Super User
Super User
Posts: 7,080

Re: Can qtr function taking macro

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.

Contributor
Posts: 24

Re: Can qtr function taking macro

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

Super User
Super User
Posts: 7,080

Re: Can qtr function taking macro

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;

Contributor
Posts: 24

Re: Can qtr function taking macro

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!

Contributor
Posts: 24

Re: Can qtr function taking macro

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?


Super Contributor
Posts: 1,636

Re: Can qtr function taking macro

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

Contributor
Posts: 24

Re: Can qtr function taking macro

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.

Super Contributor
Posts: 1,636

Re: Can qtr function taking macro

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

Contributor
Posts: 24

Re: Can qtr function taking macro

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?

Super Contributor
Posts: 1,636

Re: Can qtr function taking macro

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

313

314  %put &prev_year;

10/25/2011

Contributor
Posts: 24

Re: Can qtr function taking macro

It worked perfectly! Thanks, Linlin!

Super User
Super User
Posts: 7,080

Re: Can qtr function taking macro

%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.);

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 19 replies
  • 660 views
  • 10 likes
  • 5 in conversation