BookmarkSubscribeRSS Feed
Eva
Quartz | Level 8 Eva
Quartz | Level 8

Dear all,

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">

     <option>all</option>

     <option>first option</option>

     <option>second option</option>

     <option>third option</option>

</select>

When the user says "submit" (via an HTML button" a javascripts checks out which option has been selected by the user and writes these as a comma separated list in another input field:

<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)";

%end;

This works perfectly.

The problem is that the performance is not good when the user selects "all". I had the same thing befor without the multiselect. I.e. the same HTML select as a simple drop down menue. and without the javascript. And when the user select "all" here, the performance is much better.

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.

The Javascript which writes the comma separated list is fast. The problem can't be here.

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?

Best regards,

Eva

2 REPLIES 2
Cynthia_sas
SAS Super FREQ

Hi:

  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.

cynthia

Filipvdr
Pyrite | Level 9

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(",")))");

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 857 views
  • 0 likes
  • 3 in conversation