The first macro below I got from SAS website. I want to eliminate the %let start=, %let end=, and %let dif=, statements and just have the INTCK calculation done in the "%do i = 0 %to" line. If I make substitutions one at a time, the code works fine up until I attempt to substitute "%let start =" piece. If you try to run the code below, the first 3 will work but the last one will not.
Please help me understand why the last code will not work. Thanks in advance.
* THIS IS THE ORIGINAL FILE FROM THE SUPPORT.SAS WEBSITE;
%macro date_loop(start,end);
%let start=%sysfunc(inputn(&start,anydtdte9.));
%let end=%sysfunc(inputn(&end,anydtdte9.));
%let dif=%sysfunc(intck(month,&start,&end));
%do i=0 %to &dif;
%let date=%sysfunc(intnx(month,&start,&i,b),date9.);
%put &date;
%end;
%mend date_loop;
%date_loop(01jul2015,01feb2016);
* THIS DOES THE &dif CALCULATION IN THE DO LOOP;
%macro date_loop(start,end);
%let start=%sysfunc(inputn(&start,anydtdte9.));
%let end=%sysfunc(inputn(&end,anydtdte9.));
%do i=0 %to %sysfunc(intck(month,&start,&end));
%let date=%sysfunc(intnx(month,&start,&i,b),date9.);
%put &date;
%end;
%mend date_loop;
* THIS SUBSTITUTES THE "%let end=" LINE INTO THE 3RD ARGUMENT OF THE INTCK FUNCTION;
%macro date_loop(start,end);
%let start=%sysfunc(inputn(&start,anydtdte9.));
%do i=0 %to %sysfunc(intck(month,&start,%sysfunc(inputn(&end,anydtdte9.))));
%let date=%sysfunc(intnx(month,&start,&i,b),date9.);
%put &date;
%end;
%mend date_loop;
* THIS SUBSTITUTES THE "%let start=" LINE INTO THE 2RD ARGUMENT OF THE INTCK FUNCTION;
%macro date_loop(start,end);
%do i=0 %to %sysfunc(intck(month,%sysfunc(inputn(&start,anydtdte9.)),%sysfunc(inputn(&end,anydtdte9.))));
%let date=%sysfunc(intnx(month,&start,&i,b),date9.);
%put &date;
%end;
%mend date_loop;
But - but - in your intnx function in the %let within the %do loop - you're passing in 01jul2015, not 20270. You have to convert it somewhere!
I have never, ever, made this mistake mistake. Oh, no, no, no, no, no…
BTW, you can convert dates like that on the fly with %sysevalf, funnily enough.
%let date=%sysfunc(intnx(month, %sysevalf("&start"d) ,&i,b),date9.);
or even simply
%let date=%sysfunc(intnx(month, "&start"d ,&i,b),date9.);
I haven'ttest it but it seems to me that you should not enter %sysfunc within %sysfunc.
for example - instead line
%do i=0 %to %sysfunc(intck(month,&start,%sysfunc(inputn(&end,anydtdte9.))));
it should be:
%do i=0 %to %sysfunc(intck(month,&start,inputn(&end,anydtdte9.)));
But - but - in your intnx function in the %let within the %do loop - you're passing in 01jul2015, not 20270. You have to convert it somewhere!
I have never, ever, made this mistake mistake. Oh, no, no, no, no, no…
BTW, you can convert dates like that on the fly with %sysevalf, funnily enough.
%let date=%sysfunc(intnx(month, %sysevalf("&start"d) ,&i,b),date9.);
or even simply
%let date=%sysfunc(intnx(month, "&start"d ,&i,b),date9.);
I thought it was erroring out in the do loop. you are correct, that was a dumb error, glad you never made a similar mistake.
Your last macro will work if written like @LaurieF suggested:
%macro date_loop(start,end);
%do i=0 %to %sysfunc(intck(month,%sysevalf("&start"d),%sysevalf("&end"d)));
%let date=%sysfunc(intnx(month,%sysevalf("&start"d),&i,b),date9.);
%put &date;
%end;
%mend date_loop;
%date_loop(01jul2015,01feb2016)
HTH,
Art, CEO, AnalystFinder.com
Simplify, simplify, simplify:
%macro date_loop(start,end);
%do i = 0 %to %sysfunc(intck(month, "&start"d, "&end"d));
%let date = %sysfunc(intnx(month,"&start"d, &i, b), date9.);
%put &date;
%end;
%mend date_loop;
%date_loop(01jul2015, 01feb2016);
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.