increment month in do loop macro

[LearnByMistk]

I'm facing this weird issue 

I would like to use the yearmonth timestamp as table name to my database table but it's getting error out :

%macro increment_yymm;

proc sql;

 % do i= 0 %to 5 ;

%let new_mnt = %sysfunc(intnx(month,%sysfunc(%input(&Month_1.,$6.),i,b); 

create table mylib.table_&new_mnt. as

select * from table1_&new_mnt. ;

%end;

quit;

%mend;

%increment_yymm;

Error:1Macro keyword input appears as text

A dummy macro will be compiled.

Approach 2 

%macro increment_yymm;

proc sql;

 % do i= 0 %to 5 ;

%let new_mnt = %sysfunc(intnx(month,%sysfunc(input(&Month_1.,$6.),i,b); 

create table mylib.table_&new_mnt. as

select * from table1_&new_mnt. ;

%end;

quit;

%mend;

%increment_yymm;

Error: the input function referenced in %sysfunc is not found 

Error: %sysevalf function has no expression to evaluate

Error: Argument 2 to function intnx referenced by %sysfunc is not a  number

[Reeza]

What does &month_1 look like?

This link has an example of looping through dates in a macro.
Examples of common macro usage

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

---

@LearnByMistk wrote:

I'm facing this weird issue 

I would like to use the yearmonth timestamp as table name to my database table but it's getting error out :

%macro increment_yymm;

proc sql;

 % do i= 0 %to 5 ;

%let new_mnt = %sysfunc(intnx(month,%sysfunc(%input(&Month_1.,$6.),i,b); 

create table mylib.table_&new_mnt. as

select * from table1_&new_mnt. ;

%end;

quit;

%mend;

%increment_yymm;

Error:1Macro keyword input appears as text

A dummy macro will be compiled.

Approach 2 

%macro increment_yymm;

proc sql;

 % do i= 0 %to 5 ;

%let new_mnt = %sysfunc(intnx(month,%sysfunc(input(&Month_1.,$6.),i,b); 

create table mylib.table_&new_mnt. as

select * from table1_&new_mnt. ;

%end;

quit;

%mend;

%increment_yymm;

Error: the input function referenced in %sysfunc is not found 

Error: %sysevalf function has no expression to evaluate

Error: Argument 2 to function intnx referenced by %sysfunc is not a  number

---

[LearnByMistk]

do i= 0 to 5 ;

so intnx should work as  increment month_1 +0 ie same month

increment month_1 +1 ie next month ie 201908 

increment month_1 +2 ie next month ie 201909 and so on

 so it will increment like 

Month_1	201907
Month_2	201908
Month_3	201909
Month_4	201910
Month_5	201911
Month_6	201912
Month_7	202001

[Tom · Accepted solution]

So you have some macro variable that has the starting month as YYYYMM digits?

And you have some other macro variable that has a number, like 5, and you then want to run the loop 6 times?

%macro month_loop(start_month,n);
%Local offset basedate month ;
%let basedate=%sysfunc(inputn(&start_month,yymmn6.));
%do offset=0 %to &n;
  %let month=%sysfunc(intnx(month,&basedate,&offset),yymmn6.);
... rest of code here that uses MONTH and OFFSET ...
%end;
%mend month_loop;

Or did you want to run it only 5 times and have the index count from 1?

%macro month_loop(start_month,n);
%Local month_num basedate month ;
%let basedate=%sysfunc(inputn(&start_month,yymmn6.));
%do month_num =1 %to &n;
  %let month=%sysfunc(intnx(month,&basedate,&month_num -1),yymmn6.);
... rest of code here that uses MONTH and month_num ...
%end;
%mend month_loop;

[LearnByMistk]

options mprint mlogic symbolgen;
%macro month_date_ftch;
%local month_num basemonth month;
proc sql stimer ;
%let basemonth=%sysfunc(inputn(&Month_6.,yymmn6.));
%do month_num =0 %to 5;
%let month= %sysfunc(intnx(month,&basemonth,&month_num-1),yymmn6.);
%put &month.;
create table perm.mytab_&month. as
select a.sbscr_nbr,a.yyyymm,a.qoe_global_score from pd.QOE_SUMRY_&month. a
,perm.el_a_exl_sub_&yyyymm. b
where
b.sbscr_nbr=a.sbscr_nbr;
%end;
quit;
%MEND month_date_ftch;
%month_date_ftch;

SYMBOLGEN: Macro variable MONTH_6 resolves to 77798
MLOGIC(MONTH_DATE_FTCH): %DO loop beginning; index variable MONTH_NUM; start value is 0; stop value is 5; by value is 1.
MLOGIC(MONTH_DATE_FTCH): %LET (variable name is MONTH)
SYMBOLGEN: Macro variable BASEMONTH resolves to 2125558
SYMBOLGEN: Macro variable MONTH_NUM resolves to 0
MLOGIC(MONTH_DATE_FTCH): %PUT &month.
SYMBOLGEN: Macro variable MONTH resolves to 777907
777907
SYMBOLGEN: Macro variable MONTH resolves to 777907
SYMBOLGEN: Macro variable MONTH resolves to 777907
SYMBOLGEN: Macro variable YYYYMM resolves to 202001
MPRINT(MONTH_DATE_FTCH): create table perm.mytab_777907 as select a.sbscr_nbr,a.yyyymm,a.qoe_global_score from
pd.QOE_SBSCR_SUMMARY_777907 a ,perm.el_a_exl_sub_202001 b where b.sbscr_nbr=a.sbscr_nbr;
ERROR: File PD.QOE_SUMRY_777907.DATA does not exist.

why the highlightd part isnot converting as yymmn6. format is it coz &month_6. is a char field being a macro variable

[PaigeMiller]

SYMBOLGEN: Macro variable MONTH_6 resolves to 77798

Does that seem remotely correct? It seems as if you have not assigned the value of &MONTH_6 in a meaningful way.

[Tom]

You started with MONTH_6 having a value of 77798.

The code is assuming that MONTH_6 has a value in YYYYMM digits.

[LearnByMistk]

thanks I tired to defined month_6 again with in the macro and now it's running.

[LearnByMistk]

Thanks alot , really appreciate the help.

[PaigeMiller]

% do i= 0 %to 5 ;

So the obvious problem here is that there is a space between % and DO. I doubt anything after that will work.

To further your debugging in the future, please use 

options mprint;

at the start of your program, this writes useful information into the log. If you still need even further information, use

options mprint symbolgen mlogic;