sorry it is probably a very simple question, but I can't seem to find an answer to it.
Say, we want to create a table that contains 4 quarters back from the previous month:
%macro asd;
%let today = %sysfunc(today());
%let quarter_count_back = 4;
%let first_quarter = %sysfunc(intnx(month,&today.,-1));
proc sql;
create table quarters
(
Quarters num informat = date9. format = date9.
);
insert into quarters
%do i = 0 %to -&quarter_count_back.+1 %by -1;
values(%sysfunc(intnx(quarter,&first_quarter.,&i.)))
%end;
;
quit;
run;
%mend asd;
%asd;
run;
This code works just fine and creates a table, which starts from APR2016 and goes back in time by quarter. However, if I change the number in the 'first_quarter' line for -2, -3 etc... the code always starts from JAN2016 which just doesn't make any sense to me! For example:
%let first_quarter = %sysfunc(intnx(month,&today.,-2));
It seems logical that if I put this line in the code the table should start from MAR2016 and go back by quarter, but it does not, it starts from JAN2016.
Any ideas on what I am doing wrong here?
Thanks!
The default alignment for the INTNX function is the beginning of the interval. If you want it to go back 3 months, that's different than quarters. You can adjust these by looking at the fourth parameter of the INTNX function which controls the alignment. Options are:
If you want three months, try the MONTH.3 interval instead of quarter.