Set up a parameter value to be blank

[dbdinc]

In a macro I'm trying to write, I've got the macro working so that it makes a good WHERE clause that my Proc SQL code uses successfully. My question now is, if, in my parameter selection, I select a value of "none".... how do I translate that in my macro step so that my WHERE clause "goes away?". I want to be able to do this so if the user wants to see all values, the where clause gets blanked out. here is my macro code piece:

%let ethniccls = "%superq(ethnicity)";
%local i;
%if %superq(ethnicity0) ne
%then %do i=2 %to %superq(ethnicity0);
%let ethniccls = &ethniccls,"%superq(ethnicity&i)";
%put ethniccls is ðniccls;
%put what you get when you reference ethniccls:;
%put (work.ethnic IN (&ethniccls));
%end;

%if ethnicity eq "none" %then %do;
%let ethniccls = " ";
%end;

The code runs, but nothing results. I'm only trying to get ethniccls to be a blank value, but feel very dumb in my attempts.

 

[klaz2002]

I first wrote this whole long answer and realized that I do not enough information to solve your problem. What is your full paramter list? It seems that you may have many ehnicity variables. I have a few more questions, but let me put them away for now.

In short what you must do is build your where clause. When the test of your parameter that has the 'none' string inside it, turns true, reset the where parameter to be blank. I also noticed that in your 'none' test What seems like a macro parameter 'ethnicity', doesn't have the '&' char in front of the parameter. Also please remember that in macro no quotes are used for comparison unless the actual value will have the quotes in it. There are all sorts of macro fuctions that strip these 'quotes' away (%superq is one of them). Here is what I would do, you of course could improvise on this method for your needs.

ex.

%let whereclause=;
%if %length(&param1) gt 0 %then %do;
   %if &param1 ne none %then %do;
       %let whereclause = WHERE &paramX in ( &Y);
   %end;
%end;

%put &whereclause;

What I have done is set the WHERE clause (and you will have to build it to your spec) and when its set to 'none' nothing happends. So you are left with what you started with, an empty whereclause.

Klaz

 

[dbdinc]

Thanks, Klaz,

I'll try your suggestions.

And, I appreciate your answering my question. It's nice to have folks take the time to help other less knowledgable folks!