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
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;
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
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 |
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;
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
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.
You started with MONTH_6 having a value of 77798.
The code is assuming that MONTH_6 has a value in YYYYMM digits.
thanks I tired to defined month_6 again with in the macro and now it's running.
Thanks alot , really appreciate the help.
% 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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.