Your SAS programs, embedded in web apps and elsewhere

Create a stored processes including parameter

Reply
Contributor
Posts: 47

Create a stored processes including parameter

I want to create stored process in SAS Enterpriseguide for the following oracle sql code, kindly tell me how to write stored process for it?

here there are two parameter :1)start year   2)end year

 

create or replace
PROCEDURE AUTO_PREMIUM_INDICATION
(
  START_YEAR IN NUMBER 
, END_YAER IN NUMBER 
) AS
  SOURCE_SYSTEM VARCHAR(500) := '(2,3,4,14,15)';
POLICY_TYPE VARCHAR(500):= '(1,15)';

BEGIN

 
    --STEP1: GET COVERAGE INFO   
    --DELETE IF TABLE EXIST
    DELOBJECT ('tcoverages_summary','TABLE');  
   
    EXECUTE IMMEDIATE ('create table tcoverages_summary as
    SELECT  /*+ PARALLEL */
          POLICY_TYPE_SK,
            source_system_sk,
            ACTUAL_DT,
            EXTRACT(YEAR FROM ACTUAL_DT) as  YEAR_CT,
            EXTRACT(MONTH FROM ACTUAL_DT) as MONTH_CT,
            INTCOV_SK,
            policy_sk,
            item_attribute_sk,
            --BROKER_NM,
            EARNED_XPSR_AM,
            WRITTEN_XPSR_AM,
            MTD_WRITTEN_PRM_AM,
            EARNED_PRM_AM           
    FROM                 TCOVERAGES_SUMMARY@MISP.AIG.COM T1
    LEFT JOIN
            Tdate@MISP.AIG.COM T2
    on
            (T1.MONTH_SK = T2.DATE_SK)
    /*LEFT JOIN
    --        TBROKER_DIMENSION@MISP.AIG.COM T3
    ON
            T3.BROKER_SK = T1.bROKER_SK*/
    WHERE   POLICY_TYPE_SK IN '|| POLICY_TYPE ||' and
            source_system_sk NOT  IN '|| SOURCE_SYSTEM ||' AND
            SNAPSHOT_TYPE_IN = 0 AND
            EXTRACT(YEAR FROM ACTUAL_DT)>= '||START_YEAR||' AND
            EXTRACT(YEAR FROM ACTUAL_DT)<= '||END_YAER||'');

Super User
Posts: 5,260

Re: Create a stored processes including parameter

The whole set up seems a bit awkward. Creating a SAS Stored Process that creates/calls a Stored Procedure, that does...what?

Elaborate about the use case so we can can give adequate directions.

And if we look from the other direction, what do you know about stored process, and not? What have you tried so far?

Perhaps start up with a simple "hello world" example, and take it from there...

Data never sleeps
Ask a Question
Discussion stats
  • 1 reply
  • 170 views
  • 0 likes
  • 2 in conversation