topic Re: help to write a sas code in SAS store possess in Developers

help to write a sas code in SAS store possess

Ashwini — Sun, 26 Feb 2012 07:10:17 GMT

I have a data set like EMP_Detail.

Employe_ID Emp_Country Company Department Section Job_Title Gender Employee_Hire_Date

120101 Australia Orion Australia Sales Management Sales Management Director Male 1-Jul-99

120102 Australia Orion Australia Sales Management Sales Management Sales Manager Male 1-Jun-85

120103 Australia Orion Australia Sales Management Sales Management Sales Manager Male 1-Jan-70

120104 Australia Orion Australia Administration Administration Administration Manager Female 1-Jan-77

120105 Australia Orion Australia Administration Administration Secretary I Female 1-May-95

120106 Australia Orion Australia Administration Administration Office Assistant II Male 1-Jan-70

120107 Australia Orion Australia Administration Administration Office Assistant III Female 1-Feb-70

120108 Australia Orion Australia Administration Goods Entrance Warehouse Assistant II Female 1-Aug-02

120109 Australia Orion Australia Administration Goods Entrance Warehouse Assistant I Female 1-Oct-02

120110 Australia Orion Australia Administration Goods Entrance Warehouse Assistant III Male 1-Nov-75

120111 Australia Orion Australia Administration Security Security Guard II Male 1-Nov-70

120112 Australia Orion Australia Administration Security Security Guard I Female 1-Jul-86

120113 Australia Orion Australia Administration Security Security Guard II Female 1-Jan-70

120114 Australia Orion Australia Administration Security Security Manager Female 1-Jan-70

120115 Australia Orion Australia Administration Service Center Service Assistant I Male 1-Aug-01

120116 Australia Orion Australia Administration Service Center Service Assistant II Male 1-Feb-76

120117 Australia Orion Australia Engineering Engineering Cabinet Maker III Male 1-Apr-82

120118 Australia Orion Australia Engineering Engineering Cabinet Maker II Male 1-Jul-80

120119 Australia Orion Australia Engineering Engineering Electrician IV Male 1-Jan-94

120120 Australia Orion Australia Engineering Engineering Electrician II Female 1-Jan-70

120121 Australia Orion Australia Sales Sales Sales Rep. II Female 1-Jan-70

120122 Australia Orion Australia Sales Sales Sales Rep. II Female 1-Jul-74

120123 Australia Orion Australia Sales Sales Sales Rep. I Female 1-Oct-81

120124 Australia Orion Australia Sales Sales Sales Rep. I Male 1-Mar-75

120125 Australia Orion Australia Sales Sales Sales Rep. IV Male 1-Mar-75

120194 Australia Orion Australia Sales Sales Trainee Male 1-Feb-01

120195 Australia Orion Australia Sales Sales Trainee Female 1-Jul-02

120196 Australia Orion Australia Sales Sales Trainee Female 1-Jan-99

120197 Australia Orion Australia Sales Sales Trainee Female 1-Jan-99

120198 Australia Orion Australia Sales Sales Sales Rep. III Female 1-Dec-02

120199 Belgium Orion Belgium Administration Administration Office Assistant I Female 1-Jan-99

120200 Belgium Orion Belgium Administration Administration Office Assistant II Female 1-Jan-99

I have to make below like tabs in storeprocess .

Employee_Hire_Date

FROMDATE

	01JAN99

TODATE

29JUL99

SELECT Emp_Country

Australia

Belgium

ALL

NONE

Select Gender

NONE

Kindly help me how to write a query using base sas and sas macro .So that i have got above tab in front end of storepeocees.

Thanks in advance ,

Regards,

Ashwini

help to write a sas code in SAS store possess

Ashwini — Sun, 26 Feb 2012 11:15:24 GMT

Plsease help me to coding this program

Regards,

Ashwini

Re: help to write a sas code in SAS store possess

Cynthia_sas — Sun, 26 Feb 2012 16:28:36 GMT

Hi:

I don't understand what you mean when you say that you want to "make a tab" in the stored process. How the stored process output appears will depend on the client application that you use to execute the stored process and receive the results.

It looks to me like you are showing the PROMPTs that the end user will see. For example, your box that shows "Select Emp_Country" looks like a selection box, such as might be displayed when a user requests a stored process.

In order to create a stored process, you need to start with a working SAS program. As explained in this previous forum posting:
http://communities.sas.com/message/115182#115182

For example, if I want a report on SASHELP.CLASS for all males where AGE=12 and I have this working SAS program:

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

proc print data=sashelp.class;
where age=12 and sex = "M";
run;

ods html close;

Let's say I want to turn this program into a stored process where someone can select from any age and any gender. I will need to define two prompts for the WHERE statement. The steps to define prompts can be found in the stored process developer's guide. In addition, there are many examples of creating stored process prompts from user group and SAS Global Forum papers.

But, to continue, let's say that I create the modified program below and register 2 prompts for this stored process: &AGE and &GENDER. This is what my new program looks like:

%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;

Notice how the only pieces of the program that change are the "wrapper code" for %STPBEGIN/%STPEND and the %GLOBAL statement and the altered WHERE statement.

There is nothing in this program that will create "tabs" -- it is the prompting interface where you can define prompt groups and each prompt group could appear in a different tab. Also, in Web Report Studio or the Information Delivery Portal, reports or pages can be created with tabs -- but again, that happens through the interface and NOT in the stored process program, itself.

There is nothing in your data or in a macro program that will "make a tab" in the stored process. I would recommend that you start with a working SAS program, generate a simple report from your data and then modify your program, as shown using macro variables for selection criteria.

If you need to do parameter validation you can build some validation in the prompt definition process (whether a prompt is required, what valid values are, whether a prompt is defined in a group, etc). More advanced validation might require some advanced macro programming using %IF/%ELSE coding. But so far, nothing that you've explained points to more advanced macro programming. But let's say, for some reason that you want to check the values of &AGE in a macro program. Then you could write a macro called CKAGE that would print out an error message if the age specified was GE 17. (Although, this type of validation could be better done through the MIN and MAX settings in the prompting interface.)

But, anyway, here's the %CKAGE macro definition. Then to use this macro program in your stored process, you might have something like this. In this example, the %CKAGE macro program is a session compiled macro program. (In a true production environment, this macro would probably be an AUTOCALL macro program.)


%macro ckage;
%if &age GE 17 or &age le 10 %then %do;
     data errmsg;
       errmsg = 'Age can only be between 11 and 16';
       output;
       errmsg = "You specified an age value of &age";
       output;
     run;

     proc print data=errmsg noobs;
       var errmsg / style(data)={foreground=red font_size=20pt};
     run;
%end;
%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";
     run;
%end;
%mend ckage;

*ProcessBody;

%stpbegin;

%ckage;

%stpend;

   However, while the example does show a SAS program being generated by a macro program as a stored process, there is nothing here that will "make a tab". But to help you with a start on your SAS program, here is one way you could write a program using the above data. This program assumes that you have registered prompts called &FROMDATE, &TODATE, &COUNTRY and &GENDER:

%global fromdate todate country gender;

*ProcessBody;

%stpbegin;
proc print data=orion.employees;
    var Employee_ID Emp_Country Company Department
        Section Job_Title Gender Employee_Hire_Date;
    where Employee_Hire_Date between "&fromdate"d and "&todate"d
          and
          Emp_Country = "&Country"
          and
          Gender = "&Gender";
run;
%stpend;

   The steps for how to register a prompt using the prompt interface are well documented and are too lengthy to post here. I'd suggest that you look at the Stored Process Developer's Guide and/or work with Tech Support on the issue of "tabs".

cynthia