/******************************************************************************/ /* 51MA01 Migration script for the CI hot fix defect */ /* */ /* S0499388: SDM RESPONSES ARE NOT AVAILABLE IN CI STUDIO VIA */ /* RESPONSE CHANNEL CODES */ /* */ /* Prior to executing this script please make the following changes: */ /* a) Supply the complete libname statement to rptlib macro variable */ /* (libname statement where the CI Common model is installed) */ /* */ /* Before executing the Step 3: append the option REREAD_EXPOSURE=YES */ /* towards the end of libname statement. This setup is mandatory while */ /* using Modify procedure to update the data base tables */ /* */ /* b) Supply the Response_History table name */ /* if the customer site has more than one Response_History table i.e. */ /* one table per subject, then provide the table name for one subject at a */ /* time and execute this script. */ /* */ /* Repeat this process to update all subject specific */ /* response history tables */ /* */ /******************************************************************************/ %let rptlib=<>; %let rh_tbl=<>; %let crcr=CI_RESPONSE_CHANNEL_RESPONSE; %let cp_tbl=CI_CELL_PACKAGE; %let cpt_tbl=CI_CELL_PACKAGE_TREATMENT_CNT; %let crxcp_tbl=CI_RESPONSE_X_CELL_PACKAGE; %let data_tbl=work.rh_subset; %let s_rsp_cd=_CV; &rptlib; %let libnm = %upcase(%qscan(&rptlib.,2)); /*******************************************************************************/ /* PART I: Execute the proc sql statement below to update the response history */ /* table with the correct response code generated with in SMC CI Plug-in*/ /*******************************************************************************/ proc sql; /* Update the RESPONSE_CD in the Response_History table by joining it with the CI_RESPONSE_CHANNEL_RESPONSE table */ update &libnm..&rh_tbl. rh set response_cd=(select response_cd from &libnm..&crcr. cr where rh.response_channel_cd='_EM' and rh.response_channel_cd=cr.response_channel_cd and UPCASE(rh.response_cd)=UPCASE(cr.response_channel_response_cd) and cr.response_cd is not null) where exists (select 1 from &libnm..&crcr. cr where rh.response_channel_cd='_EM' and rh.response_channel_cd=cr.response_channel_cd and UPCASE(rh.response_cd)=UPCASE(cr.response_channel_response_cd) and cr.response_cd is not null); /* Create a subset from the Response_History table to re-process the response counts for all the records from SDM client */ create table &data_tbl. as select * from &libnm..&rh_tbl. where RESPONSE_CHANNEL_CD like '_EM'; quit; /******************************************************************************/ /* PART II: Compile and execute the macro given below to refresh the */ /* response counts in cell_package and cell_package_treatment_cnt */ /* table after successfully executing the PART I SQL Stmts */ /******************************************************************************/ /* Add reread_exposure=yes as the last option for all data bases except SASSPDS */ %let rptlib=<> reread_exposure=yes; &rptlib; %macro marspcnt(cp_tbl=, cpt_tbl=, crxcp_tbl=, data_tbl=,rptlib=, s_rsp_cd=); %let pdttm=%sysfunc(datetime(),datetime20); %let dbengine = %upcase(%qscan(&rptlib.,3,%str( ) %str(%"))); /* Extract library name for later use */ %let libnm = %upcase(%qscan(&rptlib.,2)); /* Step 1: Create work table with the Successful Response Flag */ proc sort data=&data_tbl. out=rh_in_tbl (drop=inferred_response_flg external_response_info_id1 external_response_info_id2); by response_cd cell_package_sk; run; proc sort data=&libnm..&crxcp_tbl. out=work.ci_rxcp (drop=estimated_response_rt); by response_cd cell_package_sk; run; data work.resp_intbl_rtc; merge rh_in_tbl (in=intbl) work.ci_rxcp (in=rxcp); by response_cd cell_package_sk; if intbl; run; %if &SYSERR.> 4 %then %do; %let MAError =773; %let MAMsg = Macro[&sysmacroname.]. Create Table error while joining with Response Type code SYSERR[&SYSERR]. Check the log for details; %return; %end; /* Step 2: Find TOTAL_RESPONSE_COUNT at cell_package level */ proc sort data=work.resp_intbl_rtc; by cell_package_sk; run; proc summary data=work.resp_intbl_rtc nway; by cell_package_sk; output out=work.total_resp (drop=_type_ rename=_freq_=t_resp_cnt); run; %if &SYSERR.> 4 %then %do; %let MAError =774; %let MAMsg = Macro[&sysmacroname.]. Proc Summary error while attempting to count Total Responses SYSERR[&SYSERR.]. Check the log for details; %return; %end; /* Step 3: Find TOTAL_SUCCESSFUL_RESPONSE_COUNT at cell_package level */ proc summary data=work.resp_intbl_rtc (where=(RESPONSE_TYPE_CD="&s_rsp_cd")) nway; by cell_package_sk; output out=work.success_resp (drop=_type_ rename=_freq_=s_resp_cnt); run; %if &SYSERR.> 4 %then %do; %let MAError =775; %let MAMsg = Macro[&sysmacroname.]. Proc Summary error while attempting to count Successfull Responses SYSERR[&SYSERR.]. Check the log for details; %return; %end; /* Step 4: Merge both Total_RESP and Success_RESP so that using single MODIFY stmt both the columns will be updated */ data resp_count; merge work.total_resp (in=tr) work.success_resp (in=sr); by cell_package_sk; if tr; /* Include both success and failed response record counts */ run; /* Step 5: Update CI_CELL_PACKAGE table with response counts */ data &libnm..&cp_tbl.; modify &libnm..&cp_tbl. work.resp_count; by cell_package_sk; select (_IORC_); when (%SYSRC(_SOK)) do; TOTAL_RESPONSE_CNT=SUM(0, T_RESP_CNT); TOTAL_SUCCESSFUL_RESPONSE_CNT=SUM(0, S_RESP_CNT); processed_dttm="&pdttm"dt; replace; end; otherwise do; stop; end; end; run; /* Exit the process if any error */ %if %symexist(SYSDBRC) %then %do; %if &SYSDBRC. ^= 0 %then %do; %let SYSCC = 5; %let MAError = 776; %let MAMsg = Macro[&sysmacroname.]. SYSDBRC=[&SYSDBRC] and SYSDBMSG=[&SYSDBMSG]; %return; %end; %end; %else %if &SYSERR.> 4 %then %do; %let MAError =776; %let MAMsg = Macro[&sysmacroname.]. Data Modify stmt error while attempting to Update Response counts in Cell_Pacakge table SYSERR[&SYSERR.]. Check the log for details; %return; %end; /* DBKEY is not supported for SPDS so create an index and refer the index name in KEY= option */ %if &dbengine. eq SASSPDS %then %do; proc datasets library=&libnm. memtype=data nolist; modify &cpt_tbl.; index create cell_package_sk; quit; %end; data &libnm..&cpt_tbl.; set work.resp_count; /* Load all the matching records one by one (by the dbkey variables) from master table to update the _CNT column with the single matching record from transaction table */ do until (_IORC_=%SYSRC(_DSENOM)); %if &dbengine. ne SASSPDS %then %do; modify &libnm..&cpt_tbl. (dbkey=cell_package_sk cntllev=rec) key=dbkey; %end; %else %if &dbengine. eq SASSPDS %then %do; modify &libnm..&cpt_tbl. (cntllev=rec) key=cell_package_sk; %end; select (_IORC_); when (%SYSRC(_SOK)) do; TOTAL_RESPONSE_CNT=SUM(0, T_RESP_CNT); TOTAL_SUCCESSFUL_RESPONSE_CNT=SUM(0, S_RESP_CNT); processed_dttm="&pdttm"dt; replace; end; when (%SYSRC(_DSENOM)) do; _error_ = 0; end; otherwise do; stop; end; end; end; run; %if &dbengine. eq SASSPDS %then %do; proc datasets library=&libnm. memtype=data nolist; modify &cpt_tbl; index delete cell_package_sk; quit; %end; /* Exit the process if any error */ %if %symexist(SYSDBRC) %then %do; %if &SYSDBRC. ^= 0 %then %do; %let SYSCC = 5; %let MAError = 777; %let MAMsg = Macro[&sysmacroname.]. SYSDBRC=[&SYSDBRC] and SYSDBMSG=[&SYSDBMSG]; %return; %end; %end; %else %if &SYSERR.> 4 %then %do; %let MAError =777; %let MAMsg = Macro[&sysmacroname.]. Data Modify stmt error while attempting to Update Successfull Response counts in Cell_Pacakge table SYSERR[&SYSERR.]. Check the log for details; %return; %end; %mend; options mlogic mprint; %marspcnt(cp_tbl=&cp_tbl., cpt_tbl=&cpt_tbl., crxcp_tbl=&crxcp_tbl., data_tbl=&data_tbl.,rptlib=&rptlib., s_rsp_cd=&s_rsp_cd.);