Developers

A forum for collaboration, Q&A, and knowledge sharing on SAS and open source integration
BookmarkSubscribeRSS Feed
subrat1
Fluorite | Level 6

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||'');

1 REPLY 1
LinusH
Tourmaline | Level 20

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 926 views
  • 0 likes
  • 2 in conversation