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)';
--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||'');
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.