Your SAS programs, embedded in web apps and elsewhere

help to write a sas code in SAS store possess

Reply
Frequent Contributor
Posts: 76

help to write a sas code in SAS store possess

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    

M

F

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

Frequent Contributor
Posts: 76

help to write a sas code in SAS store possess

Plsease help me to coding this program

Regards,

Ashwini

SAS Super FREQ
Posts: 8,744

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

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

Ask a Question
Discussion stats
  • 2 replies
  • 291 views
  • 0 likes
  • 2 in conversation