06-27-2011 10:38 AM
I have two Stored Processes. The first one basically displays an HTML page where the user is allowed to select parameters for his report. The report will be displayed by the second Stored Process.
On the first page I have a multiselect field:
<select name="myfield" multiple="multiple">
<input type="hidden" name="multi_myfield" value="">
(i.e. the value of this hidden field is filled with the comma separated list)
The next STP has a parameter called "multi_myfield".
In the second STP I use the macro facility to determine whether the selection the user has made needs to be reflected in the where statement of my data step.
%let mywhere =;
%if "&myfield" ne "all" %then %do;
%let mywhere = "where field_x in (&multi_myfield)";
This works perfectly.
Even if I have th HTML select field as a multiselect and klick on each option (so that I have the selection of all options) this is faster than selecting the option "all". And no, my data set does not have an index.
So my question is if anybody has an idea why the performace is worse now.
Is there maybe a hidden process when two STPs exchance the HTML multiselect field? And maybe that takes time?
06-27-2011 10:58 AM
This is really a question for Tech Support. They have the resources to look at both your stored processes, all the intermediate HTML that's created and to look at the configuration of your servers and the client applications you're using and help you come up with the answer that best suits your needs.
06-28-2011 07:02 AM
I don't know if it will help you but we did it in an other way:
%macro mvexist(mvarname); %global &mvarname wherestring; proc sql noprint; select count(*) into :mvcntval from sashelp.vmacro where name = "%upcase(&mvarname)"; quit; %if &mvcntval = 0 %then %let &mvarname=; %else %do; proc sql noprint; select count(*) into :mvcntval from sashelp.vmacro where name = "%upcase(&mvarname.0)"; quit; %if &mvcntval = 1 %then %do i = 1 %to &&&mvarname.0; %if &i > 1 %then %let &mvarname = &&&mvarname&&&mvarname&i; %if &i < &&&mvarname.0 %then %let &mvarname = &&&mvarname,; %end; %end; %mend; %mvexist(&sel_entity); where Entity in ("%sysfunc(tranwrd(%bquote(&sel_entity),%bquote(,),%bquote(",")))");