topic Re: increment month in do loop macro in SAS Programming

increment month in do loop macro

LearnByMistk — Thu, 20 Feb 2020 16:47:25 GMT

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

Re: increment month in do loop macro

Reeza — Thu, 20 Feb 2020 16:57:03 GMT

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-Appendix/ta-p/291716

@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

Re: increment month in do loop macro

LearnByMistk — Thu, 20 Feb 2020 17:31:41 GMT

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

Re: increment month in do loop macro

Tom — Thu, 20 Feb 2020 17:44:58 GMT

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;

Re: increment month in do loop macro

PaigeMiller — Thu, 20 Feb 2020 18:00:00 GMT

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

Re: increment month in do loop macro

LearnByMistk — Thu, 20 Feb 2020 18:28:53 GMT

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

Re: increment month in do loop macro

PaigeMiller — Thu, 20 Feb 2020 18:34:49 GMT

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.

Re: increment month in do loop macro

Tom — Thu, 20 Feb 2020 18:34:58 GMT

You started with MONTH_6 having a value of 77798.

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

Re: increment month in do loop macro

LearnByMistk — Thu, 20 Feb 2020 18:41:44 GMT

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

Re: increment month in do loop macro

LearnByMistk — Thu, 20 Feb 2020 18:42:15 GMT

Thanks alot , really appreciate the help.