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

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

7 REPLIES 7
SuryaKiran
Meteorite | Level 14

Use %RETURN to end the execution of macro.

 

%IF Score>100 %THEN %RETURN;
Thanks,
Suryakiran
Vk_2
Obsidian | Level 7

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? 

Tom
Super User Tom
Super User

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;
Vk_2
Obsidian | Level 7
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.

Tom
Super User Tom
Super User

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

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.

Tom
Super User Tom
Super User

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 7 replies
  • 1145 views
  • 0 likes
  • 3 in conversation