%EVAL within DO Loop in PROC SQL

[akim64]

Hello SAS Community!

I am trying to make my life easier by implementing DO loops throughout my code because otherwise I will have to run this a bajillion times.  I'm running into an error when I use the %EVAL() function within a variable.

I have the following example data set:

Cust_ID	Start_Date	End_Date	Class	Close_Flag
123	1/4/2016	5/1/2017	A	CLOSE
456	1/27/2016	2/19/2017	B	UP
789	1/15/2016	3/20/2019	C	DOWN

I also created several date variables in an earlier part of my code:

&MonthEnd0 = Jan 1, 2017;

&MonthEnd1 = Jan 31, 2017;

&MonthEnd2 = Feb 28, 2017;

&MonthEnd3 = Mar 31, 2017;

etc.

I eventually want to end up with something like the following:

Cust_ID	Start_Date	End_Date	Class	Close_Flag	Month1Status	Month2Status	…
123	1/4/2016	5/1/2017	A	CLOSE	OPEN	OPEN
456	1/27/2016	2/19/2017	B	UP	OPEN	UP
789	1/15/2016	3/20/2019	C	DOWN	OPEN	OPEN

I'm trying to run a case statement where if the End_Date falls between the previous MonthEnd variable and the next MonthEnd variable, then it returns the Close_Flag; otherwise, return 'OPEN'.  I want to do this for several months.  So I tried this:

%macro mymacro;

proc sql;
create table MyTable as 
	select 	Cust_ID,
		%do i=1 %to 10;
		case 
			when End_Date between &&MonthEnd%EVAL(&i.-1) and &&MonthEnd&i. then Close_Flag
			when End_Date > &&MonthEnd&i. then 'OPEN'
			else 'SomethingsWrong'
		end as Month&i.Status,
		%end;
		Class
	from	MyOtherTable;
quit;

%mend mymacro;

%mymacro;

I seem to be getting an error at the %EVAL portion of the code-- specifically, it says "Apparent symbolic reference MONTHEND not resolved."  When I comment out that line, the code works.  When I get rid of the %EVAL function, the code works.  But I don't know how to filter between the two dates otherwise without using the %eval function.  Am I missing an ampersand somewhere???

Thanks in advance!

[Reeza]

You'll need to show more of your code, but your little snippet of how you created your macro variables appears wrong? It sounds like you're classifying data into bins based on months or creating indicator variables for those months? This would work a lot easier within a data step using an array. Is that an option?

[Astounding · Accepted solution]

Most  likely, macro language is attempting to resolve &&MonthEnd before even touching %EVAL.  Just compute the %EVAL result first:

		%do i=1 %to 10;
                %let k = %eval(&i - 1);
		case 
			when End_Date between &&MonthEnd&k and &&MonthEnd&i. then Close_Flag
			when End_Date > &&MonthEnd&i. then 'OPEN'
			else 'SomethingsWrong'
		end as Month&i.Status,
		%end;

[akim64]

Brilliant.  Why work harder when you can work smarter?  🙂 thank you!

[Reeza]

Why not change the loop for i to 0 to 9 instead of 1 to 10?

[akim64]

I tried that! But ...

Whether I do i = 1 to 10

when EndDate between &&MonthEnd%EVAL(&i.-1) and &&MonthEnd&i. then Close_Flag

or whether I do i = 0 to 9

when EndDate between &&MonthEnd&i. and &&MonthEnd%EVAL(&i.+1) then Close_Flag

 I would still be running into the same issue with the %EVAL function because I need the next month in the between statement.

[Reeza]

Yeah, this would be much easier in a data step, no macros needed and easier to loop. Or using some date logics.