topic Re: Can qtr function taking macro in SAS Programming

Can qtr function taking macro

leisas — Tue, 23 Oct 2012 19:00:54 GMT

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!

Re: Can qtr function taking macro

Haikuo — Tue, 23 Oct 2012 19:19:58 GMT

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

%put qt=&qt;

Haikuo

Re: Can qtr function taking macro

leisas — Tue, 23 Oct 2012 19:25:48 GMT

Got it. Thanks a lot!

Re: Can qtr function taking macro

Ksharp — Wed, 24 Oct 2012 04:03:12 GMT

It could be simpler.

%LET QT=%eval(%sysfunc(today(),qtr.)-1);

%put qt=&qt;

Ksharp

Re: Can qtr function taking macro

Tom — Wed, 24 Oct 2012 04:12:39 GMT

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.

Re: Can qtr function taking macro

Tom — Wed, 24 Oct 2012 04:21:11 GMT

Why is your data step mixing macro and normal SAS functions?

If you want to use the automatic macro variable SYSDATE9 in a Data step there is normally not a need to convert it first with macro programming statements.

data quatr;

q=qtr("&sysdate9"d)-1;

run;

Re: Can qtr function taking macro

leisas — Wed, 24 Oct 2012 15:09:51 GMT

Yes, it is simpler and worked. Thank you!

Re: Can qtr function taking macro

leisas — Wed, 24 Oct 2012 15:13:33 GMT

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

Re: Can qtr function taking macro

leisas — Wed, 24 Oct 2012 15:22:54 GMT

You're correct. I did the extra step just want to see what I would get so as qtr function can take and convert. Your code is absolute correct and effecient. Thanks a lot!

Re: Can qtr function taking macro

Tom — Wed, 24 Oct 2012 16:05:16 GMT

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;

Re: Can qtr function taking macro

leisas — Thu, 25 Oct 2012 13:15:02 GMT

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!

Re: Can qtr function taking macro

leisas — Thu, 25 Oct 2012 18:37:01 GMT

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?

Re: Can qtr function taking macro

Linlin — Thu, 25 Oct 2012 18:47:09 GMT

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

Re: Can qtr function taking macro

leisas — Thu, 25 Oct 2012 19:02:52 GMT

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.

Re: Can qtr function taking macro

Linlin — Thu, 25 Oct 2012 19:08:18 GMT

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

Re: Can qtr function taking macro

leisas — Thu, 25 Oct 2012 19:45:15 GMT

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?

Re: Can qtr function taking macro

Linlin — Thu, 25 Oct 2012 19:51:18 GMT

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

313

314 %put &prev_year;

10/25/2011

Re: Can qtr function taking macro

leisas — Thu, 25 Oct 2012 19:58:26 GMT

It worked perfectly! Thanks, Linlin!

Re: Can qtr function taking macro

Tom — Thu, 25 Oct 2012 20:49:04 GMT

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

Re: Can qtr function taking macro

leisas — Fri, 26 Oct 2012 14:57:07 GMT

Tom,

Thank you SO MUCH for the detailed explaination and codes. They are very helpful indeed. I coded some of in my program, it worked perfectly. Again, Thank you for looking into my post and helped me out of the woods in time.

Leigh