Null value for a Parameter

[deleted_user]

I load values into a Parameter for a stored process. The values come from a sql table. one of the values that shows up is NO Department with a value of "NO Department". This apparently does not mean null. I had the parameters working before I converted them over to a store process. If the user did not enter or choose anything from the drop down for the dept, I have a case statement that that seemed to work...
WHERE mascrs.MscSession = "&Semester" AND CASE WHEN "&Dept" Not IS MISSING THEN mascrs.MscDept = "&Dept" ELSE mascrs.MscDept NOT IS MISSING END

However when I make this into a stored process and change the &DEPT parameter so that is lists the name of the dept and the values I have the "No Department" option and instead of getting everyone for all deptments, I get nothing.

Can you help me see a better way to do this? Thanks

[deleted_user]

It doesn't make sense to me that an SQL table can have "NO Department" as a value when the table doesn't. That is, unless you have created a format for the column value and add a format for a missing value which translates to "NO Department".

You need to present the UNformatted values to the user for selection since it is these that will be used to select against the data table.

Kind regards

David

[deleted_user]

I'm not sure how the values get put in myself, I tried unchecking the box apply formating when the Parameter Values are loaded. But I get the same thing.
When I Load Parameter Values in EG Filter and query. It only loads the values.(DeptNUM)
For example...(the first line below is a blank)

18
10
03
97
05
04
08
etc.....
When I do a Create Stored Process and it takes all my code and puts it into a stored process. I then go into the Stored Process Parameters option and Here it shows me 2 columns... Display as... and Resolves to.... When I first open it up both columns are the same. I do a load Values and this time choose DeptName for the Display as column and DEPTNUM for Resolves to
I then get the following
NO DEPARTMENT NO DEPARTMENT
ACADEMIC SUPPORT SERVICES 18
ACCOUNTING 10
ADM SUPR & FIELD SERV 03
AEROSPACE STUDIES 97
ARABIC 05
ART 04
BIOLOGY 08

I can not blank out "NO Department" in the Resolves to column ...it requires something. What I want to happen is if the parameter is not selected ... is blank then give me all of them. That is how it works before I put it into a Stored proc.
Can you tell me how these parameters get set when loading values within a stored process?

[Cynthia_sas]

Hi, Cheri:
Here's the deal. When EG builds that parameter list, it allows you to have a 'blank' -- inside EG -- as a possible parameter choice.

However, when EG goes to create a stored process for you and builds the parameter value choices, it does look like you have a user-defined format in place, because otherwise, you would see just the number in both "Display As" and "Resolves To". EG is either putting NO DEPARTMENT into the stored process parameter choice values based on your user defined format -- or is doing it because it does not allow a "blank" character as a choice -- which I can understand. Passing a quoted string or a blank requires that the character be "protected" and protecting a quote or a space for transmission involves some advanced macro programming that is not easily understandable.

But, to deal with the fact that EG put "NO DEPARTMENT" (perhaps based on your user-defined format), you can CHANGE the stored process code for your test. Something like this:
[pre]
%macro testdnum;
%if &DeptNUM = NO DEPARTMENT %then %do;
proc print data=deptdata;
title "No Where clause -- get all departments";
run;
%end;
%else %if &DeptNUM ne NO DEPARTMENT %then %do;
proc print data=deptdata;
where deptnum = &DeptNum;
/* OR where deptnum = "&DeptNUM"; if the deptnum is character */
title "With Where clause -- get only department &deptnum";
run;
%end;
%mend testdnum;
[/pre]

Of course, EG will have put a LOT more code into the stored process. So before you take this approach, you might actually want to contact Tech Support for more help. Changing the code generated by EG can be tricky, because to use Macro conditional logic (%IF), you need to have a macro program (what's defined by the %MACRO and %MEND).

You must already have some logic in place in the EG project for what happened when they picked a space. There are other ways to deal with this than the method I suggest. However, I think that your best bet for a correct answer -- given your data and your parameters and the amount of code you want to conditionally execute -- is to contact Tech Support for help.

To contact Tech Support, go to: http://support.sas.com and on the left hand side, look for the link called "Submit a Problem".

cynthia