SAS and proc SQL example

Post date: Oct 10, 2013 10:53:25 PM

/* ################################################################################################ */ /* ################################## INITIALIZATION ####################################### */ /* ################################################################################################ */ /* Global definition used for the whole code */ %include "/mine/fraud/production/sas/procsql/oracle.sas"; %include "/vol2/home/kittipat/SAS_code/2013/botlib/botsaslib.sas"; libname dirdata "/vol1/mine/fraud/kittipat/2013/sfs_fraud_perf"; libname dircode "/vol2/home/kittipat/SAS_code/2013/sfs_fraud_perf/"; * =========================================================================; /* #################################################################################### */ /* ==================================================================================== */ %macro get_workflow_and_rules(); %let database=DW; %let table_out = dirdata.workflows_rules; %let csvOut = "/vol2/home/kittipat/SAS_code/2013/sfs_fraud_perf/workflow_rules.csv"; %connect(&database); create table &table_out as select * from connection to oracle ( select to_char(sfs_wf.CUSTOMER_ID,'99999999999999999999999999999999') as seller_id ,to_char(sfs_wf.WORKFLOW_ID,'9999999999999999999999999999999') as workflow_id ,sfs_wf.REGION_ID ,sfs_wf.MARKETPLACE_ID ,sfs_wf.WORKFLOW_TYPE ,sfs_wf.CREATION_DATE ,sfs_action.ACTION_TYPE ,sfs_action.RULE_ID ,bfs.ACTION_TYPE as status_final ,bfs.MO ,bfs.ENFORCEMENT_DATE from siva_ddl.sfs_event_workflows sfs_wf left join siva_ddl.sfs_actions sfs_action on sfs_wf.WORKFLOW_ID = sfs_action.WORKFLOW_ID left join siva_ddl.blocked_frauded_sellers bfs on sfs_wf.CUSTOMER_ID = bfs.MERCHANT_CUSTOMER_ID where sfs_wf.CREATION_DATE >= sysdate - 2 and sfs_wf.CREATION_DATE < sysdate - 1 and sfs_wf.MARKETPLACE_ID = 1 and sfs_wf.REGION_ID = 1 and sfs_wf.WORKFLOW_TYPE = 'FraudSeller' order by sfs_wf.CUSTOMER_ID, sfs_wf.WORKFLOW_ID, sfs_wf.MARKETPLACE_ID, sfs_action.RULE_ID ); disconnect from oracle; %export2csv(&table_out, &csvOut); %mend get_workflow_and_rules; %get_workflow_and_rules();