marco parameter

[deleted_user]

I have saved some criteria(c1) in a dataset
I failed to pass the criteria into a marco

Aim : marco can execute the criteria saved in the dataset

Please help..................

%macro extractsth(criteria);
proc sql;
create table result as select * from testdata
where &criteria;
quit;
%mend;

data testdata;
input age sex $ weight salary;
cards;
32 m 150 20
27 f 108 22
45 m 200 48
run;

data allcriteria;
input c1 $;
cards;
age<40
run;

/*success*/
*%extractsth(age<40); /* << can age<40 saved in a dataset?*/

/*failure1 */
data _null_;
set allcriteria;
%extractsth('c1');
run;

/*failure2 */
data _null_;
set allcriteria;
%extractsth('c1'); /*wrong output*/
run;

/*failure3*/
data _null_;
set allcriteria;
symput('c1',c1)
%extractsth(symget('c1')); /*syntax error*/
run;

[deleted_user]

%extractsth(age<40); can age<40 saved in a dataset? Message was edited by: makchishing

[deleted_user]

/*failure1 */
data _null_;
set allcriteria;
%extractsth('c1');
run;

/*failure2 */
data _null_;
set allcriteria;
%extractsth('c1'); /*wrong output*/
run;

/*failure3*/
data _null_;
set allcriteria;
symput('c1',c1)
%extractsth(symget('c1')); /*syntax error*/
run;

[Cynthia_sas]

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

[deleted_user]

Thank you Cynthia,
I am using SAS9,

My working is
------------------------------------------------------------------------
proc sql;
create table result as select * from testdata
where age<10;
quit;

proc sql;
create table result as select * from testdata
where age<20;
quit;

proc sql;
create table result as select * from testdata
where age<30;
quit;
------------------------------------------------------------------------
and this marco work successfully
%extractsth(age<10);
%extractsth(age<20);
%extractsth(age<30);
...
%extractsth(age<100);
------------------------------------------------------------------------
It seems that
there is no method that to save the "criteria" i.e. age <10,age <20,age <30.., into a dataset then do a looping, instead of to duplicate sas code ?

Is that true >0

shing


------------------
I am trying to use call execute to do so Message was edited by: makchishing

[Cynthia_sas]

Hi:
You -can- create numbered macro variables from variables in a SAS dataset or from multiple %LET statements -- it really depends on how and where your information exists that needs to become part of the macro process. Then you can use those numbered variables in a macro %DO loop inside a macro program to generate iterative or repetitious code (where only a few things change for every repetition of the code).

For example, if I used %LET to create numbered macro variables, the program below would generate 3 PROC SQL steps. A more advanced way to create the numbered macro variables would be to use CALL SYMPUT or PROC SQL (which was discussed in the paper link I provided previously.) An even more advanced way to create and generate macro calls to execute a macro program multiple times would be to use CALL EXECUTE.

If you search Google with the string:
SAS macro call symput

You will come up with some useful user group papers, these among them:
http://www2.sas.com/proceedings/sugi29/243-29.pdf
http://www2.sas.com/proceedings/sugi29/052-29.pdf
http://www.nesug.org/Proceedings/nesug02/hw/hw003.pdf
http://www2.sas.com/proceedings/sugi31/039-31.pdf

cynthia
[pre]
%global c1 c2 c3;
%let c1 = %str(age=12);
%let c2 = %str(age=15);
%let c3 = %str(age>13);

%macro loopsql;
%do i = 1 %to 3;
title "condition is c&i: &&c&i";
proc sql;
select *
from sashelp.class
where &&c&i;
quit;
%end;
%mend loopsql;

options mprint symbolgen;
ods listing;
%loopsql;

[/pre]

[sbb]

Based on the OP initial post, the SAS "expression" logic is contained in a SAS data library member. For the defined macro, there is no other "variable logic" to consider. So, the simplest approach is to use CALL EXECUTE in a DATA step and generate a suitable command execution data-string variable, which will be executed after the first DATA step terminates, based on the observations in WORK.allcriteria.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument:

call execute generate code site:sas.com