PROC SQL;
select count(distinct REPORT_ID) into :REPORT_ID_Count TRIMMED from REPORTS;
select distinct REPORT_ID into :REPORT_IDVal1- :REPORT_IDVal&REPORT_ID_Count from REPORTS;
quit;
%macro EACH_REPORT;
%do index = 1 %to &REPORT_ID_Count;
/*BUNCH OF PROC SQL STATEMENTS AND DATA STEPS*/
PROC SQL NOPRINT;
SELECT ADJUSTED_SCORE
INTO :ADJ_SCORE
FROM Final_Match ;
QUIT;
/*If all adjusted_SCORES are greater than 100 then end here go to the next report*/
/*BUNCH OF PROC SQL */
%end;
%mend;
%EACH_REPORT
I am repeating a set of statements within a macro for each report_id. But I want to end the step if all adjusted scores are greater than 100 and go the next report _id. How do I do this?
using sas EG 7.12
Please just post a simple example of the type of flow control you want to create.
There is no %LEAVE or %CONTINUE statement in macro code. And unlike the data step DO the macro %DO does not allow multiple clauses so you can't create the equivalent of the data step
do i=1 to 10 while (condition) ;
So just roll your own flow control by using labels and %GOTO statement.
%put BEFORE DO loop;
%do i=1 %to 10 ;
...
%if %sysevalf(&x > 100) %then %goto after_loop ;
....
%end;
after_loop:
%put AFTER DO loop;
Use %RETURN to end the execution of macro.
%IF Score>100 %THEN %RETURN;
My adjusted score values might be more than one sometimes.
PROC SQL NOPRINT;SELECT ADJ_SCORE INTO :ADJ_SCORE FROM Final_Match ;QUIT;
%put macro variable ADJ_SCORE:&ADJ_SCORE;
so how do I test if all the values are greater than 100?
%IF Score>100 %THEN %RETURN;
and using the above statement, will it go the next iteration of report_id?
All of WHAT values? Your example code is just putting ONE value into the macro variable.
Are you asking how to use use the MAX() aggregate function in PROC SQL?
If you want to know what the %RETURN macro statement does you can look at the documentation.
Or just make a test and run it.
Try this:
%macro xx;
%put BEFORE LOOP;
%do i=1 %to 10 ;
%put &=i;
%if &i >= 4 %then %return;
%end;
%put AFTER LOOP;
%mend xx;
%xx;
PROC SQL NOPRINT;SELECT MIN(ADJUSTED_Score)as ADJ_SCORE INTO :ADJ_SCORE FROM Final_Match ;QUIT;
%put macro variable ADJ_SCORE:&ADJ_SCORE;
%if &ADJ_SCORE >= 100 %then %return;
This is what I did. For the first Report_id it performed but for next REPORT_id it gave error
macro variable ADJ_SCORE:-93.3333
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
&ADJ_SCORE >= 100
ERROR: The macro EACH_REPORT will stop executing.
If you want to use non-integer arithmetic in macro code you need to explicitly use %SYSEVALF(). Otherwise SAS will use %EVAL() which can only do integer arithmetic.
%if %sysevalf( &ADJ_SCORE >= 100.0) %then %return;
This corrected the previous error but what I realised is that it is terminating the macro but I want it to go to the next report_ID. So I did %end instead of %return but it complaints at the next %END in the macro
ERROR: There is no matching %DO statement for the %END. This statement will be ignored.
Please just post a simple example of the type of flow control you want to create.
There is no %LEAVE or %CONTINUE statement in macro code. And unlike the data step DO the macro %DO does not allow multiple clauses so you can't create the equivalent of the data step
do i=1 to 10 while (condition) ;
So just roll your own flow control by using labels and %GOTO statement.
%put BEFORE DO loop;
%do i=1 %to 10 ;
...
%if %sysevalf(&x > 100) %then %goto after_loop ;
....
%end;
after_loop:
%put AFTER DO loop;
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.