Hi,
Let's fall back to the position of a single parameter value. First I have to have a working SAS program. So let's do this for step 1 -- have a working sas program.
[pre]
*** Do NOT turn this WHOLE program into a stored process;
*** Execute STEP1 only or store it in a code node. Submit;
*** ONLY the STEP1 code, review the log and the output;
*** step 1 have a working sas program with hardcoded values;
*** that produce the 2 alternating kinds of output that you want;
*** this is the only way that you can understand the kind of code;
*** that you eventual macro program has to produce conditionally.;
proc print data=sashelp.class;
title "Only rows where age gt 13";
where age gt 13;
var name age height;
run;
proc print data=sashelp.class;
title 'this should be everybody';
title2 'because where 1 is always true';
where 1;
var name age height;
run;
*** END of step 1;
[/pre]
If I run these 2 proc prints, the code with the WHERE 1 for the where clause will tell proc print to show me every ROW because WHERE 1 means "TRUE" and is a sort of trick that I've always used when I am conditionally building a where clause and one possible condition is to give me everybody ("turn off" the WHERE clause);
Now, in STEP 2, I need to turn that code into "macroized" code with macro parameters that are hardcoded in %LET statements:
[pre]
*** Do NOT turn this WHOLE program into a stored process;
*** Then, Execute STEP2 code only or store it in a code node.;
*** Submit the STEP2 code ONLY and review the log and output;
*** step 2: change the program to use;
*** hardcoded macro variables whose values are set;
*** with %LET and use the macro variable(s) in the;
*** two versions of your code;
%let macvar = 13;
proc print data=sashelp.class;
title "Only rows where age gt &macvar";
where age gt &macvar;
var name age height;
run;
%let macvar = 1;
proc print data=sashelp.class;
title 'this should be everybody';
title2 'because where &macvar is always true';
where &macvar;
var name age height;
run;
*** end of step 2;
[/pre]
Now, assuming that Step 2 with the hardcoded macro variables works right, I can proceed to step 3 and make a macro program. I need a macro program because I want to use %if logic to set my WHERE statement and TITLE statements conditionally.
[pre]
*** Do NOT turn this WHOLE program into a stored process;
*** Then, execute STEP3 code only or store it in a code node.;
*** Submit the STEP3 code ONLY and then review the log and output;
*** step 3 -- because I need 2 different;
*** where clauses and 2 different titles;
*** this points to the need for a macro ;
*** program so the titles and where;
*** can be set conditionally.;
*** at this point, I decide that since I;
*** may someday want ages gt 1 in a where clause;
*** that I will send a value of NONE for MACVAR;
*** in order to use the "WHERE 1" syntax;
*** and thus get every row in the file ;
*** or "turn off" the where clause.;
*** NOTE, there are other solutions to this problem;
*** but I prefer to be explicit and show;
*** something coded for each condition.;
%macro chgwhere;
** begin of proc print;
proc print data=sashelp.class;
** title and where clause built conditionally;
** note that the macro facility is ONLY generating;
** the code that I want based on the values of ;
** MACVAR -- I am using that to create a new macro variable;
** called WHERECLS that will be used in my eventual where clause.;
%if &macvar = none %then %do;
%let wherecls = 1;
title 'this should be everybody';
title2 'because where 1 is always true';
%end;
%else %do;
%let wherecls = age gt &macvar;
title "Only rows where age gt &macvar";
title2 "because the wherecls macro var was built conditionally";
%end;
** end of proc print;
where &wherecls;
var name age height;
run;
%mend chgwhere;
options mprint mlogic symbolgen;
** test none value;
%let macvar = none;
%chgwhere;
** test some valid value;
%let macvar = 13;
%chgwhere;
** and make sure that I turn off my "debugging" options.;
options nomprint nomlogic nosymbolgen;
*** end of step 3;
[/pre]
At this point, you should have a fairly good understanding about how YOUR program needs to change or how you need to code your MACRO program to generate a macro variable conditionally. Understanding the macro coding necessary for a single value parameter -- as I have shown in my program -- is hard enough to understand without getting into issues of multiple value parameter selections (as we have done in previous posts).
If you have not gone through steps 1 to 3 with your code (NOT in a stored process), you might not have a very clear understanding of how macro programs work in general SAS code. So I highly recommend taking a step back from trying to do this in a stored process and get a couple of SAS programs working each of which represents a possible condition for which you will need to generate output or a report. It will be MUCH easier for you to debug the macro logic and parameters if you have a clear understanding of what your code needs to look like, because you can then compare your "starter" code to the code that you see when you use the MPRINT option and you can see where your logic or macro variable resolution is failing.
Finally, when you have a STEP 3 macro program that works under all your condition tests, you can proceed to STEP4 and turn the STEP3 program into a stored process. If you had a working STEP3 program, the conversion to a STORED PROCESS is very straightforward:
[pre]
***** STOP ***** STOP ***** STOP ***** STOP *****;
***** STOP -- do NOT turn the code below into ;
***** a stored process until you understand what is;
***** happening in steps 1-3;
*** step 4: assuming that step 3 produces the output;
*** that you want, turn step 3 program into a stored process;
*** register the stored process in the metadata;
*** and register the parameters for the MACVAR parameter;
*** AND store only the step 4 code in a .SAS program.;
*** when you define my stored process parameters,
*** you MUST code
*** none, 12, 13, 14, 15
*** as the valid values for the macvar parameter and
*** for now, it must be a SINGLE selection parameter
*** value in order to illustrate this point of how;
*** to change the WHERE clause conditionally.;
%global macvar wherecls;
%macro chgwhere;
** begin of proc print;
proc print data=sashelp.class;
** title and where clause built conditionally;
** because I do not know how the choice for "none" is;
** coded in the stored process parameter value list;
** I choose to upcase it here to be on the safe side.;
%if %upcase(&macvar) = NONE %then %do;
%let wherecls = 1;
title 'this should be everybody';
title2 'because where 1 is always true';
%end;
%else %do;
%let wherecls = age gt &macvar;
title "Only rows where age gt &macvar";
title2 "because the wherecls macro var was built conditionally";
%end;
** end of proc print;
where &wherecls;
var name age height;
run;
%mend chgwhere;
** NOW use the macro program inside the %STPBEGIN/%STPEND;
** macro invocations for the stored process.;
%stpbegin;
%chgwhere;
%stpend;
[/pre]
Note how I only need to invoke the %CHGWHERE macro program inside the
%STPBEGIN and %STPEND macro calls for the stored process. That is because my "instream" definition of the %CHGWHERE macro program has to be compiled before it can be used. At this point, I have removed ALL the hardcoded %LET statements from before the %CHGWHERE invocation because the values for MACVAR will come from the client application and the values for WHERECLS will be set from inside my macro program. That means that both MACVAR and WHERECLS need to be defined as GLOBAL macro variables, which I have done at the top of my stored process code for step 4.
At this point, I highly recommend that you contact Tech Support for more help with your macro conditional logic and the stored process that you are trying to build.
Good luck!
cynthia
ps..I used PROC PRINT instead of PROC SQL because then I did not have to worry about the semi-colon issues that you will run into with PROC SQL and the WHERE clause (versus the WHERE statement in my code). If you are generating PROC SQL, then it is REALLY important for you to have WORKING PROC SQL code before you put it into a macro program -- because the PROC SQL CREATE/SELECT clause ends with one and only one semi-colon and you have to be able to deal with this in your macro program.
Click image to register for webinar
