Desktop productivity for business analysts and programmers

Parameters and SQL

Reply
N/A
Posts: 0

Parameters and SQL

I'm working on creating a stored process that queries our SQL database based upon certain parameters. By doing such, I edited the SAS PROC SQL code to select rows based upon those parameters. For example, I wrote a "WHERE" clause for each of those parameters. That works fine...but, if I wanted to leave one of those parameters blank, or not set, how do I query for everything...or rather select all rows rather than query based upon the parameter?

This probably doesn't make too much sense and I probably need to clarify a bit more. Let me know if that is the case.

~Phil
SAS Super FREQ
Posts: 8,720

Re: Parameters and SQL

Hi:
You can alter your stored process code to include SAS Macro conditional logic, based on parameter values or based on parameters having some default value. Depending on your stored process and your data, you may want to create a SAS macro program to execute 2 different PROC SQL steps. Or you may want to split your differing SQL queries into different macro programs. Ther are a lot of different ways to add conditional logic to your stored process. Imagine something like this:
[pre]
%macro makesql;
%if &how = USEPARM %then %do;
proc sql;
create table work.wombat as
select *
from perm.master
where var1 = "&parm1" and other = "&parm2"
order by var1;
quit;
%end;
%else %do;
proc sql;
create table work.wombat as
select *
from perm.master
order by var1;
quit;
%end;
%mend makesql;
[/pre]
So in the above example, if the parameter &HOW is set to USEPARM, then the PROC SQL step includes a WHERE clause that uses input parameters. If the value of &HOW is anything else (empty value, something else), then the PROC SQL step has no WHERE statement. There are other ways to generate code like this, but for this example, inside your stored process, you'd have:
[pre]
%global how parm1 parm2;
*ProcessBody;
%stpbegin;

** this macro invocation will put the correct SQL step into the stored process;
** based on values of the macro parameter &HOW;
%makesql;

** no matter which PROC SQL step was used, still want to have a;
** PROC PRINT on work.wombat;
proc print data=work.wombat;
run;

%stpend;
[/pre]

Tech Support can help you figure out the best way to add macro conditional logic to your stored process. You can generate whole steps with macro code or you can generate single statements or parts of statements. It is a very powerful feature of SAS and if you're not familiar with macro processing, the documentation would be a good place to start, along with getting help from Tech Support.

cynthia
N/A
Posts: 0

Re: Parameters and SQL

> %if &how = USEPARM %then %do;


In this example, how would I determine whether or not &how has been set or has a value. Instead of checking to see if it equals something, I'd like to know how to check to see whether it has been set to a value.

Thanks for the informative post!
SAS Super FREQ
Posts: 8,720

Re: Parameters and SQL

Hi...well, when you write the stored process (or use the EG wizard to create the SP), you should find this statement
[pre]
%Global how othrparm;
[/pre]
which tells the server that it will have 2 macro variables coming down the road for your stored process. So, you would test for NO VALUE with:
[pre]
%if &HOW=;
[/pre]
When that statement is TRUE, &HOW is in the GLOBAL symbol table (because of the %GLOBAL statement), but no value came from the client application. The negative of that condition:
[pre]
%if &HOW NE ;
[/pre]
means that &HOW has SOME value, you just don't know WHAT. And this:
[pre]
%if &HOW = USEPARM ;
[/pre]
was either set by the client application based on input parameters that you set up, or you assigned a value to &HOW based on some combination of testing the other input parameters that you did.

Here's the only warning, however, a %IF statement cannot be used in "OPEN" code, like a DATA step program or inside a PROC step. You have to use %if and %do inside a macro program -- this is the thing that you'll need help with from Tech Support.

cynthia
N/A
Posts: 0

Re: Parameters and SQL

Cynthia,

I went ahead and used this line of code:
%if %length(&majorparm) %then %do;

&majorparm is the parameter that I'm using to run the query. By the looks of this code, I'm guessing that if &majorparm has a length, then the code located after the %do statement will be run. If &majorparm isn't set, there is no length; therefore it will not run the code after the %do statement.

So far this has worked perfectly for me in my stored process.

Thanks so much for your help on writing my first SAS SP!
Ask a Question
Discussion stats
  • 4 replies
  • 125 views
  • 0 likes
  • 2 in conversation