topic Issue with intnx function within a macro in SAS Programming

Issue with intnx function within a macro

ItsAProccess — Mon, 13 Apr 2020 22:05:47 GMT

Hi all,

I'm having some issues getting the following code to execute as intended:

%macro ck (start=, end=);
%let start=%sysfunc(inputn(&start.,YYMMN6.));
%let end=%sysfunc(inputn(&end.,YYMMN6.));
%let dif=%sysfunc(intck(month,&start.,&end.));
%do i=0 %to &dif.;
%let date=%sysfunc(intnx(month,&start.,&i.,b),YYMMN6.);
%put &date.;
%let date2=%sysfunc(intnx(month,&date.,1));
%put &date2.;
%end;
%mend ck;

%ck(start=201701,end=201802);

The macro variable date is executing the way I'd like it to, looping through 201701 to 201802, but date2 is not. I am trying to have date2 be one month later than date. My log shows the following:

Why does SAS think that 201701 + 1 month is 201705? As the loop progresses the values for date2 get further out of expected range.

I'd appreciate any feedback and help, thank you.

Re: Issue with intnx function within a macro

PaigeMiller — Mon, 13 Apr 2020 22:17:02 GMT

Important rule: do NOT format macro variables for use in arithmetic or logical operations.

%let date=%sysfunc(intnx(month,&start.,&i.,b));

Re: Issue with intnx function within a macro

Tom — Mon, 13 Apr 2020 22:56:56 GMT

It is because it is the first day of the next month.

201,701 is the 28th of March and 201,705 is the first of April.

Try formatting those numbers as dates and you will see that.

1692  %macro ck (start=, end=);
1693  %let start=%sysfunc(inputn(&start.,YYMMN6.));
1694  %let end=%sysfunc(inputn(&end.,YYMMN6.));
1695  %let dif=%sysfunc(intck(month,&start.,&end.));
1696  %do i=0 %to &dif.;
1697  %let date=%sysfunc(intnx(month,&start.,&i.,b),YYMMN6.);
1698  %let date2=%sysfunc(intnx(month,&date.,1));
1699  %put &=date %sysfunc(putn(&date,date9)) &=date2 %sysfunc(putn(&date2,date9));
1700  %end;
1701  %mend ck;
1702
1703  %ck(start=201701,end=201702);
DATE=201701 28MAR2512 DATE2=201705 01APR2512
DATE=201702 29MAR2512 DATE2=201705 01APR2512

I assume you wanted to do this instead?

%macro ck (start=, end=);
%let start=%sysfunc(inputn(&start.,YYMMN6.));
%let end=%sysfunc(inputn(&end.,YYMMN6.));
%let dif=%sysfunc(intck(month,&start.,&end.));
%do i=0 %to &dif.;
%let date=%sysfunc(intnx(month,&start.,&i.,b),YYMMN6.);
%let date2=%sysfunc(intnx(month,&start.,&i+1),YYMMN6.);
%put &=date &=date2 ;
%end;
%mend ck;

1727  %ck(start=201701,end=201702);
DATE=201701 DATE2=201702
DATE=201702 DATE2=201703

Re: Issue with intnx function within a macro

ChrisNZ — Tue, 14 Apr 2020 00:15:00 GMT

This is not a macro or intnx issue.

Your variable &date contains a formatted date.

intnx() needs the raw value, a SAS date, as its input.

Re: Issue with intnx function within a macro

ItsAProccess — Tue, 14 Apr 2020 00:26:47 GMT

Thanks!!