topic Re: The %TO value of the %DO I loop is invalid in New SAS User

The %TO value of the %DO I loop is invalid

Michelle_ — Fri, 14 Jul 2023 18:08:10 GMT

Hi all, i have write the macro as shown below.

Initially, I direct put the begin date and end date (example stated in orange font below). The macro manages to run.

However, for ease of efficiency in future, I would like to set a formula to indicate the begin date and end date. But, it pops the error as below.

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: 
       &dif 
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro DATECREATE will stop executing.

%let Period = 202304;
%let Month  = 4;

%macro month_setting();
    %global begin end;

    %if %sysevalf(&month = 4) %then 
        %do;		
	    %let begin		= put(intnx('month',input(strip(put(&period, 6.)||'01'), yymmdd10.), -6), date9.);
	    %let end		= put(intnx('month',input(strip(put(&period, 6.)||'01'), yymmdd10.), -1), date9.);
/*	    %let begin 		= 01OCT2022;*/
/*	    %let end 		= 01APR2023;*/
        %end;
	%else %if %sysevalf(&month = 13) %then
        %do;
	    %let begin		= put(intnx('month',input(strip(put(&period, 6.)||'01'), yymmdd10.), -13), date9.);
	    %let end		= put(intnx('month',input(strip(put(&period, 6.)||'01'), yymmdd10.), -1), date9.);
/*	    %let begin 		= 01JAN2022;*/
/*	    %let end 		= 01APR2023;*/
        %end;

%mend month_setting;
%month_setting();

%macro datecreate;
	%let startdate  = %sysfunc(inputn(&begin,anydtdte9.));
	%let enddate	= %sysfunc(inputn(&end,anydtdte9.));
	%let dif	= %sysfunc(intck(MONTH,&startdate,&enddate)); 
		%do i=0 %to &dif;
			%global date_&i;
			%let date_&i=%sysfunc(intnx(month,&startdate,&i,B),yymmn6.);
			%put &&date_&i;
		%end;
%mend;
%datecreate;

Would like to seek for help to solve the issue. Thanks in advance!

Re: The %TO value of the %DO I loop is invalid

Tom — Fri, 14 Jul 2023 18:40:33 GMT

Simple enough to analyze.

Start with the error. The only way &DIF is not value is if the result of the function call that created it is a missing value.

So look at the arguments to that function call.

%let dif = %sysfunc(intck(MONTH,&startdate,&enddate));

The first one is fine.

So where did those two macro variable get created? There are two choices, but they both follow the same (incorrect) pattern.

%let begin = put(intnx('month',input(strip(put(&period, 6.)||'01'), yymmdd10.), -6), date9.);
%let end = put(intnx('month',input(strip(put(&period, 6.)||'01'), yymmdd10.), -1), date9.);

The answer is obvious. You are setting BEGIN and END to strings that start with the letters PUT. Those cannot be a valid number to pass to the INTNX() function.

You clearly understand that you have to use %SYSFUNC() to call SAS functions in macro code because you used it when trying to make the macro variable DIF. So you need to use it when making BEGIN and END also.

%let begin = %sysfunc(intnx(month,%sysfunc(inputN(&period.01,yymmdd10.)),-6));
%let end = %sysfunc(intnx(month,&begin,+5));

Do not FORMAT the values with the DATE9. format if the goal is to use them as DATE values (and not text strings for humans to read). But if you did set the macro variables to strings like 01JAN2023 then to use them as actual date values you would need to add quotes and the letter D to create date literals.

%let begin = "%sysfunc(intnx(month,%sysfunc(inputN(&period.01,yymmdd10.)),-6),date9.)"d;
%let end = "%sysfunc(intnx(month,&begin,+5),date9.)"d;

Re: The %TO value of the %DO I loop is invalid

Kurt_Bremser — Fri, 14 Jul 2023 18:41:58 GMT

&begin will resolve to the text

put(intnx('month',input(strip(put(&period, 6.)||'01'), yymmdd10.), -6), date9.)

which can not be used as argument for the INPUTN function. Similar for &end.

Always keep in mind that a DATA step function will only work in macro language when wrapped in %SYSFUNC.

Re: The %TO value of the %DO I loop is invalid

Michelle_ — Sat, 15 Jul 2023 06:33:09 GMT

It works! Thank you so much.