how to create drop down in storeprocess

[Ashwini]

I  have a sas datasetcustomer detail like

Customerno   Customerid  customername  productname productcode  porchdate 

CN111           111                RAM               FREEZE         PD1              09JAN2012

CN112           112                HARI              TV                   PD2             10JAN2012

CN113           113                GOPAL           FAN                PD3             04JAN2012

CN111           111                RAM               TV                   PD2             12JAN2012

CN112            112                HARI              FREEZE          PD1            13JAN2012

CN113            113                GOPAL           TV                   PD2             15JAN2012

Productcode:-PDI is FR

                       PD2 is T

                      PD3 is  FN

I have to create a stored process .In this store process  I have to create 3 dropdown selection like Customername ,Productname, productcode.

1. If I     select customername RAM  and TRUE .

      It show below detail like

Customerno   Customerid  customername  productname productcode  porchdate 

CN111           111                RAM               TV                   PD2             12JAN2012

CN111           111                RAM               FREEZE         PD1              09JAN2012

2. If  select customername HARI and TRUE

              Customerno     CN112

Then it show like

Customerno   Customerid  customername  productname productcode  porchdate

CN112           112                HARI              TV                  PD2              10JAN2012

CN112            112                HARI              FREEZE          PD1            13JAN2012

3. If  select productcode FR only it show

Customerno   Customerid  customername  productname productcode  porchdate

CN111           111                RAM               FREEZE         PD1              09JAN2012

CN112            112                HARI              FREEZE          PD1            13JAN2012

If we select customername  HARI and TRUE

                    Customerno       CN112

                   Productcode       FR

It show like

Customerno   Customerid  customername  productname productcode  porchdate

CN112            112                HARI              FREEZE          PD1            13JAN2012                   

As per mention logic how we create drop down selection instored process .using sas macro, base sas and sas sql .Kindly help me .

Thanks and Regards,

Ashwini

[Cynthia_sas]

Hi:

  This question might get more feedback if it was posted in the Stored Process forum, instead of the SAS Macro forum. Stored process prompts do come from the server to your program as SAS Macro variables, so I understand why you might have chosen this forum to post your question,  but the process by which you create dropdown choices is more a function of  how you define the prompts in the metadata, than how you use macro variables. You can create your code, test it with macro variables and then manually register the stored process metadata (including prompts) using SAS Management Console, or you can create and test your program or project in SAS Enterprise Guide and then use the Stored Process Wizard, in EG to create the stored process and register the metadata (including the prompts).

  The process of converting a regular SAS program into a stored process is outlined in one of my older papers (2008) -- which was originally written for SAS 9: http://www2.sas.com/proceedings/forum2008/024-2008.pdf

  Although this is an older paper, the basic conversion steps are the same. The types of prompts that are available to you in SAS 9.2 and 9.3 versions of the BI Platform are different. For example, cascading prompts were not available in earlier versions of SAS, but became available in SAS 9.2.

  The stored process documentation is very good and there are a lot of papers from SAS user groups that you can find, if you search the Internet that talk about stored process features and how to implement stored processes. The steps that you have to go through to register a stored process and define your prompts are many and probably too many to post here. There are a few contributors to the Stored Process forums who maintain blogs about the BI Platform. Either the documentation or the user group papers or the blogs would be a better resource, then asking for someone to outline the registration and conversion steps for you on the forums.

  The first thing you need, however, is a working SAS program or EG project that successfully creates the report you want. So I would suggest that you start by trying to get a program or project working first and then work to convert that working program or project into a stored process. By the time you have a working SAS program or working EG project, you'll also have some idea of the data, the variable names, how your code is going to look, where you can use macro variables and how you're going to register the stored process (either manually using SAS Management Console or using SAS EG Stored Process Wizard).

cynthia

[Ashwini]

Dear Cynthia,

Thanks for your kind information.

Kindly help to write code using sas macro , base sas .

Regards,

Ashwini

[Cynthia_sas]

Hi:

  Consider this SAS program that creates an ODS HTML file on my C drive:

*** 1) Starting SAS Program;

ods html file='c:\temp\myreport.html';

proc print data=sashelp.class;

  where age=12 and sex = "M";

run;

ods html close;

It has hardcoded values for my selection criteria (WHERE statement) for AGE=12 and SEX="M";

Next, using Macro variables, I test this second version of the program using %LET statements to create some macro variables for testing:

*** 2) Working SAS program with Macro variables;

%let age=12;

%let gender=M;

ods html file='c:\temp\myreport.html';

proc print data=sashelp.class;

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

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

run;

ods html close;

Finally, once I have thoroughly tested my program, I am ready to turn the code into a stored process. To do that, I must remove the %LET statements and the hardcoded ODS HTML statements. In place of the %LET statements, I use the %GLOBAL statement to declare that &AGE and &GENDER will be GLOBAL macro variables (because prompt choices sent from the Platform are sent to your program as GLOBAL macro variables). I also need to add the %STPBEGIN; and %STPEND; macro calls to the program (in place of the hard-coded ODS statements) -- because these are the actual calls that will start the "capture" of output to my BI client application.

The final version of the program is shown below

*** 3) program turned into stored process;

%global age gender;

*ProcessBody;

%stpbegin;

proc print data=sashelp.class;

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

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

run;

%stpend;

But, before I can run this stored process, from a BI client application, I first have to register the stored process and define the prompts (AGE and GENDER) either in SAS Management Console or in SAS Enterprise Guide.

There are many user group papers and documentation examples of registering your stored process and defining the prompts. To go into the process of registering here would be too lengthy an answer.

If you notice, the differences between program #2 and program #3 are all in the "wrapper" code -- the basic PROC PRINT did not change between #2 and #3. If you needed more complex logic or needed to vary something about the program based on prompt choices, then that would point to more complex macro coding techniques.

The SAS code needed to use prompt values can be trivially simple or can be more complex, but at this point, my recommendation is that you start simple and explore more about stored processes and SAS Macro programming before you continue.

cynthia