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!
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;
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;
Brilliant. Why work harder when you can work smarter? 🙂 thank you!
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.
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.