BookmarkSubscribeRSS Feed
Calcite | Level 5

I have a table like

studentname   subject         mark  grade
A                       ENGLISH     50      B
A                       MATH           70      A
A                       SCIENCE      80      A
B                        ENGLISH     40      B
B                        MATH           30      C
B                        SCIENCE      40      C
D                       ENGLISH     40      C
D                       MATH           70      A
D                       SCIENCE      80      A

I want create a  a prompt in store prosess like


select stname



selct sub





If i select stname A and and SUB  MATH then it show below data
A                       MATH           70      A

if I select stname B and D  and SUB math then it show below data

B                        MATH           30      C
D                       MATH           70      A

I iI select stname ALL and SUB MATH then it show like

A                       MATH           70      A
B                        MATH           30      C
D                       MATH           70      A

If I select stname ONLY A then it show below data
A                       ENGLISH     50      B
A                       MATH           70      A
A                       SCIENCE      80      A

Kindly help me how to write the code show that it easy create a prompt in stored process.




  This question, in various forms, has been asked and answered several times in several different forums.

1) First have a working SAS program that produces each report you want.

2) Then, instead of hard-coded values use macro variables to test that you have covered all the possible macro variables that you could possibly need

3) Possibly use a macro program to introduce %IF/%ELSE conditions into your macro program so that correct code gets generated based on the presence or absence of macro variables that are passed to the macro program when it executes.

4) Test the macro program in #3 with ALL the combinatioins of macro variables

5) Then, modify your code to use %STPBEGIN/%STPEND and introduce %GLOBAL statement for all the macro variables that will come from prompts defined in the metadata.

6) Register the stored process in the metadata and set the prompt definitions (this is necessary so that each client application shows the user a popup window to make choices)

7) Test the stored process in all the client applications where it will be requested.

I generally recommend that you do steps 1,2,3 and 4 outside of a stored process environment. I know that I have posted many different code examples that show just about everything that needs to be done:

Prompts that are created in the prompting interface for a stored process come to your program as SAS GLOBAL MACRO VARIABLES. So if you understand how to use macro variables in your code, then you will be able to easily create a prompt in a stored process.

As I explained before, the steps to register a prompt are lengthy and well documented in the many user group papers and the Stored Process Developers. The place to start is to take a program like this:

%macro ckage;

  %if &age GE 17 or &age le 10 %then %do;

     data errmsg;

       errmsg = 'Age can only be between 11 and 16';


       errmsg = "You specified an age value of &age";




     proc print data=errmsg noobs;

       var errmsg / style(data)={foreground=red font_size=20pt};



  %else %if &age lt 17 %then %do;

     proc print data=sashelp.class;

       title "Showing age &age and for &gender students";

       where age=&AGE and sex = "&gender";



%mend ckage;









(previously posted code}

Save this file as XXX.SAS on your system and then create a stored process from XXX.SAS. You can either use SAS Management Console or use SAS Enterprise Guide to register the stored process and define the prompts. The macro variables that will become prompts are those used in the code... so you will have a prompt for AGE and another prompt for GENDER. You should probably add a %GLOBAL statement above the *ProcessBody; line:



Then, after you register this stored process, you will have to test it. If you define the prompts correctly, you should see a popup window with a prompt for AGE and a prompt for GENDER.

If you continue to need help with defining a stored process or adapting your code, then the appropriate thing to do is to open a track with SAS Tech Support. They will likely ask you for a copy of the working SAS program that you want to convert to a stored process -- so you should attempt steps 1, 2 and 3 -- outside of the stored process world -- before you attempt to write a stored process. Since 1, 2 and 3 steps all involve writing code and using macro variables and writing and using macro programs, learning how macro programs work would be a good place to start. I always recommend this paper as a good introduction into how macro variables and macro programs work.

Good luck!


Super User Tom
Super User

You have done a good job on explaining how to create the code behind the stored process, but I took the question (in its many iterations) to be more about how to get the list of values to use in the prompt.


Where is the documentation for how to create those prompts?

Is it possible that the list of values surfaced to the user is dynamic?  If so how is that done?

How much does it depend on the environment used to call the stored process?



  The Stored Process Developer's Guide has the steps to build a prompt. When you define a prompt, the prompt interface for building the prompt generally has a "Get Values" if, for example, you want to build a static list such as the list for Gender. Or, if you only have a few values, you can choose to type the values into the wizard interface. If you are building dynamic or cascading prompts and you select the dynamic choice for Values Generation method, then you can specify the table from which the values will be populated.

  The OP keeps asking for code. I don't think he/she actually wants code. I think they actually want interface steps. And, those steps are too lengthy to go over here. But there are several blogs, many user-group papers and the Developer's Guide that all have examples of how to build prompts, including how to populate the list of values for the drop-down choices.




Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.


Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 


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
  • 3 replies
  • 3 in conversation