BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LearnByMistk
Obsidian | Level 7

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

 

View solution in original post

9 REPLIES 9
Reeza
Super User

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
Obsidian | Level 7

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
Super User Tom
Super User

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
Obsidian | Level 7

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
Diamond | Level 26
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.

--
Paige Miller
Tom
Super User Tom
Super User

You started with MONTH_6 having a value of 77798.

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

LearnByMistk
Obsidian | Level 7

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

 

LearnByMistk
Obsidian | Level 7

 

Thanks alot , really appreciate the help.

PaigeMiller
Diamond | Level 26
% 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;

 

--
Paige Miller

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2368 views
  • 2 likes
  • 4 in conversation