Previous Page | Next Page

Add-in Code Generators and the Process Library

Add-In Code Generator Technical Reference

An add-in code generator is a SAS Metadata API application that is registered in SAS/Warehouse Administrator to dynamically generate the code for a process step. These applications enable you to access and use relevant metadata to drive your table processes.

After an add-in code generator is written and stored in the proper SAS library, you can specify it as a user-written routine on the Source Code tab for a given process step. For example, in Source Code Tab With Add-in Code Generator Specified, an add-in code generator named DATASTEP.MAIN.SCL has been specified for a process.

Source Code Tab With Add-in Code Generator Specified

[Source Code Tab With Add-in Code Generator Specified]

The difference between specifying a source entry for your source code and specifying an SCL entry is that the source entry gets included in the Job that is created, while the SCL entry you specify gets run during the creation of that Job. The SCL entry you supply should create the source code that will actually be run when the Job is submitted.

The add-in code generator that you specify is passed parameters that make it context-sensitive and metadata-aware. The code you provide can use the Metadata API to dynamically create the code for that process step.

For details about the Metadata API, see SAS/Warehouse Administrator Metadata API Reference, Release 2.3.


Requirements

There are certain requirements you must adhere to in order to successfully take advantage of an add-in code generator. When using this feature you are no longer only providing code to be run to create your table, you are actually integrating your API application with SAS/Warehouse Administrator, and that integration must follow the rules described in this section.

SAS/Warehouse Administrator will pass these parameters, in the following order, to your application:

ID

is the metadata ID of the process object associated with the Process Editor object you have selected.

I_API

is an initialized instance of the Metadata API object. This instance will already be initialized with the primary and possibly secondary repositories, as appropriate for the object selected.

Note:   The called application should not terminate the API object passed. It will be terminated when control is returned to SAS/Warehouse Administrator.  [cautionend]

L_PARMS

is an empty SCL list that can be used to pass return code information from the called application back to SAS/Warehouse Administrator.

The L_PARMS list is designed to contain named items that pass information back and forth between SAS/Warehouse Administrator and the called application. The only named item in the list that SAS/Warehouse Administrator currently processes is RC, which is expected to be a list. If you choose to pass return code information back to SAS/Warehouse Administrator (which will produce a pop-up error message), your application should create a list with two named items, 'RC' and 'MSG'.

The 'RC' item is a numeric return code, the 'MSG' item is a character string that will become the message in the pop-up error message. Add this list you create to L_PARMS as the named item 'RC' to return the value to SAS/Warehouse Administrator. A non-zero RC value will signal an error to SAS/Warehouse Administrator.

The entry statement for the called add-in code generator should be coded as follows:

entry id $ 26 i_api 8 l_parms 8;

SAS/Warehouse Administrator uses the preview buffer to store generated code for all the process steps in the Process Editor, including access code for input sources and macro definitions between steps. Your SCL code will need to place any code it generates into the preview buffer in order for that code to be saved or submitted for that step.

CAUTION:
Because the preview buffer is shared by the SAS/Warehouse Administrator generation process and your add-in code generation process, it is critical that you take great care in handling the preview buffer.   [cautionend]

You should write to the preview buffer as necessary, but you should not clear it out (thereby deleting any prior generated code). You should not submit the code in the Process Editor (so do not use submit CONTINUE; statements).

SAS/Warehouse Administrator will control whether the generated code should be submitted, saved, or viewed, based on how the code generation process was initiated.


Usage Notes

There are several small differences between API applications run as add-in code generators and API applications run as stand-alone. The add-in generator application will always return blank values in the STEP SOURCE CODE and SOURCE CODE properties of any process queried with the _GET_METADATA_ method. The add-in generator code has been registered to generate code that is supplied by those properties, so it cannot request that information even though it creates that information.

An add-in generator API application has been registered in SAS/Warehouse Administrator to generate code for a process and is assumed to be a secure application. The fact that it is a secure application means that API queries of relevant types will return password information about those types.

The WHSASSTR, WHDBMS, and WHLIBRY types have password information about SAS data sets and DBMS connections respectively. If password information is registered for either type, an API query of that object from an add-in code generator application will return the registered user ID and password information.

If the API application is not a secure application, the passwords will be returned as 'XXXXXXXX'.

For details about the values returned for the WHSASSTR, WHDBMS, and WHLIBRY types, see SAS/Warehouse Administrator Metadata API Reference, Release 2.3.

CAUTION:
Using the add-in code generator feature demands that you be very aware of the effects your API application will have on SAS/Warehouse Administrator.   [cautionend]

A simple failure to compile your application will cause a Program Halt in SAS/Warehouse Administrator. Incorrect entry parameters will cause a Program Halt. Any halts or other errors in your application can adversely affect SAS/Warehouse Administrator. Also, take great care with your handling of the preview buffer (see caution note in preceding section). If your application does take a Program Halt for any reason, you should exit SAS/Warehouse Administrator and re-enter it.


Sample Add-In Code Generator

******************************************************************/                                                                    
/*                                                                */                                                                    
/*                                                                */                                                                    
/*              S A S   S  A M P L E   L I B R A R Y              */                                                                    
/*                                                                */                                                                    
/*                                                                */                                                                    
/*   TITLE: Data Step Mapping                                     */                                                                    
/* VERSION: 2.0.0                                                 */                                                                    
/* PRODUCT: SAS/Warehouse Administrator                           */                                                                    
/*  SYSTEM: Windows 95, Windows NT, Unix                          */                                                                    
/*    DESC: This program demonstrates how to develop a            */                                                                    
/*          user-written one-to-one data mapping process by       */                                                                    
/*          extracting information from existing metadata.  It    */                                                                    
/*          uses SAS DATA step statements for the column mapping  */                                                                    
/*          to load a detail or data table rather than the        */                                                                    
/*          PROC SQL code that SAS/WA would generate.             */                                         
*/                         
/*                                                                */                                                                    
/*          This add-in retrieves column metadata of the output   */                                                                    
/*          table to do a straight mapping, which selects input   */                                                                    
/*          columns based on the output table metadata.  The      */                                                                    
/*          column names of the output table remain the same as   */                                                                    
/*          the ones mapped in the input table.                   */                                                                    
/*                                                                */                                                                    
/*          This program expects to be passed the ID of a WHPRCMAP*/                                                                    
/*          type, the process for a mapping in your warehouse.    */                                                                    
/******************************************************************/                                                                    

                                                                                                                                        
length mpin_lib $ 8;                                                                                                                    
length mpin_data $ 32;                                                                                                                  
length mpout_lib $ 8;                                                                                                                   
length mpout_data mpout_col $ 32                                                                                                        
       mpout_coltyp $ 1                                                                                                                 
       mpout_collen 8                                                                                                                   
       mpout_colfmt mpout_colinfmt $ 20                                                                                                 
       ;                                                                                                                                
length keepvar $ 32;                                                                                                                    
length table_id $ 26                                                                                                                    
       type_id super_type_id $ 8                                                                                                        
       ;                                                                                                                                
                                                                                                                                        
entry id $ 26 i_api 8 l_parms 8;                                                                                                        
                                                                                                                                        
rc = rc;                                                                                                                                
                                                                                                                                        
INIT:                                                                                                                                   
                                                                                                                                        
   /* l_meta is used later to store metadata of */                                                                                      
   /* output tables from data mapping process   */                                                                                      
                                                                                                                                        
   l_meta = makelist();                                                                                                                 
   rc = insertc(l_meta, id, -1, 'ID');                                                                                                  
                                                                                                                                        
   /* l_var is used later to store column names of the  */                                                                              
   /* interim data set output from data mapping process */                                                                              
                                                                                                                                        
   l_var = makelist();                                                                                                                  
                                                                                                                                        
   /* get metadata of output table of data mapping process */                                                                           
                                                                                                                                        
   l_outtb = makelist();                                                                                                                
   l_meta = insertl(l_meta, l_outtb, -1, 'OUTPUT TABLES');                                                                              
                                                                                                                                        
   call send(i_api, '_GET_METADATA_', l_rc, l_meta);                                                                                    
                                                                                                                                        
   if l_rc ne 0 then do;                                                                                                                
      link MAKERC;                                                                                                                      
      return;                                                                                                                           
   end;                                                                                                                                 
                                                                                                                                        
   if listlen(l_outtb) < 1 then do;                                                                                                     
                                                                                                                                        
       rc = 1000;                                                                                                                       
       rc_msg =                                                                                                                         
      'ERROR: output table metadata missing';                                                                                           
                                                                                                                                        
       link MAKERC;                                                                                                                     
       return;                                                                                                                          
                                                                                                                                        
   end;                                                                                                                                 
                                                                                                                                        
   /* assume only one output table is generated */                                                                                      
                                                                                                                                        
   l_outtbA = getiteml(l_outtb, 1);                                                                                                     
                                                                                                                                        
   /* verify table ID referring to a subtype of WHTBLPRC */                                                                             
                                                                                                                                        
   table_id = getnitemc(l_outtbA, 'ID');                                                                                                
   type_id = scan(table_id, 2, '.');                                                                                                    
   super_type_id = 'WHTBLPRC';                                                                                                          
                                                                                                                                        
   call send(i_api, '_IS_SUBTYPE_OF_', l_rc, type_id,                                                                                   
             super_type_id,  a_subtype);                                                                                                
                                                                                                                                        
   if l_rc ne 0 then do;                                                                                                                
                                                                                                                                        
      link MAKERC;                                                                                                                      
      return;                                                                                                                           
                                                                                                                                        
   end;                                                                                                                                 
                                                                                                                                        
   if not a_subtype then do;                                                                                                            
                                                                                                                                        
      rc = 10001;                                                                                                                       
      rc_msg = 'ERROR: invalid process ID: ' || id ||                                                                                   
          '.  Expecting an intermediate output table process.';                                                                         
      link MAKERC;                                                                                                                      
      return;                                                                                                                           
                                                                                                                                        
   end;   /*  if  */                                                                                                                    
                                                                                                                                        
   /* extract metadata of the name of the output */                                                                                     
   /* table from data mapping process            */                                                                                     
                                                                                                                                        
   l_outtbA = insertc(l_outtbA, ' ', -1, 'TABLE NAME');                                                                                 
                                                                                                                                        
   /* extract metadata of the library that the */                                                                                       
   /* output table resides                     */                                                                                       
                                                                                                                                        
   l_outlib = makelist();                                                                                                               
   l_outtbA = insertl(l_outtbA, l_outlib, -1, 'LIBRARY');                                                                               
                                                                                                                                        
   /* extract metadata of input source that serves   */                                                                                 
   /* as the input table to data mapping process */                                                                                     
                                                                                                                                        
   l_insrc = makelist();                                                                                                                
   l_outtbA = insertl(l_outtbA, l_insrc, -1, 'INPUT SOURCES');                                                                          
                                                                                                                                        
   /* extract metadata of output object */                                                                                              
                                                                                                                                        
   l_outobj = makelist();                                                                                                               
   l_outtbA = insertl(l_outtbA, l_outobj, -1, 'OUTPUT OBJECTS');                                                                        
                                                                                                                                        
   call send(i_api, '_GET_METADATA_', l_rc, l_outtbA);                                                                                  
                                                                                                                                        
   if l_rc ne 0 then do;                                                                                                                
      link MAKERC;                                                                                                                      
      return;                                                                                                                           
   end;                                                                                                                                 
                                                                                                                                        
   *************************************                                                                                                
   * information of the input data set *                                                                                                
   * used by data mapping process      *                                                                                                
   *************************************                                                                                                
   ;                                                                                                                                    
   if listlen(l_insrc) < 1 then do;                                                                                                     
                                                                                                                                        
      rc = 2000;                                                                                                                        
      rc_msg =                                                                                                                          
     'ERROR: input source metadata missing';                                                                                            
                                                                                                                                        
      link MAKERC;                                                                                                                      
      return;                                                                                                                           
                                                                                                                                        
   end;                                                                                                                                 
                                                                                                                                        
   /* one input source is used */                                                                                                       
                                                                                                                                        
   l_insrcA = getiteml(l_insrc, 1);                                                                                                     
                                                                                                                                        
   l_insrcA = insertc(l_insrcA, ' ', -1, 'TABLE NAME');                                                                                 
                                                                                                                                        
   /* get metadata of the library storing the */                                                                                        
   /* input data set for data mapping process */                                                                                        
                                                                                                                                        
   l_inlib = makelist();                                                                                                                
   l_insrcA = insertl(l_insrcA, l_inlib, -1, 'LIBRARY');                                                                                
                                                                                                                                        
   call send(i_api, '_GET_METADATA_', l_rc, l_insrcA);                                                                                  
                                                                                                                                        
   if l_rc ne 0 then do;                                                                                                                
      link MAKERC;                                                                                                                      
      return;                                                                                                                           
   end;                                                                                                                                 
                                                                                                                                        
   mpin_data = getnitemc(l_insrcA, 'TABLE NAME');                                                                                       
                                                                                                                                        
   if mpin_data = _blank_ then do;                                                                                                      
                                                                                                                                        
      rc = 9995;                                                                                                                        
      rc_msg =                                                                                                                          
     'ERROR: mapping input data set name missing';                                                                                      
      link MAKERC;                                                                                                                      
      return;                                                                                                                           
                                                                                                                                        
   end;   /*  if  */                                                                                                                    
                                                                                                                                        
   else do;                                                                                                                             
                                                                                                                                        
      l_inlib = insertc(l_inlib, ' ', -1, 'LIBREF');                                                                                    
                                                                                                                                        
      call send(i_api, '_GET_METADATA_', l_rc, l_inlib);                                                                                
                                                                                                                                        
      if l_rc = 0 then do;                                                                                                              
                                                                                                                                        
         mpin_lib = getnitemc(l_inlib, 'LIBREF');                                                                                       
                                                                                                                                        
         if mpin_lib = _blank_ then do;                                                                                                 
            rc = 9996;                                                                                                                  
            rc_msg =                                                                                                                    
        'ERROR: mapping input libref name missing';                                                                                     
            link MAKERC;                                                                                                                
            return;                                                                                                                     
                                                                                                                                        
         end;   /*  if  */                                                                                                              
                                                                                                                                        
   end;   /*  else  */                                                                                                                  
                                                                                                                                        
   ***************************************                                                                                              
   * information of the interim data set *                                                                                              
   * output from data mapping process    *                                                                                              
   ***************************************                                                                                              
   ;                                                                                                                                    
   /* get the name of the output data set */                                                                                            
                                                                                                                                        
   mpout_data = getnitemc(l_outtbA, 'TABLE NAME');                                                                                      
                                                                                                                                        
   if mpout_data = _blank_ then do;                                                                                                     
                                                                                                                                        
      rc = 8887;                                                                                                                        
      rc_msg =                                                                                                                          
  'ERROR: mapping output data set name missing';                                                                                        
      link MAKERC;                                                                                                                      
      return;                                                                                                                           
                                                                                                                                        
   end;   /*  if  */                                                                                                                    
                                                                                                                                        
   /* get metadata of the libref storing the  */                                                                                        
   /* output data set in data mapping process */                                                                                        
                                                                                                                                        
   l_outlib = insertc(l_outlib, ' ', -1, 'LIBREF');                                                                                     
                                                                                                                                        
   call send(i_api, '_GET_METADATA_', l_rc, l_outlib);                                                                                  
                                                                                                                                        
   if l_rc ne 0 then do;                                                                                                                
      link MAKERC;                                                                                                                      
      return;                                                                                                                           
   end;                                                                                                                                 
                                                                                                                                        
      mpout_lib = getnitemc(l_outlib, 'LIBREF');                                                                                        
                                                                                                                                        
      if mpout_lib = _blank_ then do;                                                                                                   
                                                                                                                                        
         rc = 8888;                                                                                                                     
         rc_msg =                                                                                                                       
        'ERROR: mapping output libref name missing';                                                                                    
         link MAKERC;                                                                                                                   
         return;                                                                                                                        
                                                                                                                                        
      end;   /*  if  */                                                                                                                 
                                                                                                                                        
      submit;                                                                                                                           
         DATA &mpout_lib.&mpout_data;                                                                                                   
      endsubmit;                                                                                                                        
                                                                                                                                        
                                                                                                                                        
   end;   /*  if  */                                                                                                                    
                                                                                                                                        
                                                                                                                                        
   /* get metadata of the output data set columns */                                                                                    
                                                                                                                                        
   if listlen(l_outobj) < 1 then do;                                                                                                    
                                                                                                                                        
      rc = 7773;                                                                                                                        
      rc_msg =                                                                                                                          
     'ERROR: output object metadata missing';                                                                                           
      link MAKERC;                                                                                                                      
      return;                                                                                                                           
                                                                                                                                        
   end;   /*  if  */                                                                                                                    
                                                                                                                                        
   l_outobjA = getiteml(l_outobj, 1);                                                                                                   
                                                                                                                                        
   l_outcol = makelist();                                                                                                               
   l_outobjA = insertl(l_outobjA, l_outcol, -1, 'COLUMNS');                                                                             
                                                                                                                                        
   call send(i_api, '_GET_METADATA_', l_rc, l_outobjA);                                                                                 
                                                                                                                                        
   if l_rc ne 0 then do;                                                                                                                
      link MAKERC;                                                                                                                      
      return;                                                                                                                           
   end;                                                                                                                                 
                                                                                                                                        
   do i = 1 to listlen(l_outcol);                                                                                                       
                                                                                                                                        
      l_outcolA = getiteml(l_outcol, i);                                                                                                
                                                                                                                                        
      l_outcolA = insertc(l_outcolA, ' ', -1, 'NAME');                                                                                  
      l_outcolA = insertc(l_outcolA, ' ', -1, 'TYPE');                                                                                  
      l_outcolA = insertn(l_outcolA, ., -1, 'LENGTH');                                                                                  
      l_outcolA = insertc(l_outcolA, ' ', -1, 'FORMAT');                                                                                
      l_outcolA = insertc(l_outcolA, ' ', -1, 'INFORMAT');                                                                              
                                                                                                                                        
      call send(i_api, '_GET_METADATA_', l_rc, l_outcolA);                                                                              
                                                                                                                                        
      if l_rc ne 0 then do;                                                                                                             
         link MAKERC;                                                                                                                   
         return;                                                                                                                        
      end;                                                                                                                              
                                                                                                                                        
      *-------------------*                                                                                                             
      * column attributes *                                                                                                             
      *-------------------*                                                                                                             
      ;                                                                                                                                 
      /* get column name */                                                                                                             
                                                                                                                                        
      mpout_col =                                                                                                                       
         getnitemc(l_outcolA, 'NAME');                                                                                                  
                                                                                                                                        
      if mpout_col = _blank_ then do;                                                                                                   
                                                                                                                                        
         rc = 7774;                                                                                                                     
         rc_msg =                                                                                                                       
       'ERROR: mapping output column name missing';                                                                                     
         link MAKERC;                                                                                                                   
         return;                                                                                                                        
                                                                                                                                        
      end;   /*  if  */                                                                                                                 
                                                                                                                                        
      /* create an SCL list containing column */                                                                                        
      /* names later referred by the SAS      */                                                                                        
      /* statements in the submit block       */                                                                                        
                                                                                                                                        
      l_var = insertc(l_var, mpout_col, -1);                                                                                            
                                                                                                                                        
      /* get column type */                                                                                                             
                                                                                                                                        
      mpout_coltyp =                                                                                                                    
         getnitemc(l_outcolA, 'TYPE');                                                                                                  
                                                                                                                                        
      if mpout_coltyp = _blank_ then do;                                                                                                
                                                                                                                                        
         rc = 7775;                                                                                                                     
         rc_msg =                                                                                                                       
        'ERROR: mapping output column type missing';                                                                                    
          link MAKERC;                                                                                                                  
          return;                                                                                                                       
                                                                                                                                        
       end;   /*  if  */                                                                                                                
                                                                                                                                        
      /* get column length */                                                                                                           
                                                                                                                                        
      mpout_collen =                                                                                                                    
         getnitemn(l_outcolA, 'LENGTH');                                                                                                
                                                                                                                                        
      if mpout_collen <= 0 then do;                                                                                                     
                                                                                                                                        
         rc = 7776;                                                                                                                     
         rc_msg =                                                                                                                       
       'ERROR: mapping output column length missing';                                                                                   
         link MAKERC;                                                                                                                   
         return;                                                                                                                        
                                                                                                                                        
      end;   /*  if  */                                                                                                                 
                                                                                                                                        
      if mpout_coltyp = 'N' then do;                                                                                                    
                                                                                                                                        
         /* numeric variable */                                                                                                         
                                                                                                                                        
         submit;                                                                                                                        
            LENGTH &mpout_col &mpout_collen;                                                                                            
         endsubmit;                                                                                                                     
                                                                                                                                        
      end;   /*  if  */                                                                                                                 
                                                                                                                                        
      else if mpout_coltyp = 'C' then do;                                                                                               
                                                                                                                                        
         /* character variable */                                                                                                       
                                                                                                                                        
         submit;                                                                                                                        
            LENGTH &mpout_col $&mpout_collen;                                                                                           
         endsubmit;                                                                                                                     
                                                                                                                                        
      end;   /*  else  */                                                                                                               
                                                                                                                                        
      /* get column format */                                                                                                           
                                                                                                                                        
      mpout_colfmt =                                                                                                                    
         getnitemc(l_outcolA,'FORMAT');                                                                                                 
                                                                                                                                        
      if mpout_colfmt ne _blank_ then do;                                                                                               
                                                                                                                                        
      /* If column format is specified by   */                                                                                          
      /* the data warehouse administrator,  */                                                                                          
      /* mpout_colfmt will have a non-blank */                                                                                          
      /* value.                             */                                                                                          
                                                                                                                                        
         submit;                                                                                                                        
            FORMAT &mpout_col &mpout_colfmt;                                                                                            
         endsubmit;                                                                                                                     
                                                                                                                                        
      end;   /*  if  */                                                                                                                 
                                                                                                                                        
      /* get column informat */                                                                                                         
                                                                                                                                        
      mpout_colinfmt =                                                                                                                  
         getnitemc(l_outcolA,'INFORMAT');                                                                                               
                                                                                                                                        
      if mpout_colinfmt ne _blank_ then do;                                                                                             
                                                                                                                                        
      /* If column informat is specified by */                                                                                          
      /* the data warehouse administrator,  */                                                                                          
      /* mpout_colinfmt will have a non-    */                                                                                          
      /* blank value.                       */                                                                                          
                                                                                                                                        
         submit;                                                                                                                        
            INFORMAT &mpout_col &mpout_colinfmt;                                                                                        
         endsubmit;                                                                                                                     
                                                                                                                                        
      end;  /*  if  */                                                                                                                  
                                                                                                                                        
                                                                                                                                        
   end;   /*  do  */                                                                                                                    
                                                                                                                                        
                                                                                                                                        
   submit;                                                                                                                              
      SET &mpin_lib.&mpin_data                                                                                                          
      ( KEEP =                                                                                                                          
   endsubmit;                                                                                                                           
                                                                                                                                        
   do k = 1 to listlen(l_var);                                                                                                          
                                                                                                                                        
      keepvar = getitemc(l_var, k);                                                                                                     
                                                                                                                                        
      submit;                                                                                                                           
         &keepvar                                                                                                                       
      endsubmit;                                                                                                                        
                                                                                                                                        
   end;   /*  do  */                                                                                                                    
                                                                                                                                        
   submit;                                                                                                                              
      );                                                                                                                                
      RUN;                                                                                                                              
   endsubmit;                                                                                                                           
                                                                                                                                        
return;                                                                                                                                 
                                                                                                                                        
MAKERC:                                                                                                                                 
                                                                                                                                        
   passed_rc = rc;                                                                                                                      
                                                                                                                                        
   if listlen(l_rc) = -1 then do;                                                                                                       
                                                                                                                                        
      l_rc = makelist();                                                                                                                
      rc = setnitemn(l_rc, passed_rc, 'RC');                                                                                            
                                                                                                                                        
      if rc_msg ne _blank_ then                                                                                                         
         rc = setnitemc(l_rc, rc_msg, 'MSG');                                                                                           
   end;                                                                                                                                 
                                                                                                                                        
   rc = insertl(l_parms, l_rc, -1, 'RC');                                                                                               
                                                                                                                                        
return;                                                                                                                                 
                                                                                                                                        
TERM:                                                                                                                                   
                                                                                                                                        
   rc = dellist(l_meta, 'Y');                                                                                                           
   rc = dellist(l_var, 'Y');                                                                                                            
                                                                                                                                        
return;                          

Previous Page | Next Page | Top of Page