Hi:
Before you write a SAS Macro program, you should have a WORKING program that produces the code that you want to execute inside a Macro program.
For example, let's look at your "failure 1" example:
[pre]
/*failure1 */
data _null_;
set allcriteria;
%extractsth('c1');
run;
[/pre]
If %EXTRACTSTH is this code:
[pre]
%macro extractsth(criteria);
proc sql;
create table result as select * from testdata
where &criteria;
quit;
%mend;
[/pre]
Then, effectively, the "resolved" SAS code that you would be trying to send to the compiler would be:
[pre]
data _null_;
set allcriteria;
proc sql;
create table result as select * from testdata
where 'c1';
quit;
run;
[/pre]
Have you tested this or any version of this program without SAS macro code being involved? The PROC SQL statement effectively will provide a step boundary, which causes the DATA _NULL_ program to end.
The SAS Macro facility and SAS Macro programs do not "execute" any code. The purpose of the Macro facility is to create SAS code -- either whole programs or statements or partial statements -- which can then be sent to the SAS compiler for compilation and statement execution. When you invoke the macro program %EXTRACTSTH -- the macro word scanner and tokenizer takes whatever code is found in the macro progarm (between the %macro and %mend) and resolves any macro variable references in that code and then sends that code to the SAS compiler, where the code is compiled and executed.
In the way, the SAS Macro facility is conceptually behaving like a typewriter -- it is ONLY typing code for you. The power of the macro facility comes into play when you use %IF conditions inside your macro program in order to allow the macro facility to conditionally build code to be sent to the compiler.
For example, you could have a Macro program like this:
[pre]
%macro dorept(type=);
ods listing;
%let type = %upcase(&type);
%if &type eq SHOES %then %do;
proc print data=sashelp.shoes(obs=3);
title "Type is &type";
run;
%end;
%else %if &type eq CLASS %then %do;
title "Type is &type";
proc sql;
select * from sashelp.class;
quit;
%end;
%else %do;
** message for SAS Log;
%put *************************************************;
%put Note: You asked for: &type as the value for type=;
%put You can only select "SHOES" or "CLASS";
%put Try again please.;
%put *************************************************;
%end;
%mend dorept;
%dorept(type=shoes);
%dorept(type=class);
%dorept(type=other);
[/pre]
In the above macro program, the %IF and %ELSE statements are controlling what code gets sent to the compiler based on the value of the &TYPE parameter. For any execution of the macro program, only 1 thing will happen -- either &TYPE=SHOES and the PROC PRINT gets sent to the compiler OR the &TYPE=CLASS and the PROC SQL gets sent to the compiler OR the series of %PUT statements are sent to the SAS log. This is only 1 example of the type of macro program that you could write.
However, before you can write a macro program, the recommended approach is to understand how SAS macro processing works and to start with a WORKING program that produces the output you want without the Macro facility being used in the code. From this, you have a starting point from which to work. It is -this- WORKING code that you want the macro facility to generate for you and send to the compiler when all the macro variables are resolved.
This paper provides a good introduction to SAS Macro concepts:
http://www2.sas.com/proceedings/sugi28/056-28.pdf
and this paper shows a lot of "data-driven" examples of macro processing:
http://www2.sas.com/proceedings/sugi24/Posters/p254-24.pdf
cynthia