Good SAS example for connecting/uploading to DW and Production

Post date: Nov 25, 2013 7:58:51 PM

This code snippet is a good example for

• Connect to and query from production FRMW1NA
• Upload sas data to our sandbox "risk_mgmt_ddl" in DW and join with other tables in DW
  • When the table kittipat_sellers is uploaded to DW, it's in our sandbox "risk_mgmt_ddl"
  • Therefore, we have to specify risk_mgmt_ddl in front of our table name (i.e. risk_mgmt_ddl.kittipat_sellers) when using it with other tables in DW.
• Take date as a variable for both SQL and filename
  • %let mydate = 01Jun2013; /*For filename*/ %let mydate_sql = %str(%')&mydate.%str(%'); /* We need to add single quote (') for SQL date*/

The code comes from /vol2/home/kittipat/SAS_code/2013/sflc/get_seller_status.sas

/* ################################################################################################ */ /* ################################## 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/sflc/"; libname dircode "/vol2/home/kittipat/SAS_code/2013/sflc/"; * =========================================================================; %macro get_data(mydate); /* Get the workflow_instance_id within a time period */ %let database = frmw1na; %let table_temp = dirdata.temp; /* output table file */ /* %let mydate = 01Jun2013; */ %let mydate_sql = %str(%')&mydate.%str(%'); %let outCSV = "/vol2/home/kittipat/SAS_code/2013/sflc/workflow_instance_id_&mydate..csv"; %connect(&database); create table &table_temp. as select * from connection to oracle ( select workflow_id , cast(customer_id as varchar2(38)) as seller_id , cast(workflow_instance_id as varchar2(38)) as workflow_instance_id , start_date , end_date , creation_date , last_updated_date from sfs_workflows where last_updated_date >= to_date(&mydate_sql.,'ddmonyyyy') and last_updated_date < to_date(&mydate_sql.,'ddmonyyyy')+1 and marketplace_id = 1 ); disconnect from oracle; /*==== write the results to csv file =====*/ %export2csv(&table_temp., &outCSV.); /* ===== Query status from given sellers list ====== */ %let database = DW7; %let seller_status = dirdata.temp2; /* output table file */ %let outCSV = "/vol2/home/kittipat/SAS_code/2013/sflc/seller_status_&mydate..csv"; %load_table(&database., &table_temp., kittipat_sellers); %connect(&database.); create table &seller_status. as select * from connection to oracle ( select slr.* ,bfs.ACTION_TYPE as status_final ,bfs.MO ,bfs.ENFORCEMENT_DATE ,bfs.registration_date ,(bfs.ENFORCEMENT_DATE - bfs.registration_date) as time_to_fraud from risk_mgmt_ddl.kittipat_sellers slr left join siva_ddl.blocked_frauded_sellers bfs on slr.seller_id = bfs.MERCHANT_CUSTOMER_ID where bfs.region_id = 1 and bfs.marketplace_id = 1 ); disconnect from oracle; %drop_table(&database., kittipat_sellers); /*==== write the results to csv file =====*/ %export2csv(&seller_status., &outCSV.); %mend get_data; %get_data(02Jun2013); %get_data(04Jun2013); %get_data(08Jun2013); %get_data(11Jun2013);