topic how to create drop down in storeprocess in SAS Programming

how to create drop down in storeprocess

Ashwini — Tue, 07 Feb 2012 02:33:03 GMT

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.

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

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

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

how to create drop down in storeprocess

Cynthia_sas — Tue, 07 Feb 2012 04:04:56 GMT

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

how to create drop down in storeprocess

Ashwini — Tue, 07 Feb 2012 05:34:29 GMT

Dear Cynthia,

Thanks for your kind information.

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

Regards,

Ashwini

Re: how to create drop down in storeprocess

Cynthia_sas — Tue, 07 Feb 2012 23:18:20 GMT

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