/*---------------------------------------------------------------------------- The %WaConvrt (Web Analytics CONVeRT) macro allows you to convert a Web Analytics Web mart to the v5.2 HotFix standard. You can run the %WaConvrt macro two ways: 1) in Diagnose mode to assess a Web mart and create a report that - identifies changes that %WaConvrt will make when it is run in Update mode - lists changes that you will have to make manually 2) in Update mode to convert a Web mart and create a report that - identifies changes that %WaConvrt has made - lists changes that you will have to make manually The %WaConvrt Macro: Syntax %WaConvrt(SWAMART=path to the Web marts root, TEMP_STORE=path to the Web marts Worklib directory, MODE=DIAGNOSE | UPDATE ); The %WaConvrt Macro: Details SWAMART=path to the Web marts root specifies the fully qualified path to the Web marts root TEMP_STORE=path to the Worklib directory (optional) specifies the fully qualified path to the Web marts root if this parameter is omitted or left blank, the %WaConvrt macro creates a default Worklib directory as a sub-directory of WORK MODE=DIAGNOSE | UPDATE specifies whether %WaConvrt should DIAGNOSE (create a report of what changes will be made to bring a Web mart into compliance with the v5.2 HotFix) or UPDATE (actually make the changes to the designated Web mart) If this parameter is omitted, the default (DIAGNOSE) operation is done. To actually convert the designated Web mart, you must specify MODE=UPDATE The %WaConvrt Macro: Notes The %WaConvrt macro performs error checking on the arguments that you specify and writes error messages in the log if it detects any problems. The format for these messages is as follows: ERROR: (WACONVRT) hh:mm:ss (text of message) where hh:mm:ss is the time when the %WaConvrt macro ran. The text of the message indicates the type of problem that the %WaConvrt macro detected. If the text inside the parentheses is not "WACONVRT," then it will be the name of a module that %WaConvrt has called and means that the error occurred in the called module, not in %WaConvrt. The %WaConvrt macro creates a report that summarizes its activities. When MODE=DIAGNOSE, the report indicates what changes the %WaConvrt macro will need to make to bring the designated Web mart into compliance with the v5.2 HotFix standard. These changes can be made by re-submitting the %WaConvrt macro with MODE=UPDATE. When MODE=UPDATE, the report lists all the Web mart features that the %WaConvrt macro assessed and indicates if %WaConvrt was able to update them or if %WaConvrt detected a problem with the feature that will need to be addressed manually. NOTE: you do not need to first run the %WaConvrt macro with MODE=DIAGNOSE. However, if you do run the %WaConvrt macro with MODE=DIAGNOSE, you will be able to quickly discover whether the %WaConvrt macro can automatically update your Web mart so that it is compliant with the v5.2 HotFix or whether you will need to make manual changes as well. Running the %WaConvrt macro with MODE=UPDATE can provide you with the same information, but will take much longer because of the additional processing needed to perform the actual updates. The %WaConvrt Macro: Examples Example1. %waconvrt(swamart=D:\swa\TestMart ); Create the diagnostic v5.2 HotFix report for the Web mart whose root directory is at D:\swa\TestMart. Use the default location for WORKLIB. Example2. %waconvrt(swamart=D:\swa\TestMart ,temp_store=D:\swa\TestMart\TempStore ); Create the diagnostic v5.2 HotFix report for the Web mart whose root directory is at D:\swa\TestMart. Use the D:\swa\TestMart\TempStore directory for WORKLIB (this will make the temporary work data sets available after the SAS session in which the %WaConvrt macro was submitted has ended). Example3. %waconvrt(swamart=D:\swa\TestMart ,temp_store=D:\swa\TestMart\TempStore ,mode=diagnose ); Create the diagnostic v5.2 HotFix report for the Web mart whose root directory is at D:\swa\TestMart\TestMart. Use the D:\swa\TestMart\TempStore directory for WORKLIB (this will make the temporary work data sets available after the SAS session in which the %WaConvrt macro was submitted has ended). This is the same as Example 2, except that here the MODE is explicitly set to DIAGNOSE (in Example 2, it is implicitly set to DIAGNOSE, the default). Example4. %waconvrt(swamart=D:\swa\TestMart ,temp_store=D:\swa\TestMart\TempStore ,mode=update ); Try to make all the changes necessary to convert the designated Web mart to the state of the v5.2 HotFix. Use the D:\swa\TestMart\TempStore directory for WORKLIB (this will make the temporary work data sets available after the SAS session in which the %WaConvrt macro was submitted has ended). NOTE: if the %WaConvrt macro could not make a required change to the designated Web mart, it will present an ERROR: message in its summary report that indicates the update will need to be made manually. /*----------------------Web Analytics-----------------------------------------*/ /* WACONVRT */ /* Convert Web Analytics features so they conform to the current release. */ /* List of conversion activities: */ /* II.A Make sure that the CONFIG.WAADMSUM data set has a column named */ /* ETL_RUN_SW. if it does not, create it and assign it the value */ /* of 1 for all rows. */ /* II.B Make sure that the CONFIG.WADSHINP data set has a column named */ /* DASHBOARD_LINK. if it does not, create it and assign it the */ /* value of blanks for all rows. */ /* II.C Make sure that the CONFIG.WAAUTOSG data set has a column named */ /* ETL_RUN_SW. if it does not, create it and assign it the value */ /* of 1 for all rows. */ /* II.D Make sure that the CONFIG.WADSHBRD data set has a column named */ /* ETL_RUN_SW. if it does not, create it and assign it the value */ /* of 1 for all rows. */ /* II.E Make sure that the CONFIG.WASCRCRD data set has a column named */ /* ETL_RUN_SW. if it does not, create it and assign it the value */ /* of 1 for all rows. */ /* II.F Make sure that the CONFIG.WAPATH data set has a new column */ /* named MIN_SEQUENCE_LENGTH. if it does not, create it and */ /* assign it the value blanks for all rows. */ /* Re-name the existing field named SEQUENCE_LENGTH to MAX_ */ /* SEQUENCE_LENGTH. */ /* II.G Make sure that the CONFIG.WAFUNNEL data set has a column named */ /* IsStatic. if it does not, create it and assign it the value */ /* of 0 for all rows. */ /* II.H Make sure that the current set of members are present in the */ /* Web marts \sas sub-directory */ /* II.I Make sure that the PROC_SUMMARY_OPTIONS field in the */ /* CONFIG.WAADMSUM data set contains comma-delimited values */ /* II.J Make sure that the STP_x.SAS programs in the \sas directory */ /* have the statement, OPTIONS COMPRESS=YES */ /* II.K Make sure that the VALUE field in the CONFIG.WACONFIG data set */ /* has a length of at least 1024. */ /* II.L Remove e-Data-ETL-specific formats from any data sets in the */ /* DATED, SUMMARY, and WADETAIL libraries */ /* II.M Make sure that the SUMMARY library contains a */ /* CALENDAR_EXCEPTION data set. */ /* II.N Make sure that the PROC_SUMMARY_OPTIONS field in the */ /* CONFIG.WAADMSUM data set does not contain CHARTYPE */ /* II.O Make sure that CONFIG.WACONFIG already has a */ /* WAB_MAX_PATH_PAGES row */ /* II.P Test whether CONFIG.WAADMSUM has a RESULTS_TABLE field greater */ /* than 24 bytes long. If such a field exists, issue a */ /* notification message in the log that identifies the row and */ /* indicates that it must be changed using the Administrator. */ /* II.Q Translate any SUMMARY_LEVEL_LIST column in CONFIG.WAADMSUM */ /* that has 'quarter' to 'qtr'. Rename the corresponding */ /* SUMMARY data sets so that they have a 'qtr' suffix. */ /* II.R Make sure that the SESSION_ID field is numeric (this */ /* conversion will already have been made if the SUMMARY. */ /* NEXT_SESS_ID data set already exists). The SESSION_ID */ /* conversion to numeric needs to be done for the DATED, */ /* SUMMARY, and WADETAIL libraries. */ /* II.S Verify that the metadata in CONFIG.WAADMSUM are compatible */ /* with the use of _TOKENs for CLASS variables in the Summary */ /* Engine. */ /* II.T Make sure that CONFIG.WACONFIG already has a */ /* WAB_NORMALIZE_URI_URL row. */ /* II.U Create SUMMARY.HASH_ data sets with the MD5 tokens for their */ /* corresponding large text fields. */ /* II.V Remove the _TYPE_ field from WAADMSUM-based legacy summaries. */ /* II.W Create one date-based subset data set for each date in the */ /* current SUMMARY.PATHING data set. */ /* II.X Make sure that CONFIG.WACONFIG already has a */ /* WAB_TOP_PATH_PAGES row */ /* II.Y Create the SUMMARY.TOP_REFERRER_PATHS_HX and */ /* SUMMARY.TOP_ENTRY_PATHS_HX data sets for ETL pathing analyses */ /* II.Z Create the SUMMARY.NEXT_PAGE_REFERENCE data set */ /* II.Z1 Create the RECOVERY.PAGE_INVENTORY data set */ /* II.Z2 Address problems with PATHING_yyyymmdd and TOP_x_PATHS_HX */ /* */ /*----------------------------------------------------------------------------*/ /* Supported By: Frank Roediger, FRROED */ /* */ /*----------------------------------------------------------------------------*/ /* History: */ /* Date Description Username Change Code */ /* 20050608 Program Created frroed NA */ /* 20060719 HotFix 5.2 frroed NA */ /* */ /*----------------------------------------------------------------------------*/ /* Dependencies: */ /* The following macros need to be available: */ /* %WAUTILS */ /* %Config_Parameters */ /* %Current_OS */ /* %Get_Observation_Count */ /* */ /* The following macro variables need to be defined and assigned values */ /* in the invoking environment: */ /* none */ /* */ /*----------------------------------------------------------------------------*/ /* Parameters: */ /* SWAMART: the path to the root directory for the designated swamart */ /* TEMP_STORE: optional - path to the directory for the WORKLIB library */ /* */ /*----------------------------------------------------------------------------*/ /* Input: */ /* %waconvrt(swamart= */ /* ,temp_store= */ /* ); */ /* */ /*----------------------------------------------------------------------------*/ /* Output: */ /* */ /*----------------------------------------------------------------------------*/ /* Copyright (c) 2005 SAS Institute Inc. All Rights Reserved */ /*----------------------------------------------------------------------------*/ %macro waconvrt(swamart= ,temp_store= ,mode=diagnose ); %local macname; %let macname=&sysmacroname; %******************************************************************************; %* SECTION: I - Initialization ; %******************************************************************************; %******************************************************************************; %* MODULE: I_A - Set up macro variables and librefs. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=I_A; %let macname=&macname._&module; %put macname: >>>&macname<<<; %******************************************************************************; %* assign the standard WEBA macro variables ; %******************************************************************************; options compress=yes nofmterr; %local wab_rc; %global _wab_rc_; %let _wab_rc_ = 0; %let wab_rc = 0; %let wab_note = %nrstr(NOTE:(&macname) %left(%sysfunc(time(),time.))); %let wab_warning = %nrstr(WARNING:(&macname) %left(%sysfunc(time(),time.))); %let wab_error = %nrstr(ERROR:(&macname) %left(%sysfunc(time(),time.))); %* assign a macro variable WaConvrts PROC Path SUPPORT level ; %let waconvrt_support=3; %* initial assumption: Step II_W and Step II_Y will be bypassed ; %let step_ii_w=BYPASS; %let step_ii_y=BYPASS; %******************************************************************************; %* assign a yyyymmdd representation of the current date to CURRDTTM ; %******************************************************************************; data _null_; length tm $4; tm=compress(translate((put(time(),hhmm5.)),' ',':')); if length(tm) eq 3 then tm='0' || compress(tm); call symput('currdttm' ,trim(left(put(date(),yymmddn8.))) || '_' || strip(tm) ); run; %******************************************************************************; %* compile the standard WEBA macro programs ; %******************************************************************************; dm 'af catalog=SASHELP.WAGUI.WAINI.SCL'; %wautils; %Current_OS; %if &_wab_rc_ ne 0 %then %do; %put %unquote(&wab_error) CURRENT_OS macro did not run %QCMPRES( successfully). The SEPARATOR macro variable cannot be defined.; %goto WACONVERT_END; %end; %******************************************************************************; %* check whether &MODE is valid -- DIAGNOSE or UPDATE ; %******************************************************************************; %if %upcase(&mode) ne DIAGNOSE and %upcase(&mode) ne UPDATE %then %do; %put %unquote(&wab_error) The MODE argument -- &mode -- is not %QCMPRES( valid.) It must be DIAGNOSE or UPDATE.; %goto WACONVERT_END; %end; %******************************************************************************; %* check whether &SWAMART exists and has the appropriate sub-directories ; %******************************************************************************; %if %quote(&swamart) eq %then %do; %put %unquote(&wab_error) The SWAMART argument was not provided. %QCMPRES( It) must be specified.; %goto WACONVERT_END; %end; %let swamart=%sysfunc(strip(%sysfunc(translate(&swamart," ","'")))); %let swamart=%sysfunc(strip(%sysfunc(translate(&swamart,' ','"')))); %if not %sysfunc(fileexist(&swamart)) %then %do; %put %unquote(&wab_error) The SWAMART argument -- &swamart -- is %QCMPRES( not) a valid path.; %goto WACONVERT_END; %end; %******************************************************************************; %* assign the necessary librefs ; %******************************************************************************; %let wab_config_dir=&swamart.&separator.config; %if not %sysfunc(fileexist(&wab_config_dir)) %then %do; %put %unquote(&wab_error) The &wab_config_dir location for the %QCMPRES( CONFIG) library does not exist.; %goto WACONVERT_END; %end; libname config "&wab_config_dir"; %let wab_doc_dir=&swamart.&separator.data&separator.documents; %if not %sysfunc(fileexist(&wab_doc_dir)) %then %do; %put %unquote(&wab_error) The &wab_doc_dir location for the %QCMPRES( DOCUMENT library does not exist.; %goto WACONVERT_END; %end; libname document "&wab_doc_dir"; %let wab_dated_dir=&swamart.&separator.data&separator.dated; %if not %sysfunc(fileexist(&wab_dated_dir)) %then %do; %put %unquote(&wab_error) The &wab_dated_dir location for the %QCMPRES( DATED library does not exist.; %goto WACONVERT_END; %end; libname dated "&wab_dated_dir"; %let wab_detl_dir=&swamart.&separator.data&separator.detail; %if not %sysfunc(fileexist(&wab_detl_dir)) %then %do; %put %unquote(&wab_error) The &wab_detl_dir location for the %QCMPRES( WADETAIL library does not exist.; %goto WACONVERT_END; %end; libname wadetail "&wab_detl_dir"; %let wab_smy_dir=&swamart.&separator.data&separator.summary; %if not %sysfunc(fileexist(&wab_smy_dir)) %then %do; %put %unquote(&wab_error) The &wab_smy_dir location for the %QCMPRES( SUMMARY library does not exist.; %goto WACONVERT_END; %end; libname summary "&wab_smy_dir"; %******************************************************************************; %* Assign the libref for TEMP_LIB. ; %******************************************************************************; %* TEMP_STORE was not specified: use the default -- /swawork under work. ; %******************************************************************************; %if %quote(&temp_store)= %then %do; %let temp_store=%sysfunc(pathname(work,L)); %let temp_store=&temp_store.&separator.swawork; %if not %sysfunc(fileexist(&temp_store)) %then %do; %make_directory(dirname=&temp_store ,retcode=wab_rc); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) A new directory could not be made %QCMPRES( at) the default TEMP_STORE location -- &temp_store..; %goto WACONVERT_END; %end; %end; libname worklib "&temp_store"; %let temp_lib=worklib; %end; %******************************************************************************; %* TEMP_STORE was specified: make sure that it exists and does not point to ; %* the same directory as the SUMMARY library. ; %******************************************************************************; %else %do; %if not %sysfunc(fileexist(&temp_store)) %then %do; %put %unquote(&wab_error) TEMP_STORE -- &temp_store -- argument %QCMPRES( is) a directory that does not exist.; %goto WACONVERT_END; %end; %if %sysfunc(strip(%sysfunc(lowcase("&temp_store")))) =%sysfunc(strip(%sysfunc(lowcase("&wab_smy_dir")))) %then %do; %put %unquote(&wab_error) TEMP_STORE -- &temp_store -- argument %QCMPRES( cannot) be the same as the permanent location for the %QCMPRES( SUMMARY) library.; %goto WACONVERT_END; %end; libname worklib "&temp_store"; %let temp_lib=worklib; %end; %*****************************************************************************; %* Create macro variables with the values of the configuration parameters ; %*****************************************************************************; %Config_Parameters(retcode=wab_rc); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) All configuration parameters could not %QCMPRES( be) converted into macro variables.; %goto WACONVERT_END; %end; %******************************************************************************; %* MODULE: II.A Make sure that the CONFIG.WAADMSUM data set has a column ; %* named ETL_RUN_SW. If it does not, create it and assign it ; %* the value of 1 for all rows. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_A; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; proc sql; create table waadmsum_inventory as select * from dictionary.columns where libname eq 'CONFIG' and memname eq 'WAADMSUM' and upcase(name) eq 'ETL_RUN_SW'; quit; %if %get_observation_count(indsn=waadmsum_inventory) eq 0 %then %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_a=%unquote(&wab_warning) The WaConvrt macro will add %QCMPRES( the) ETL_RUN_SW column to CONFIG.WAADMSUM.; %goto II_A_EXIT; %end; data config.waadmsum_pre_v52_backup; set config.waadmsum; run; data config.waadmsum; %wametatr(srce_dsn=waadmsum ,retcode=wab_rc ); set config.waadmsum_pre_v52_backup; etl_run_sw=1; run; proc datasets library=config nolist; modify waadmsum; index create id / nomiss unique; quit; %let msg_ii_a=%unquote(&wab_note) ETL_RUN_SW column has been added %QCMPRES( to) CONFIG.WAADMSUM.; %end; %else %do; %let msg_ii_a=%unquote(&wab_note) CONFIG.WAADMSUM already has an %QCMPRES( ETL_RUN_SW) column.; %put %unquote(&wab_note) CONFIG.WAADMSUM already has an %QCMPRES( ETL_RUN_SW) column.; %end; %II_A_EXIT:; %******************************************************************************; %* MODULE: II.B Make sure that the CONFIG.WADSHINP data set has a column ; %* named DASHBOARD_LINK. If it does not, create it and assign ; %* it the value of blanks for all rows. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_B; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; proc sql; create table wadshinp_inventory as select * from dictionary.columns where libname eq 'CONFIG' and memname eq 'WADSHINP' and upcase(name) eq 'DASHBOARD_LINK'; quit; %if %get_observation_count(indsn=wadshinp_inventory) eq 0 %then %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_b=%unquote(&wab_warning) The WaConvrt macro will add %QCMPRES( the) DASHBOARD_LINK column to CONFIG.WADSHINP.; %goto II_B_EXIT; %end; data config.wadshinp_pre_v52_backup; set config.wadshinp; run; data config.wadshinp; %wametatr(srce_dsn=wadshinp ,retcode=wab_rc ); set config.wadshinp_pre_v52_backup; dashboard_link=' '; run; proc datasets library=config nolist; modify wadshinp; index create id / nomiss unique; quit; %let msg_ii_b=%unquote(&wab_note) The DASHBOARD_LINK column has %QCMPRES( been) added to CONFIG.WADSHINP.; %end; %else %do; %let msg_ii_b=%unquote(&wab_note) CONFIG.WADSHINP already has a %QCMPRES( DASHBOARD_LINK) column.; %put %unquote(&wab_note) CONFIG.WADSHINP already has a %QCMPRES( DASHBOARD_LINK) column.; %end; %II_B_EXIT:; %******************************************************************************; %* MODULE: II.C Make sure that the CONFIG.WAAUTOSG data set has a column ; %* named ETL_RUN_SW. If it does not, create it and assign it ; %* the value of 1 for all rows. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_C; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; proc sql; create table waautosg_inventory as select * from dictionary.columns where libname eq 'CONFIG' and memname eq 'WAAUTOSG' and upcase(name) eq 'ETL_RUN_SW'; quit; %if %get_observation_count(indsn=waautosg_inventory) eq 0 %then %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_c=%unquote(&wab_warning) The WaConvrt macro will add %QCMPRES( the) ETL_RUN_SW column to CONFIG.WAAUTOSG.; %goto II_C_EXIT; %end; data config.waautosg_pre_v52_backup; set config.waautosg; run; data config.waautosg; %wametatr(srce_dsn=waautosg ,retcode=wab_rc ); set config.waautosg_pre_v52_backup; etl_run_sw=1; run; proc datasets library=config nolist; modify waautosg; index create id / nomiss unique; quit; %let msg_ii_c=%unquote(&wab_note) The ETL_RUN_SW column has %QCMPRES( been) added to CONFIG.WAAUTOSG.; %end; %else %do; %let msg_ii_c=%unquote(&wab_note) CONFIG.WAAUTOSG already has an %QCMPRES( ETL_RUN_SW) column.; %put %unquote(&wab_note) CONFIG.WAAUTOSG already has an %QCMPRES( ETL_RUN_SW) column.; %end; %II_C_EXIT:; %******************************************************************************; %* MODULE: II.D Make sure that the CONFIG.WADSHBRD data set has a column ; %* named ETL_RUN_SW. If it does not, create it and assign it ; %* the value of 1 for all rows. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_D; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; proc sql; create table wadshbrd_inventory as select * from dictionary.columns where libname eq 'CONFIG' and memname eq 'WADSHBRD' and upcase(name) eq 'ETL_RUN_SW'; quit; %if %get_observation_count(indsn=wadshbrd_inventory) eq 0 %then %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_d=%unquote(&wab_warning) The WaConvrt macro will add %QCMPRES( the) ETL_RUN_SW column to CONFIG.WADSHBRD.; %goto II_D_EXIT; %end; data config.wadshbrd_pre_v52_backup; set config.wadshbrd; run; data config.wadshbrd; %wametatr(srce_dsn=wadshbrd ,retcode=wab_rc ); set config.wadshbrd_pre_v52_backup; etl_run_sw=1; run; proc datasets library=config nolist; modify wadshbrd; index create id / nomiss unique; quit; %let msg_ii_d=%unquote(&wab_note) The ETL_RUN_SW column has %QCMPRES( been) added to CONFIG.WADSHBRD.; %end; %else %do; %let msg_ii_d=%unquote(&wab_note) CONFIG.WADSHBRD already has an %QCMPRES( ETL_RUN_SW) column.; %put %unquote(&wab_note) CONFIG.WADSHBRD already has an %QCMPRES( ETL_RUN_SW) column.; %end; %II_D_EXIT:; %******************************************************************************; %* MODULE: II.E Make sure that the CONFIG.WASCRCRD data set has a column ; %* named ETL_RUN_SW. If it does not, create it and assign it ; %* the value of 1 for all rows. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_E; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; proc sql; create table wascrcrd_inventory as select * from dictionary.columns where libname eq 'CONFIG' and memname eq 'WASCRCRD' and upcase(name) eq 'ETL_RUN_SW'; quit; %if %get_observation_count(indsn=wascrcrd_inventory) eq 0 %then %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_e=%unquote(&wab_warning) The WaConvrt macro will add %QCMPRES( the) ETL_RUN_SW column to CONFIG.WASCRCRD.; %goto II_E_EXIT; %end; data config.wascrcrd_pre_v52_backup; set config.wascrcrd; run; data config.wascrcrd; %wametatr(srce_dsn=wascrcrd ,retcode=wab_rc ); set config.wascrcrd_pre_v52_backup; etl_run_sw=1; run; proc datasets library=config nolist; modify wascrcrd; index create id / nomiss unique; quit; %let msg_ii_e=%unquote(&wab_note) The ETL_RUN_SW column has %QCMPRES( been) added to CONFIG.WASCRCRD.; %end; %else %do; %let msg_ii_e=%unquote(&wab_note) CONFIG.WASCRCRD already has an %QCMPRES( ETL_RUN_SW) column.; %put %unquote(&wab_note) CONFIG.WASCRCRD already has an %QCMPRES( ETL_RUN_SW) column.; %end; %II_E_EXIT:; %******************************************************************************; %* MODULE: II.F Make sure that the CONFIG.WAPATH data set has a new column ; %* named MIN_SEQUENCE_LENGTH. if it does not, create it and ; %* assign it the value blanks for all rows. ; %* Re-name the existing field named SEQUENCE_LENGTH to MAX_ ; %* SEQUENCE_LENGTH. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_F; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; proc sql; create table wapath_inventory as select * from dictionary.columns where libname eq 'CONFIG' and memname eq 'WAPATH' and upcase(name) eq 'MIN_SEQUENCE_LENGTH'; quit; %if %get_observation_count(indsn=wapath_inventory) eq 0 %then %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_f=%unquote(&wab_warning) The WaConvrt macro will add %QCMPRES( the) MIN_SEQUENCE_LENGTH column to CONFIG.WAPATH.; %goto II_F_EXIT; %end; data config.wapath_pre_v52_backup; set config.wapath; run; data config.wapath; %wametatr(srce_dsn=wapath ,retcode=wab_rc ); set config.wapath_pre_v52_backup; min_sequence_length=' '; max_sequence_length=sequence_length; drop sequence_length; run; proc datasets library=config nolist; modify wapath; index create id / nomiss unique; quit; %let msg_ii_f=%unquote(&wab_note) The MIN_SEQUENCE_LENGTH column %QCMPRES( has) been added to CONFIG.WAPATH.; %end; %else %do; %let msg_ii_f=%unquote(&wab_note) CONFIG.WAPATH already has a %QCMPRES( MIN_SEQUENCE_LENGTH) column.; %put %unquote(&wab_note) CONFIG.WAPATH already has a %QCMPRES( MIN_SEQUENCE_LENGTH) column.; %end; %II_F_EXIT:; %******************************************************************************; %* MODULE: II.G Make sure that the CONFIG.WAFUNNEL data set has a column ; %* named IsStatic. if it does not, create it and assign it the ; %* value of 0 for all rows. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_G; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; proc sql; create table wafunnel_inventory as select * from dictionary.columns where libname eq 'CONFIG' and memname eq 'WAFUNNEL' and upcase(name) eq 'ISSTATIC'; quit; %if %get_observation_count(indsn=wafunnel_inventory) eq 0 %then %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_g=%unquote(&wab_warning) The WaConvrt macro will add %QCMPRES( the) IsStatic column to CONFIG.WAFUNNEL.; %goto II_G_EXIT; %end; data config.wafunnel_pre_v52_backup; set config.wafunnel; run; data config.wafunnel; %wametatr(srce_dsn=wafunnel ,retcode=wab_rc ); set config.wafunnel_pre_v52_backup; IsStatic=0; run; proc datasets library=config nolist; modify wafunnel; index create id / nomiss unique; quit; %let msg_ii_g=%unquote(&wab_note) The IsStatic column %QCMPRES( has) been added to CONFIG.WAFUNNEL.; %end; %else %do; %let msg_ii_g=%unquote(&wab_note) CONFIG.WAFUNNEL already has an %QCMPRES( IsStatic) column.; %put %unquote(&wab_note) CONFIG.WAFUNNEL already has an %QCMPRES( IsStatic) column.; %end; %II_G_EXIT:; %******************************************************************************; %* MODULE: II.H Make sure that the current set of members are present in the ; %* Web marts \sas sub-directory ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_H; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_h=%unquote(&wab_warning) The WaConvrt macro will update %QCMPRES( the) the Web marts SAS sub-directory with a complete set of %QCMPRES( standard) entries.; %goto II_H_EXIT; %end; %wasasdir(retcode=wab_rc); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Failure executing WASASDIR.; %let msg_ii_h=%unquote(&wab_error) Failure executing WASASDIR -- %QCMPRES( the) Web marts SAS sub-directory may not have all the standard %QCMPRES( entries).; %goto II_H_EXIT; %end; %let msg_ii_h=%unquote(&wab_note) The Web marts SAS sub-directory %QCMPRES( has) the complete set of standard entries.; %II_H_EXIT:; %******************************************************************************; %* MODULE: II.I Make sure that the PROC_SUMMARY_OPTIONS field in the ; %* CONFIG.WAADMSUM data set contains comma-delimited values. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_I; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; proc freq data=config.waadmsum (where=(index(proc_summary_options,',') eq 0 and strip(proc_summary_options) ne compress(proc_summary_options) and proc_summary_options gt ' ' ) ) noprint; tables id / out=waadmsum_freqout; run; %if %get_observation_count(indsn=waadmsum_freqout) ne 0 %then %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_i=%unquote(&wab_warning) The WaConvrt macro will add %QCMPRES( comma) delimiters to the PROC_SUMMARY_OPTIONS field in %QCMPRES( CONFIG.WAADMSUM.); %goto II_I_EXIT; %end; data config.waadmsum; set config.waadmsum; if index(proc_summary_options,',') eq 0 and strip(proc_summary_options) ne compress(proc_summary_options) and proc_summary_options gt ' ' then proc_summary_options=translate (strip (compbl(proc_summary_options) ),',',' '); run; proc datasets library=config nolist; modify waadmsum; index create id / nomiss unique; quit; %let msg_ii_i=%unquote(&wab_note) Comma delimiters have been %QCMPRES( added) to the PROC_SUMMARY_OPTIONS field in CONFIG.WAADMSUM.; %end; %else %do; %let msg_ii_i=%unquote(&wab_note) The PROC_SUMMARY_OPTIONS %QCMPRES( field) in CONFIG.WAADMSUM already is comma-delimited.; %put %unquote(&wab_note) The PROC_SUMMARY_OPTIONS field in %QCMPRES( CONFIG.WAADMSUM) already is comma-delimited.; %end; %II_I_EXIT:; %******************************************************************************; %* MODULE: II.J Make sure that the STP_x.SAS programs in the \sas directory ; %* have the statement, OPTIONS COMPRESS=YES ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_J; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %let j1_cnt=0; %let msg_ii_j1=%unquote(&wab_note); %let j2_cnt=0; %let msg_ii_j2=%unquote(&wab_note); %******************************************************************************; %* define a macro that will determine if an existing STP_ data set lacks the ; %* COMPRESS=YES Option and adds it if it is missing ; %******************************************************************************; %macro compress_yes(stp_dset=); data _null_; infile "&swamart.&separator.sas&separator.&stp_dset..sas" length=reclen end=eof; file "&swamart.&separator.sas&separator.&stp_dset._BACKUP.sas"; input @; input @ 1 in_rec $varying120. reclen; put _infile_; if index(upcase(compress(in_rec)),'COMPRESS=YES') then found_sw+1; if eof then do; if found_sw ge 1 then found_sw=1; else found_sw=0; call symput('waconvrt_found_sw',strip(put(found_sw,1.))); end; run; %if &waconvrt_found_sw eq 0 %then %do; data _null_; infile "&swamart.&separator.sas&separator.&stp_dset._BACKUP.sas" length=reclen end=eof; file "&swamart.&separator.sas&separator.&stp_dset..sas"; input @; input @ 1 in_rec $varying120. reclen; put _infile_; if index(upcase(compress(in_rec)),'*PROCESSBODY;') then put @ 1 'options compress=yes;'; run; %let j1_cnt=%eval(&j1_cnt+1); %let msg_ii_j1=&msg_ii_j1 The COMPRESS=YES Option has %QCMPRES( been) added to &stp_dset..sas.; %end; %else %do; %let j2_cnt=%eval(&j2_cnt+1); %let msg_ii_j2=&msg_ii_j2 &stp_dset..sas already has %QCMPRES( the) Option COMPRESS=YES.; %put %unquote(&wab_note) &stp_dset..sas already has the Option %QCMPRES( COMPRESS)=YES.; %end; %mend compress_yes; %if %upcase(&mode) eq DIAGNOSE %then %do; %let j1_cnt=1; %let msg_ii_j1=%unquote(&wab_warning) The WaConvrt macro will add %QCMPRES( the) COMPRESS=YES Option to STP_xxx.sas programs.; %goto II_J_EXIT; %end; %******************************************************************************; %* execute the COMPRESS_YES macro for each existing STP_x.SAS data set ; %******************************************************************************; %compress_yes(stp_dset=stp_wadesign); %compress_yes(stp_dset=stp_wafunnel); %compress_yes(stp_dset=stp_wapath); %compress_yes(stp_dset=stp_wastatus); %II_J_EXIT:; %******************************************************************************; %* MODULE: II.K Make sure that the VALUE field in the CONFIG.WACONFIG data ; %* set has a length of at least 1024. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_K; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; proc sql; create table waconfig_inventory as select * from dictionary.columns where libname eq 'CONFIG' and memname eq 'WACONFIG' and upcase(name) eq 'VALUE' and length lt 1024; quit; %if %get_observation_count(indsn=waconfig_inventory) ne 0 %then %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_k=%unquote(&wab_warning) The WaConvrt macro will %QCMPRES( increase) the length of the VALUE field in CONFIG.WACONFIG %QCMPRES( to) 1024 bytes.; %goto II_K_EXIT; %end; data config.waconfig_pre_v52_backup; set config.waconfig; run; data config.waconfig; %wametatr(srce_dsn=waconfig ,retcode=wab_rc ); set config.waconfig; run; proc datasets library=config nolist; modify waconfig; index create id / nomiss unique; quit; %let msg_ii_k=%unquote(&wab_note) The length of the VALUE field in %QCMPRES( CONFIG.WACONFIG) has been increased to 1024 bytes.; %end; %else %do; %let msg_ii_k=%unquote(&wab_note) The VALUE field in %QCMPRES( CONFIG.WACONFIG) already is at least 1024 bytes long.; %put %unquote(&wab_note) The VALUE field in CONFIG.WACONFIG %QCMPRES( already) is at least 1024 bytes long.; %end; %II_K_EXIT:; %******************************************************************************; %* MODULE: II.L Remove e-Data-ETL-specific formats from any data sets in the ; %* DATED, SUMMARY, and WADETAIL libraries. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_L; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %******************************************************************************; %* determine if there are any columns in any table in the DATED, SUMMARY, or ; %* WADETAIL libraries that has an e-Data-ETL-related format ; %******************************************************************************; proc sql; create table edata_formatted_cols as select * from dictionary.columns where libname in ('DATED','SUMMARY','WADETAIL') and ( index(upcase(format),'$BLNK') or index(upcase(format),'$COUNTRY') or index(upcase(format),'$OTYPE') or index(upcase(format),'$UNKN') or index(upcase(format),'$WHCOMP') or index(upcase(format),'STATUS') ) order by name ,memname ,libname; quit; %******************************************************************************; %* identify the LIBNAMEs and their MEMNAMEs that have columns with ; %* e-Data-ETL-related formats ; %******************************************************************************; proc summary data=edata_formatted_cols n chartype; class libname memname; output out=edata_libnames (where=(_type_ eq '10')); output out=edata_memnames (where=(_type_ eq '11')); run; %******************************************************************************; %* create a macro variable for each affected LIBNAME ; %******************************************************************************; data _null_; set edata_libnames; call symput('libname' || strip(put(_n_,3.)),strip(libname)); run; %if %get_observation_count(indsn=edata_libnames) eq 0 %then %do; %put %unquote(&wab_note) All data sets in the DATED, WADETAIL, or %QCMPRES( SUMMARY) libraries have already been stripped of their %QCMPRES( e-Data-ETL) formats.; %let msg_ii_l=%unquote(&wab_note) All data sets in the DATED, %QCMPRES( WADETAIL), and SUMMARY libraries have already been stripped %QCMPRES( of) their e-Data-ETL formats.; %end; %else %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_l=%unquote(&wab_warning) The WaConvrt macro will %QCMPRES( strip) data sets in the DATED, WADETAIL, and SUMMARY %QCMPRES( libraries) of their e-Data-ETL formats.; %goto II_L_EXIT; %end; %let msg_ii_l=%unquote(&wab_note) The following data sets have been %QCMPRES( stripped) of their e-Data-ETL formats: .; %do i=1 %to %get_observation_count(indsn=edata_libnames); data edata_memname_extr; set edata_memnames; if libname eq "&&libname&i"; run; %************************************************************************; %* create a macro variable for each affected MEMNAME ; %************************************************************************; data _null_; set edata_memname_extr; call symput('memname' || strip(put(_n_,3.)),strip(memname)); run; %do j=1 %to %get_observation_count(indsn=edata_memname_extr); %let name1=&&libname&i; %let name2=&&memname&j; %let msg_ii_l=&msg_ii_l &name1..&name2; %remove_formats(libname=&&libname&i ,memname=&&memname&j ); %end; %end; %end; %II_L_EXIT:; %******************************************************************************; %* MODULE: II.M Make sure that the SUMMARY library contains a ; %* CALENDAR_EXCEPTION data set. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_M; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %if %sysfunc(exist(summary.calendar_exception)) eq 0 %then %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_m=%unquote(&wab_warning) The WaConvrt macro will add %QCMPRES( the) CALENDAR_EXCEPTION data set to the SUMMARY library.; %goto II_M_EXIT; %end; data summary.calendar_exception; length documents_id $ 36 id $ 36 type $ 20 first_date 8 last_date 8; format first_date last_date date9.; stop; run; %let msg_ii_m=%unquote(&wab_note) The CALENDAR_EXCEPTION data set %QCMPRES( has) been added to the SUMMARY library.; %end; %else %do; %let msg_ii_m=%unquote(&wab_note) SUMMARY library already has a %QCMPRES( CALENDAR_EXCEPTION) data set.; %put %unquote(&wab_note) SUMMARY library already has a %QCMPRES( CALENDAR_EXCEPTION) data set.; %end; %II_M_EXIT:; %******************************************************************************; %* MODULE: II.N Make sure that the PROC_SUMMARY_OPTIONS field in the ; %* CONFIG.WAADMSUM data set does not contain CHARTYPE. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_N; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; proc freq data=config.waadmsum (where=(index(upcase(proc_summary_options) ,'CHARTYPE' ) ) ) noprint; tables id / out=waadmsum_freqout2; run; %if %get_observation_count(indsn=waadmsum_freqout2) ne 0 %then %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_n=%unquote(&wab_warning) The WaConvrt macro will %QCMPRES( remove) CHARTYPE from the PROC_SUMMARY_OPTIONS field in %QCMPRES( CONFIG.WAADMSUM.); %goto II_N_EXIT; %end; data config.waadmsum; set config.waadmsum; if index(upcase(proc_summary_options),'CHARTYPE') then do; proc_summary_options=tranwrd(upcase(proc_summary_options) ,'CHARTYPE' ,' ' ); proc_summary_options=tranwrd(proc_summary_options ,',,' ,',' ); if substr(reverse(strip(proc_summary_options)),1,1) eq ',' then proc_summary_options= reverse(substr(reverse(strip(proc_summary_options)),2)); proc_summary_options=lowcase(proc_summary_options); end; run; proc datasets library=config nolist; modify waadmsum; index create id / nomiss unique; quit; %let msg_ii_n=%unquote(&wab_note) CHARTYPE has been removed from %QCMPRES( the) PROC_SUMMARY_OPTIONS field in CONFIG.WAADMSUM.; %end; %else %do; %let msg_ii_n=%unquote(&wab_note) The PROC_SUMMARY_OPTIONS field in %QCMPRES( CONFIG.WAADMSUM) already does not contain CHARTYPE.; %put %unquote(&wab_note) The PROC_SUMMARY_OPTIONS field in %QCMPRES( CONFIG.WAADMSUM) already does not contain CHARTYPE.; %end; %II_N_EXIT:; %******************************************************************************; %* MODULE: II.O Make sure that CONFIG.WACONFIG already has a ; %* WAB_MAX_PATH_PAGES row. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_O; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; data &temp_lib..wab_max_path_pages; set config.waconfig; if upcase(parameter_name) eq 'WAB_MAX_PATH_PAGES'; run; %if %get_observation_count(indsn=&temp_lib..wab_max_path_pages) eq 0 %then %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_o=%unquote(&wab_warning) The WaConvrt macro will add %QCMPRES( the) WAB_MAX_PATH_PAGES row to CONFIG.WACONFIG.; %goto II_O_EXIT; %end; data config.waconfig; set config.waconfig end=eof; output; if eof then do; id_val=input(strip(id),2.)+1; id=put(id_val,2.); Parameter_Name='wab_max_path_pages'; Value='500'; Description='The maximum page count for a visit to be counted ' || 'in PROC Path analyses.'; output; end; drop id_val; run; proc datasets library=config nolist; modify waconfig; index create id / nomiss unique; quit; %let msg_ii_o=%unquote(&wab_note) The WAB_MAX_PATH_PAGES row has %QCMPRES( been) added to CONFIG.WACONFIG.; %end; %else %do; %let msg_ii_o=%unquote(&wab_note) CONFIG.WACONFIG already has a %QCMPRES( WAB_MAX_PATH_PAGES) row.; %put %unquote(&wab_note) CONFIG.WACONFIG already has a %QCMPRES( WAB_MAX_PATH_PAGES) row.; %end; %II_O_EXIT:; %******************************************************************************; %* MODULE: II.P Test whether CONFIG.WAADMSUM has a RESULTS_TABLE field ; %* greater than 24 bytes long. If such a field exists, issue ; %* a notification message in the log that identifies the row ; %* and indicates that it must be changed using the ; %* Administrator. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_P; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %let results_table_count=0; data results_table_length_too_long; set config.waadmsum end=eof; retain max_label max_table 0; field_size=length(results_table); if field_size gt 24 then do; results_table_count+1; output; if length(label) gt max_label then max_label=length(label); if length(results_table) gt max_table then max_table=length(results_table); end; if eof and results_table_count gt 0 then do; call symput('results_table_count',strip(put(results_table_count,2.))); call symput('max_label',strip(put(max_label,3.))); call symput('max_table',strip(put(max_table,3.))); end; keep label results_table field_size; run; %if &results_table_count ne 0 %then %do; %let prnt_pos1=%eval(17 + &max_label + 3); %let prnt_pos2=%eval(&prnt_pos1 + 18); %let prnt_pos3=%eval(&prnt_pos2 + &max_table + 3); %let prnt_pos4=%eval(&prnt_pos3 + 9); %let log_ls=%eval(&prnt_pos4 + 3); options ls=&log_ls; data _null_; set results_table_length_too_long end=eof; file log; if _n_ eq 1 then put @ 1 "WARNING: (&macname)" +2 'The name you are using for the Results Dataset for the' +1 "following &results_table_count Summaries is too long. " +1 'It cannot be longer than $24. Please use the Administrator' +1 'to change the name so that it is not too long before' +1 'running the Web Analytics ETL.' /; put @ 1 'WARNING:' @ 10 'Label:' @ 17 label @ &prnt_pos1 'Results Dataset:' @ &prnt_pos2 results_table @ &prnt_pos3 'length:' @ &prnt_pos4 field_size; call symput('too_long_label_' || strip(put(_n_,2.)) ,strip(label) ); call symput('too_long_table_' || strip(put(_n_,2.)) ,strip(results_table) ); if eof then call symput('too_long_cnt' ,strip(put(_n_,2.)) ); run; %let msg_ii_p=%unquote(&wab_warning) Some names in %QCMPRES( CONFIG.WAADMSUM) for the Results Dataset are too long. The %QCMPRES( name) cannot be longer than $24. Please use the Administrator %QCMPRES( to) change the name so that it is not too long before running %QCMPRES( the) Web Analytics ETL again.; %do i=1 %to &too_long_cnt; %let msg_ii_p=&msg_ii_p &i) Label: &&too_long_label_&i, %QCMPRES( Results) Data Set Name: &&too_long_table_&i; %end; %end; %else %do; %let msg_ii_p=%unquote(&wab_note) The name for the Results Dataset %QCMPRES( for) all Summaries is within the $24 limit.; %put %unquote(&wab_note) The name for the Results Dataset for %QCMPRES( all) Summaries is within the $24 limit.; %end; %******************************************************************************; %* MODULE: II.Q Translate any SUMMARY_LEVEL_LIST column in CONFIG.WAADMSUM ; %* that has 'quarter' to 'qtr'. Rename the corresponding ; %* SUMMARY data sets so that they have a 'qtr' suffix. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_Q; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %******************************************************************************; %* identify the rows with 'quarter' in SUMMARY_LEVEL_LIST ; %******************************************************************************; data _null_; set config.waadmsum end=eof; if index(lowcase(summary_level_list),'quarter') then do; quarter_cnt+1; call symput('quarter_' || strip(put(quarter_cnt,2.)) ,strip(results_table) ); output; end; if eof then do; call symput('quarter_cnt' ,strip(put(quarter_cnt,2.)) ); end; run; %let q2_cnt=0; %let q3_cnt=0; %if &quarter_cnt gt 0 %then %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_q1=%unquote(&wab_warning) The WaConvrt macro will %QCMPRES( change) 'quarter' to 'qtr' in the SUMMARY_LEVEL_LIST column %QCMPRES( of) CONFIG.WAADMSUM .; %goto II_Q_EXIT; %end; %if %sysfunc(exist(config.waadmsum_pre_v52_backup)) eq 0 %then %do; data config.waadmsum_pre_v52_backup; set config.waadmsum; run; %end; data config.waadmsum; set config.waadmsum; if index(lowcase(summary_level_list),'quarter') then summary_level_list=tranwrd(lowcase(summary_level_list) ,'quarter' ,'qtr' ); run; proc datasets library=config nolist; modify waadmsum; index create id / nomiss unique; quit; %put %unquote(&wab_note) The following &quarter_cnt rows in %QCMPRES( CONFIG.WAADMSUM) that had 'quarter' in the SUMMARY_LEVEL_LIST %QCMPRES( column) now have 'qtr'.; %do i=1 %to &quarter_cnt; %put %unquote(&wab_note) Row &i : &&quarter_&i; %end; %let msg_ii_q1=%unquote(&wab_note) The rows in CONFIG.WAADMSUM %QCMPRES( that) had 'quarter' in the SUMMARY_LEVEL_LIST column now have 'qtr'.; %***************************************************************************; %* rename the corresponding SUMMARY tables ; %* NOTE: renaming will take place only if 1) the _QUARTER version does ; %* exist and 2) the _QTR version does not exist. ; %***************************************************************************; %let msg_ii_q2=%unquote(&wab_note); %let msg_ii_q3=%unquote(&wab_warning); %do i=1 %to &quarter_cnt; %let quarter_dset=&&quarter_&i; %if %sysfunc(exist(summary.&quarter_dset._quarter)) ne 0 %then %do; %if %sysfunc(exist(summary.&quarter_dset._qtr)) eq 0 %then %do; %let q2_cnt=%eval(&q2_cnt + 1); %let msg_ii_q2=&msg_ii_q2 SUMMARY.&quarter_dset._quarter %QCMPRES( has) been renamed as SUMMARY.&quarter_dset._qtr.; proc datasets library=summary nowarn nolist; change &quarter_dset._quarter=&quarter_dset._qtr; quit; %end; %else %do; %let q3_cnt=%eval(&q3_cnt + 1); %let msg_ii_q3=&msg_ii_q3 Re-naming did not %QCMPRES( take) place because SUMMARY.&quarter_dset._qtr %QCMPRES( already) exists.; %put %unquote(&wab_warning) Re-naming cannot occur %QCMPRES( because) summary.&quarter_dset._qtr already exists.; %end; %end; %else %do; %let q3_cnt=%eval(&q3_cnt + 1); %let msg_ii_q3=&msg_ii_q3 Re-naming did not %QCMPRES( take) place because SUMMARY.&quarter_dset._quarter %QCMPRES( does) not exist.; %put %unquote(&wab_warning) Re-naming cannot occur %QCMPRES( because) summary.&quarter_dset._quarter does not exist.; %end; %end; %end; %else %do; %let msg_ii_q1=%unquote(&wab_note) CONFIG.WAADMSUM does not %QCMPRES( have) any rows with 'quarter' in the SUMMARY_LEVEL_LIST column.; %put %unquote(&wab_note) CONFIG.WAADMSUM does not have any rows %QCMPRES( with) 'quarter' in the SUMMARY_LEVEL_LIST column.; %end; %II_Q_EXIT:; %******************************************************************************; %* MODULE: II.R Make sure that the SESSION_ID field is numeric (this ; %* conversion will already have been made if the SUMMARY. ; %* NEXT_SESS_ID data set already exists). The SESSION_ID ; %* conversion to numeric needs to be done for the DATED, ; %* SUMMARY, and WADETAIL libraries. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_R; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %if %sysfunc(exist(summary.next_sess_id)) %then %do; %put %unquote(&wab_note) This Web mart already has the new style %QCMPRES( of) SESSION_ID. No conversion is needed.; %let msg_ii_r=%unquote(&wab_note) This Web mart already has the %QCMPRES( new) style of SESSION_ID. No conversion is needed.; %end; %else %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_r=%unquote(&wab_warning) The WaConvrt macro will %QCMPRES( convert) SESSION_ID to a numeric field in this Web mart.; %goto II_R_EXIT; %end; %***************************************************************************; %* because legacy Web marts can have a very large number of 245-byte ; %* SESSION_IDs, we want to make sure that we break up the re-assignment ; %* process into small enough units that they will not fail (it would be ; %* more efficient to convert everything at once -- and that approach ; %* might work for smaller marts, but we need to have a single strategy to ; %* convert ALL marts). our assumption is that the Web mart data was ; %* successfully put together one day at a time and the re-assigning ; %* SESSION_ID on the same basis would likely be successful, too. ; %***************************************************************************; %* identify all the DATED, WADETAIL, and SUMMARY data sets that have ; %* 245-byte SESSION_IDs ; %***************************************************************************; proc sql; %************************************************************************; %* identify the DATED and WADETAIL data sets that have 245-byte ; %* SESSION_IDs -- the suffix in their filename identifies their date. ; %************************************************************************; create table sess_id_locs1 as select libname ,memname from dictionary.columns where libname in ('DATED','WADETAIL') and upcase(name) eq 'SESSION_ID' and length eq 245 order by libname ,memname; %************************************************************************; %* identify the SUMMARY data sets that have 245-byte SESSION_IDs -- ; %* PATHIHG is the only standard one, but the Web mart may have some ; %* customized data sets as well. ; %* NOTE: customized data sets will be converted only if they contain a ; %* field called DATE. ; %************************************************************************; create table summary_date_dsets as select memname ,count(memname) as rec_count ,name from dictionary.columns where libname eq 'SUMMARY' and ( (upcase(name) eq 'SESSION_ID' and length eq 245) or upcase(name) eq 'DATE' ) group by memname; create table sess_id_locs2 as select distinct memname from summary_date_dsets where rec_count gt 1 and memname not in ('WEBLOG_DETAIL_1_SAMPLE' ,'CNTLIN_INDTOSEQURL' ,'PATHING_DMDB' ,'PATHING_DMDBCAT' ); quit; %if %get_observation_count(indsn=sess_id_locs1) eq 0 and %get_observation_count(indsn=sess_id_locs2) eq 0 %then %do; %put %unquote(&wab_note) This Web mart has empty DATED, DETAIL, %QCMPRES( and) SUMMARY libraries. NEXT_SESS_ID is primed to 1.; %let msg_ii_r=%unquote(&wab_note) This Web mart has empty DATED, %QCMPRES( DETAIL,) and SUMMARY libraries. NEXT_SESS_ID is primed to 1.; %let next_sess_id=1; %goto II_R_end; %end; %***************************************************************************; %* identify the dates that are represented by the identified data sets ; %* in the DATED and WADETAIL libraries ; %***************************************************************************; data dated_dates wadetail_dates; set sess_id_locs1; length suffix $ 8; if substr(scan(memname,2,'_'),1,1) eq '2' then suffix=scan(memname,2,'_'); else suffix=scan(memname,3,'_'); date=input(suffix,yymmdd8.); if libname eq 'DATED' then output dated_dates; else output wadetail_dates; keep date suffix ; run; proc sort data=dated_dates out =dated_uniqs nodupkey; by date; run; proc sort data=wadetail_dates out =wadetail_uniqs nodupkey; by date; run; %***************************************************************************; %* create INVENTORY with an entry for each date that has 245-byte ; %* SESSION_IDs that need to be converted ; %***************************************************************************; %* add DATED and WADETAIL entries to INVENTORY ; %***************************************************************************; data inventory; merge dated_uniqs (in=in_a) wadetail_uniqs (in=in_b); by date; if in_a then dated=date; if in_b then wadetail=date; format date date9.; run; data _null_; set sess_id_locs2 end=eof; call symput('smmry_' || strip(put(_n_,3.)) ,strip(memname) ); if eof then call symput('smmry_cnt',strip(put(_n_,3.))); run; %***************************************************************************; %* add entries for the SUMMARY data sets to INVENTORY ; %***************************************************************************; %do j=1 %to &smmry_cnt; proc summary data=summary.&&smmry_&j (keep=date) nway; class date; output out=smmry_dates; run; data inventory; merge inventory (in=in_a) smmry_dates (in=in_b keep=date); by date; if in_b then &&smmry_&j=date; run; %end; %***************************************************************************; %* create macro variables for each date in INVENTORY ; %***************************************************************************; data _null_; set inventory end=eof; call symput('date_' || strip(put(_n_,3.)) ,strip(put(date,5.)) ); call symput('sffx_' || strip(put(_n_,3.)) ,strip(suffix) ); if eof then call symput('date_cnt',strip(put(_n_,3.))); run; %***************************************************************************; %* create a partition for each SUMMARY data set for each DATE ; %* NOTE: this avoids having to make multiple passes through a large data ; %* set like SUMMARY.PATHING ; %***************************************************************************; %do j=1 %to &smmry_cnt; %do i=1 %to &date_cnt; %let name1=&&smmry_&j; %let name2=&&sffx_&i; %let name_&i=&name1.&name2; %end; data %do i=1 %to &date_cnt; &&name_&i %end;; set summary.&&smmry_&j; select (date); %do i=1 %to &date_cnt; when (&&date_&i) output &&name_&i; %end; otherwise; end; run; %end; %***************************************************************************; %* process each date in INVENTORY ; %***************************************************************************; %let next_sess_id=1; %***************************************************************************; %* make sure that the SUMMARY data sets that will be built with the new ; %* keys do not already exist ; %***************************************************************************; proc datasets library=summary nowarn nolist; %do j=1 %to &smmry_cnt; delete HF_&&smmry_&j; %end; quit; %do i=1 %to &date_cnt; %************************************************************************; %* initialize an empty list of 245-byte SESSION_IDs for DATE. The ; %* INVENTORY will be used to build MD5_HASH, which will be used to ; %* assign a numeric SESSION_ID to replace the old 245_byte field. ; %************************************************************************; data sess_id_inventory; length session_id $ 245; stop; run; %************************************************************************; %* add DATED entries to the list of 245-byte SESSION_IDs for DATE ; %************************************************************************; %if %sysfunc(exist(dated.session_&&sffx_&i)) %then %do; proc sort data=dated.session_&&sffx_&i (keep=session_id) out =dated_sess_ids nodupkeys; by session_id; run; proc append base=sess_id_inventory data=dated_sess_ids; run; %end; %************************************************************************; %* add WADETAIL entries to the list of 245-byte SESSION_IDs for DATE ; %* NOTE: WADETAIL has two entries for each DATE ; %************************************************************************; %if %sysfunc(exist(wadetail.detail_&&sffx_&i)) %then %do; proc sort data=wadetail.detail_&&sffx_&i (keep=session_id) out =wadetail_sess_ids nodupkeys; by session_id; run; data sess_id_inventory; update sess_id_inventory wadetail_sess_ids; by session_id; run; %end; %if %sysfunc(exist(wadetail.detail_tally_&&sffx_&i)) %then %do; proc sort data=wadetail.detail_tally_&&sffx_&i (keep=session_id) out =wadetail_sess_ids nodupkeys; by session_id; run; data sess_id_inventory; update sess_id_inventory wadetail_sess_ids; by session_id; run; %end; %************************************************************************; %* add the SUMMARY entries to list of 245-byte SESSION_IDs for DATE ; %************************************************************************; %do j=1 %to &smmry_cnt; %let name1=&&smmry_&j; %let name2=&&sffx_&i; %let name_&i=&name1.&name2; data sess_id_inventory; update sess_id_inventory &&name_&i (keep=session_id); by session_id; run; %end; %************************************************************************; %* create a 32-byte MD5 encoding of each 245-byte SESSION_IDs for DATE ; %************************************************************************; data md5_xlate; set sess_id_inventory; md5_32=put(md5(session_id),hex32.); run; proc sort data=md5_xlate; by md5_32; run; %************************************************************************; %* create a hash data set with a numeric value for each MD5_32 key ; %************************************************************************; data md5_hash (rename=(session_id=sess_id_245)) md5_dups; set md5_xlate end=eof; by md5_32; retain next_sess_id &next_sess_id; new_sess_id=next_sess_id; next_sess_id+1; output md5_hash; if first.md5_32 then do; if not last.md5_32 then output md5_dups; end; else output md5_dups; keep session_id md5_32 new_sess_id; if eof then call symput('next_sess_id',strip(put(next_sess_id,9.))); run; %************************************************************************; %* if MD5_DUPS has any observations, the MD5_32 field cannot be used ; %* as the key field in the hash table -- the old 245-byte SESSION_ID ; %* field must be used instead. ; %************************************************************************; %if %get_observation_count(indsn=md5_dups) ne 0 %then %do; %let md5_key=sess_id_245; %end; %else %do; %let md5_key=md5_32; %end; %************************************************************************; %* replace the 245-byte SESSION_IDs in the DATED data set for DATE ; %* NOTE: the DATED.SESSION_yyyymmdd data set is renamed with an _HF ; %* (HotFix) suffix before the SESSION_ID replacement occurs. ; %************************************************************************; %if %sysfunc(exist(dated.session_&&sffx_&i)) %then %do; %let bkup=&&sffx_&i; %let bkup=&bkup._HF; proc datasets library=dated nolist; change session_&&sffx_&i=session_&bkup; quit; data dated.session_&&sffx_&i; attrib session_id length=8 format=z12.; if _n_ eq 1 then do; declare hash h(dataset: 'work.md5_hash' ,hashexp: 10 ); h.defineKey("&md5_key"); h.defineData('new_sess_id'); h.defineDone(); end; set dated.session_&bkup (rename=(session_id=sess_id_245)); md5_32=put(md5(sess_id_245),hex32.); rc=h.find(); if rc eq 0 then session_id=new_sess_id; else do; if session_id le .z then do; missing_cnt+1; if missing_cnt le 15 then put "WARNING:(session_&&sffx_&i) SESSION_ID has missing values." / 'WARNING: old SESSION_ID=' sess_id_245 /; else if missing_cnt eq 16 then put "WARNING: more than 15 SESSION_IDs have missing values." / "WARNING: no more cases will be listed for session_&&sffx_&i.." /; end; end; drop sess_id_245 missing_cnt md5_32 rc ; run; %end; %************************************************************************; %* replace the 245-byte SESSION_IDs in the WADETAIL data set for DATE ; %* NOTE: the WADETAIL.DETAIL_yyyymmdd data set is renamed with an _HF ; %* (HotFix) suffix before the SESSION_ID replacement occurs. ; %* NOTE: WADETAIL has two entries for each DATE ; %************************************************************************; %if %sysfunc(exist(wadetail.detail_&&sffx_&i)) %then %do; %let bkup=&&sffx_&i; %let bkup=&bkup._HF; proc datasets library=wadetail nolist; change detail_&&sffx_&i=detail_&bkup; quit; data wadetail.detail_&&sffx_&i; attrib session_id length=8 format=z12.; if _n_ eq 1 then do; declare hash h(dataset: 'work.md5_hash' ,hashexp: 10 ); h.defineKey("&md5_key"); h.defineData('new_sess_id'); h.defineDone(); end; set wadetail.detail_&bkup (rename=(session_id=sess_id_245)); md5_32=put(md5(sess_id_245),hex32.); rc=h.find(); if rc eq 0 then session_id=new_sess_id; else do; if session_id le .z then do; missing_cnt+1; if missing_cnt le 15 then put "WARNING:(detail_&&sffx_&i) SESSION_ID has missing values." / 'WARNING: old SESSION_ID=' sess_id_245 /; else if missing_cnt eq 16 then put "WARNING: more than 15 SESSION_IDs have missing values." / "WARNING: no more cases will be listed for detail_&&sffx_&i.." /; end; end; drop sess_id_245 missing_cnt md5_32 rc ; run; %end; %if %sysfunc(exist(wadetail.detail_tally_&&sffx_&i)) %then %do; %let bkup=&&sffx_&i; %let bkup=&bkup._HF; proc datasets library=wadetail nolist; change detail_tally_&&sffx_&i=detail_tally_&bkup; quit; data wadetail.detail_tally_&&sffx_&i; attrib session_id length=8 format=z12.; if _n_ eq 1 then do; declare hash h(dataset: 'work.md5_hash' ,hashexp: 10 ); h.defineKey("&md5_key"); h.defineData('new_sess_id'); h.defineDone(); end; set wadetail.detail_tally_&bkup (rename=(session_id=sess_id_245)); md5_32=put(md5(sess_id_245),hex32.); rc=h.find(); if rc eq 0 then session_id=new_sess_id; else do; if session_id le .z then do; missing_cnt+1; if missing_cnt le 15 then put "WARNING:(detail_tally_&&sffx_&i) SESSION_ID has missing values." / 'WARNING: old SESSION_ID=' sess_id_245 /; else if missing_cnt eq 16 then put "WARNING: more than 15 SESSION_IDs have missing values." / "WARNING: no more cases will be listed for detail_tally_&&sffx_&i.." /; end; end; drop sess_id_245 missing_cnt md5_32 rc ; run; %end; %************************************************************************; %* replace the 245-byte SESSION_IDs in the SUMMARY data sets for DATE ; %* NOTE: because the SUMMARY data sets contain a range of dates, the ; %* process of replacing their 245_byte SESSION_IDs is different ; %* than the process for DATED and WADETAIL data sets. ; %************************************************************************; %do j=1 %to &smmry_cnt; %let name1=&&smmry_&j; %let name2=&&sffx_&i; %let name_&i=&name1.&name2; data summary.xlat&&smmry_&j; attrib session_id length=8 format=z12.; if _n_ eq 1 then do; declare hash h(dataset: 'work.md5_hash' ,hashexp: 10 ); h.defineKey("&md5_key"); h.defineData('new_sess_id'); h.defineDone(); end; set &&name_&i (where=(date eq &&date_&i) rename=(session_id=sess_id_245) ); md5_32=put(md5(sess_id_245),hex32.); rc=h.find(); if rc eq 0 then session_id=new_sess_id; else do; if session_id le .z then do; missing_cnt+1; if missing_cnt le 15 then put "WARNING:(detail_tally_&&sffx_&i) SESSION_ID has missing values." / 'WARNING: old SESSION_ID=' sess_id_245 /; else if missing_cnt eq 16 then put "WARNING: more than 15 SESSION_IDs have missing values." / "WARNING: no more cases will be listed for detail_tally_&&sffx_&i.." /; end; end; drop sess_id_245 missing_cnt md5_32 rc ; run; proc append base=summary.HF_&&smmry_&j data=summary.xlat&&smmry_&j; run; proc datasets library=summary nowarn nolist; delete xlat&&smmry_&j; quit; %end; %end; %***************************************************************************; %* rename the SUMMARY data sets that have the old 245-byte keys with the ; %* with an _HF (HotFix). then rename the SUMMARY data sets that have the ; %* new numeric SESSION_ID with standard data set name ; %***************************************************************************; %do j=1 %to &smmry_cnt; %let bkup=&&smmry_&j; %let bkup=&bkup._HF; proc datasets library=summary nolist; change &&smmry_&j=&bkup; change HF_&&smmry_&j=&&smmry_&j; quit; %end; %let msg_ii_r=%unquote(&wab_note) SESSION_ID conversion is %QCMPRES( complete.) The 245-byte character fields have been replaced %QCMPRES( by) 8-byte numerics.; %II_R_end:; %***************************************************************************; %* create SUMMARY.NEXT_SESS_ID to provide continuity for SESSION_ID ; %* assignments across ETL runs. ; %***************************************************************************; data summary.next_sess_id; session_id=&next_sess_id; etl_dttm=datetime(); format etl_dttm datetime19.; run; %end; %II_R_EXIT:; %******************************************************************************; %* MODULE: II.S Verify that the metadata in CONFIG.WAADMSUM are compatible ; %* with the use of _TOKENs for CLASS variables in the Summary ; %* Engine. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_S; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %******************************************************************************; %* There are 4 fields in WAADMSUM that will be changed during ETL to ; %* accommodate _TOKEN-based summarizations of long text fields. ; %* The 4 WAADMSUM fields are: ; %* - CLASS_VAR_LIST ; %* - OUTPUT_DSN_OPTIONS_0 ; %* - OUTPUT_DSN_OPTIONS_1 ; %* - OUTPUT_DSN_WHERE_STMT ; %* The WAADMSUM obs is NOT tested if its CLASS_VAR_LIST already contains a ; %* field with a suffix of _TOKEN. ; %* A summarization qualifies for testing if it has one of the following vars ; %* as a _TOKEN descriptor: ; %* - FIRST_REQUESTED_FILE ; %* - REQUESTED_FILE ; %* - REFERRER ; %* - REFERRER_DOMAIN ; %* - SEARCH_TERM ; %* - VISITOR_ID ; %******************************************************************************; data &temp_lib..WaConvrt_waadmsum_disqualify; set config.waadmsum end=eof; class_var_list=lowcase(class_var_list); output_dsn_where_stmt=lowcase(output_dsn_where_stmt); disqualify_ind=0; %***************************************************************************; %* CLASS vars that contain REQUESTED_FILE ; %***************************************************************************; if index(class_var_list,'first_requested_file') and not index(class_var_list,'first_requested_file_token') then do; if index(output_dsn_where_stmt,'first_requested_file') and not index(output_dsn_where_stmt,'first_requested_file_token') then link where_test; end; else if index(class_var_list,'requested_file') and not index(class_var_list,'requested_file_token') then do; if index(output_dsn_where_stmt,'requested_file') and not index(output_dsn_where_stmt,'requested_file_token') then link where_test; end; %***************************************************************************; %* CLASS vars that contain REFERRER ; %***************************************************************************; if index(class_var_list,'referrer_domain') and not index(class_var_list,'referrer_domain_token') then do; if index(output_dsn_where_stmt,'referrer_domain') and not index(output_dsn_where_stmt,'referrer_domain_token') then link where_test; end; else if index(class_var_list,'referrer') and not index(class_var_list,'referrer_token') and not index(class_var_list,'referrer_domain_token') then do; if index(output_dsn_where_stmt,'referrer') and not index(output_dsn_where_stmt,'referrer_token') and not index(output_dsn_where_stmt,'referrer_domain_token') then link where_test; end; %***************************************************************************; %* CLASS vars that contain SEARCH_TERM ; %***************************************************************************; if index(class_var_list,'search_term') and not index(class_var_list,'search_term_token')then do; if index(output_dsn_where_stmt,'search_term') and not index(output_dsn_where_stmt,'search_term_token') then link where_test; end; %***************************************************************************; %* CLASS vars that contain VISITOR_ID ; %***************************************************************************; if index(class_var_list,'visitor_id') and not index(class_var_list,'visitor_id_token')then do; if index(output_dsn_where_stmt,'visitor_id') and not index(output_dsn_where_stmt,'visitor_id_token') then link where_test; end; if disqualify_ind then output; return; WHERE_TEST:; %* test for WHERE features that cannot be used with _TOKENs ; if index(output_dsn_where_stmt,'between') or index(output_dsn_where_stmt,'?') or index(output_dsn_where_stmt,'contains') or index(output_dsn_where_stmt,'is null') or index(output_dsn_where_stmt,'is missing') or index(output_dsn_where_stmt,'like') or index(output_dsn_where_stmt,'=*') or index(output_dsn_where_stmt,'index') or index(output_dsn_where_stmt,'substr(') or index(output_dsn_where_stmt,'same') then disqualify_ind=1; return; run; %if %get_observation_count(indsn=&temp_lib..WaConvrt_waadmsum_disqualify) eq 0 %then %do; %let msg_ii_s_cnt=0; %let msg_ii_s=%unquote(&wab_note) The metadata in CONFIG.WAADMSUM %QCMPRES( are) compatible with the use of _TOKENs for CLASS variables.; %end; %else %do; %let msg_ii_s=%unquote(&wab_error) The following rows in %QCMPRES( CONFIG.WAADMSUM) have output WHERE statements that are not %QCMPRES( compatible) with the use of _TOKENs for CLASS variables. Please %QCMPRES( contact) SAS Technical Support for details.; data _null_; set &temp_lib..WaConvrt_waadmsum_disqualify end=eof; call symput('msg_ii_s_' || strip(put(_n_,2.)) ,strip(Label) || ' / ' || strip(output_dsn_where_stmt) ); if eof then do; call symput('msg_ii_s_cnt',put(_n_,2.)); end; run; %end; %******************************************************************************; %* MODULE: II.T Make sure that CONFIG.WACONFIG already has a ; %* WAB_NORMALIZE_URI_URL row. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_T; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; data &temp_lib..wab_normalize_uri_url; set config.waconfig; if upcase(parameter_name) eq 'WAB_NORMALIZE_URI_URL'; run; %if %get_observation_count(indsn=&temp_lib..wab_normalize_uri_url) eq 0 %then %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_t=%unquote(&wab_warning) The WaConvrt macro will add %QCMPRES( the) WAB_NORMALIZE_URI_URL row to CONFIG.WACONFIG.; %goto II_T_EXIT; %end; data config.waconfig; set config.waconfig end=eof; output; if eof then do; id_val=input(strip(id),2.)+1; id=put(id_val,2.); Parameter_Name='wab_normalize_uri_url'; Value='1'; Description='The 0/1 indicator of whether to lowcase/compress ' || 'URI/URL information.'; output; end; drop id_val; run; proc datasets library=config nolist; modify waconfig; index create id / nomiss unique; quit; %let msg_ii_t=%unquote(&wab_note) The WAB_NORMALIZE_URI_URL row %QCMPRES( has) been added to CONFIG.WACONFIG.; %end; %else %do; %let msg_ii_t=%unquote(&wab_note) CONFIG.WACONFIG already has a %QCMPRES( WAB_NORMALIZE_URI_URL) row.; %put %unquote(&wab_note) CONFIG.WACONFIG already has a %QCMPRES( WAB_NORMALIZE_URI_URL) row.; %end; %Config_Parameters(retcode=wab_rc); %II_T_EXIT:; %******************************************************************************; %* MODULE: II.U Create SUMMARY.HASH_ data sets with the MD5 tokens for the ; %* corresponding large text fields. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_U; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %******************************************************************************; %* Make sure that HASH_ data sets do not already exist in SUMMARY. if they ; %* do, assume that the _TOKEN feature has already been implemented for the ; %* Web mart. ; %******************************************************************************; proc sql; create table &temp_lib..hash_dsets as select memname ,lowcase(name) as token_var from dictionary.columns where libname eq 'SUMMARY' and index(memname,'HASH_'); quit; %if %get_observation_count(indsn=&temp_lib..hash_dsets) eq 0 %then %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_u=%unquote(&wab_warning) The WaConvrt macro will %QCMPRES( create) the HASH_ data sets in the SUMMARY library.; %goto II_U_EXIT; %end; %***************************************************************************; %* Identify the SUMMARY aggregations that need _TOKEN CLASS variables -- ; %* they have have one of the pre-identified large CLASS variables: ; %* - first_requested_file ; %* - referrer ; %* - referrer_domain ; %* - requested_file ; %* - search_term ; %* - visitor_id ; %***************************************************************************; proc sql; create table &temp_lib..dsets_needing_tokens as select memname ,lowcase(name) as native_field ,length as native_field_length from dictionary.columns where libname eq 'SUMMARY' and (index(memname,'_DAY') or index(memname,'_WEEK') or index(memname,'_MONTH') or index(memname,'_QTR') or index(memname,'_QUARTER') or index(memname,'_YEAR') ) and lowcase(name) in ('first_requested_file' ,'referrer' ,'referrer_domain' ,'requested_file' ,'search_term' ,'visitor_id' ) order by native_field ,memname ; quit; %***************************************************************************; %* Assign _TOKENs for values already in SUMMARY aggregations. if there are ; %* no variables with values that need tokens, generate an ERROR message. ; %***************************************************************************; %if %get_observation_count(indsn=&temp_lib..dsets_needing_tokens) gt 0 %then %do; proc sort data=&temp_lib..dsets_needing_tokens (keep=native_field native_field_length ) out =&temp_lib..fields_in_summary nodupkeys; by native_field; run; data _null_; set &temp_lib..fields_in_summary end=eof; call symput('uniq_token_var_' || strip(put(_n_,3.)) ,strip(native_field) || '_token' ); call symput('uniq_source_var_' || strip(put(_n_,3.)) ,strip(native_field) || '_source' ); call symput('uniq_string_var_' || strip(put(_n_,3.)) ,strip(native_field) ); call symput('uniq_string_len_' || strip(put(_n_,3.)) ,strip(put(native_field_length,5.)) ); if eof then call symput('uniq_string_var_count',strip(put(_n_,3.))); run; %do i=1 %to &uniq_string_var_count; %*********************************************************************; %* identify the SUMMARY data sets that contain &&UNIQ_STRING_VAR_&I ; %*********************************************************************; proc sort data=&temp_lib..dsets_needing_tokens (where=(native_field eq "&&uniq_string_var_&i")) out =&temp_lib..field_dset_list; by memname; run; data _null_; set &temp_lib..field_dset_list end=eof; call symput('string_dset_' || strip(put(_n_,3.)) ,strip(memname) ); if eof then call symput('string_dset_count',strip(put(_n_,3.))); run; %*********************************************************************; %* make sure that the &&UNIQ_TOKEN_VAR_&I data set does not exist ; %* in &TEMP_LIB ; %*********************************************************************; proc datasets library=&temp_lib nolist nowarn; delete &&uniq_token_var_&i; quit; %do j=1 %to &string_dset_count; %******************************************************************; %* create the _SOURCE field from the native field using the ; %* transformation/normalization rules. use the value in the ; %* _SOURCE field to assign a 32-byte MD5 value to _TOKEN field ; %******************************************************************; data summary.&&string_dset_&j; set summary.&&string_dset_&j; length &&uniq_source_var_&i $ &&uniq_string_len_&i; %* create the _SOURCE field using the normalization rules ; %if &wab_normalize_uri_url eq 1 %then %do; %* NORMALIZE COMPRESSes all blanks and LOWCASEs native field ; &&uniq_source_var_&i=compress(lowcase(&&uniq_string_var_&i)); %end; %else %do; %* not NORMALIZE COMPBLs blanks and LOWCASEs native field ; &&uniq_source_var_&i=compbl(lowcase(&&uniq_string_var_&i)); %end; %* assign _SOURCEs MD5 value to the _TOKEN field ; &&uniq_token_var_&i = put(md5(strip(&&uniq_source_var_&i)),hex32.); drop &&uniq_source_var_&i ; run; %******************************************************************; %* create the unique list of _TOKEN values for the current SUMMARY ; %* data set ; %******************************************************************; proc sort data=summary.&&string_dset_&j (keep=&&uniq_string_var_&i &&uniq_token_var_&i) out =&temp_lib..token_list nodupkeys; by &&uniq_token_var_&i; run; %******************************************************************; %* APPEND the working copy to the accummulated data ; %******************************************************************; proc append base=&temp_lib..&&uniq_token_var_&i data=&temp_lib..token_list; run; %end; %*********************************************************************; %* create SUMMARY.HASH_ data set by discarding the duplicates ; %* from the accummulated data ; %*********************************************************************; proc sort data=&temp_lib..&&uniq_token_var_&i out =summary.hash_&&uniq_token_var_&i nodupkeys; by &&uniq_token_var_&i; run; %end; %let msg_ii_u=%unquote(&wab_note) HASH_ data sets have %QCMPRES( been) created in the SUMMARY library.; %end; %else %do; %let msg_ii_u=%unquote(&wab_note) No SUMMARY variables need to %QCMPRES( have) _TOKENs created.; %put %unquote(&wab_note) No SUMMARY variables need to have %QCMPRES( _TOKENs) created.; %end; %end; %else %do; %let msg_ii_u=%unquote(&wab_note) HASH_ data sets already %QCMPRES( exist) in the SUMMARY library.; %put %unquote(&wab_note) HASH_ data sets already exist in %QCMPRES( the) SUMMARY library.; %end; %II_U_EXIT:; %******************************************************************************; %* MODULE: II.V Remove the _TYPE_ field from WAADMSUM-based legacy summaries ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_V; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; proc sql; create table &temp_lib.._type_dsets as select lowcase(memname) as memname from dictionary.columns where libname eq 'SUMMARY' and lowcase(name) eq '_type_' order by memname; quit; data &temp_lib..waadmsum_output_dsets; set config.waadmsum; length memname $ 32; results_table=lowcase(results_table); summary_level_list=lowcase(summary_level_list); do i=1 to (1+countc(summary_level_list,',')); memname=strip(results_table) || '_' || strip(scan(summary_level_list,i,',')); output; end; run; proc sort data=&temp_lib..waadmsum_output_dsets; by memname; run; data &temp_lib..drop_type_dsets; merge &temp_lib..waadmsum_output_dsets (in=in_a) &temp_lib.._type_dsets (in=in_b); by memname; if in_a and in_b then output; run; %if %get_observation_count(indsn=&temp_lib..drop_type_dsets) ne 0 %then %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_v=%unquote(&wab_warning) The WaConvrt macro will %QCMPRES( drop) _TYPE_ from legacy summarizations in the SUMMARY library.; %goto II_V_EXIT; %end; data _null_; set &temp_lib..drop_type_dsets end=eof; call symput('_type_drop_dset_' || strip(put(_n_,3.)) ,strip(memname) ); if eof then call symput('drop_dset_cnt',strip(put(_n_,3.))); run; %do i=1 %to &drop_dset_cnt; data summary.&&_type_drop_dset_&i; set summary.&&_type_drop_dset_&i; drop _type_; run; %end; %let msg_ii_v=%unquote(&wab_note) _TYPE_ has been dropped %QCMPRES( from) legacy summarizations in the SUMMARY library.; %end; %else %do; %let msg_ii_v=%unquote(&wab_note) _TYPE_ was already dropped %QCMPRES( from) legacy summarizations in the SUMMARY library.; %put %unquote(&wab_note) _TYPE_ was already dropped from legacy %QCMPRES( summarizations) in the SUMMARY library.; %end; %II_V_EXIT:; %******************************************************************************; %* MODULE: II.W Create one date-based subset data set (PATHING_yyyymmdd) for ; %* each date in the current SUMMARY.PATHING data set. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_W; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %******************************************************************************; %* check if there are already date-specific SUMMARY.PATHING_yyyymmdd data sets ; %******************************************************************************; proc sql; create table &temp_lib..date_specific_pathing as select memname from dictionary.tables where libname="SUMMARY" and memname ne 'PATHING' and memname like 'PATHING_%' and notdigit(trim(memname),9)=0 order by memname desc; quit; %if %get_observation_count(indsn=&temp_lib..date_specific_pathing) eq 0 %then %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_w=%unquote(&wab_warning) The WaConvrt macro will %QCMPRES( create) date-specific PATHING data sets in the SUMMARY library.; %goto II_W_EXIT; %end; %if %sysfunc(exist(summary.pathing)) ne 1 %then %do; %let msg_ii_w=%unquote(&wab_error) SUMMARY.PATHING does not %QCMPRES( exist.) Date-specific PATHING data sets have not been created.; %put %unquote(&wab_error) SUMMARY.PATHING does not %QCMPRES( exist.) Date-specific PATHING data sets have not been created.; %goto II_W_EXIT; %end; %* over-ride initial assignment ; %let step_ii_w=UPDATE; %***************************************************************************; %* Identify all the dates that are in SUMMARY.PATHING ; %* SUMMARY.PATHING_SESS_COUNT contains one record for each pathing ; %* date with the tally of pathing sessions for that date. it avoids ; %* having to re-count sessions whenever this information is needed. ; %***************************************************************************; proc summary data=summary.pathing (keep=date sequence where=(sequence eq 0)) nway; class date; output out=&temp_lib..pathing_sess_count (keep=date _freq_ rename=(_freq_=sess_count) ); run; data summary.pathing_sess_count; set &temp_lib..pathing_sess_count; retain last_dttm; if _n_ eq 1 then last_dttm=datetime(); format last_dttm datetime19.; run; data _null_; set summary.pathing_sess_count end=eof; length select_when $ 3200; retain select_when ''; call symput('pathdate_' || strip(put(_n_,2.)) ,strip(put(date,yymmddn8.)) ); select_when=strip(strip(select_when) || " when ('" || strip(put(date,date9.)) || "'d) output summary.pathing_" || '&pathdate_' || strip(put(_n_,2.)) || ';' ); if eof then do; call symput('pathdate_cnt' ,strip(put(_n_,2.)) ); call symput('select_when' ,strip(select_when) ); end; run; data %do i=1 %to &pathdate_cnt; summary.pathing_&&pathdate_&i (bufsize=256k) %end; ; set summary.pathing; by session_id notsorted; retain sess_date; if first.session_id then sess_date=date; select (sess_date); &select_when otherwise; end; drop sess_date referrer requested_file sequenced_requested_file; run; %if %sysfunc(exist(summary.pathing_ii_w)) eq 1 %then %do; proc datasets library=summary nowarn nolist; delete pathing_ii_w; quit; %end; proc datasets library=summary nowarn nolist; change pathing=pathing_II_W; quit; %let msg_ii_w=%unquote(&wab_note) The SUMMARY.PATHING data set %QCMPRES( has) been renamed to PATHING_II_W and date-specific PATHING %QCMPRES( data) sets have been created.; %end; %else %do; %let msg_ii_w=%unquote(&wab_note) SUMMARY library already has %QCMPRES( date-specific) PATHING data sets.; %put %unquote(&wab_note) SUMMARY library already has %QCMPRES( date-specific) PATHING data sets.; %end; %II_W_EXIT:; %******************************************************************************; %* MODULE: II.X Make sure that CONFIG.WACONFIG already has a ; %* WAB_TOP_PATH_PAGES row. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_X; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; data &temp_lib..wab_top_path_pages; set config.waconfig; if upcase(parameter_name) eq 'WAB_TOP_PATH_PAGES'; run; %if %get_observation_count(indsn=&temp_lib..wab_top_path_pages) eq 0 %then %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_x=%unquote(&wab_warning) The WaConvrt macro will add %QCMPRES( the) WAB_TOP_PATH_PAGES row to CONFIG.WACONFIG.; %goto II_X_EXIT; %end; data config.waconfig; set config.waconfig end=eof; output; if eof then do; id_val=input(strip(id),2.)+1; id=put(id_val,2.); Parameter_Name='wab_top_path_pages'; Value='2'; Description='The maximum page count for a visit to be counted ' || 'in PROC Path Top Entry/Top Referrer analyses.'; output; %*********************************************************************; %* create WAB_TOP_PATH_PAGES so that it is available for II_Y ; %*********************************************************************; call symput(parameter_name,strip(value)); end; drop id_val; run; proc datasets library=config nolist; modify waconfig; index create id / nomiss unique; quit; %let msg_ii_x=%unquote(&wab_note) The WAB_TOP_PATH_PAGES row has %QCMPRES( been) added to CONFIG.WACONFIG.; %end; %else %do; %let msg_ii_x=%unquote(&wab_note) CONFIG.WACONFIG already has a %QCMPRES( WAB_TOP_PATH_PAGES) row.; %put %unquote(&wab_note) CONFIG.WACONFIG already has a %QCMPRES( WAB_TOP_PATH_PAGES) row.; %end; %II_X_EXIT:; %******************************************************************************; %* MODULE: II.Y Create the SUMMARY.TOP_REFERRER_PATHS_HX and ; %* SUMMARY.TOP_ENTRY_PATHS_HX data sets. ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_Y; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %******************************************************************************; %* check if there are already SUMMARY.TOP_ENTRY_PATHS_HX and ; %* SUMMARY.TOP_REFERRER_PATHS_HX data sets. ; %******************************************************************************; proc sql; create table &temp_lib..pathing_hx_dsets as select memname from dictionary.tables where libname="SUMMARY" and memname in ('TOP_ENTRY_PATHS_HX','TOP_REFERRER_PATHS_HX'); quit; %if %get_observation_count(indsn=&temp_lib..pathing_hx_dsets) eq 2 %then %do; %let msg_ii_y=%unquote(&wab_note) SUMMARY library already has %QCMPRES( TOP_ENTRY_PATHS_HX) and TOP_REFERRER_PATHS_HX data sets.; %put %unquote(&wab_note) SUMMARY library already has %QCMPRES( TOP_ENTRY_PATHS_HX) and TOP_REFERRER_PATHS_HX data sets.; %end; %else %if %get_observation_count(indsn=&temp_lib..pathing_hx_dsets) eq 1 %then %do; data _null_; set &temp_lib..pathing_hx_dsets; if memname eq 'TOP_ENTRY_PATHS_HX' then missing_dset='TOP_REFERRER_PATHS_HX'; else missing_dset='TOP_ENTRY_PATHS_HX'; call symput('missing_dset',strip(missing_dset)); call symput('found_dset',strip(memname)); run; %let msg_ii_y=%unquote(&wab_error) SUMMARY library already %QCMPRES( has) &found_dset but not &missing_dset.. Please %QCMPRES( rename) -- or delete -- &found_dset and re-submit WaConvrt.; %put %unquote(&wab_error) SUMMARY library already %QCMPRES( has) &found_dset but not &missing_dset.. Please %QCMPRES( rename) -- or delete -- &found_dset and re-submit WaConvrt.; %end; %else %if %sysfunc(exist(summary.pathing_sess_count)) eq 0 %then %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_y=%unquote(&wab_warning) The WaConvrt macro will %QCMPRES( create) the PATHING_SESS_COUNT data set in the SUMMARY library.; %goto II_Y_EXIT; %end; %let msg_ii_y=%unquote(&wab_error) SUMMARY.PATHING_SESS_COUNT %QCMPRES( does) not exist. SUMMARY.TOP_ENTRY_PATHS_HX and %QCMPRES( SUMMARY.TOP_REFERRER_PATHS_HX) have not been created.; %put %unquote(&wab_error) SUMMARY.PATHING_SESS_COUNT %QCMPRES( does) not exist. SUMMARY.TOP_ENTRY_PATHS_HX and %QCMPRES( SUMMARY.TOP_REFERRER_PATHS_HX) have not been created.; %end; %else %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_y=%unquote(&wab_warning) The WaConvrt macro will %QCMPRES( create) the TOP_ENTRY_PATHS_HX and TOP_REFERRER_PATHS_HX %QCMPRES( data) sets in the SUMMARY library.; %goto II_Y_EXIT; %end; %* over-ride initial assignment ; %let step_ii_y=UPDATE; %***************************************************************************; %* create a template for TOP_ENTRY_PATHS_HX and for TOP_REFFERER_PATHS_HX ; %***************************************************************************; data summary.top_entry_paths_hx summary.top_referrer_paths_hx; length date 8 rule $ 100 item1-item&wab_itemset_size $ 9 size 8 count 8; stop; run; %***************************************************************************; %* Create the $IDX2SRL. format ; %***************************************************************************; proc format cntlin=summary.cntlin_indtosequrl_; run; %***************************************************************************; %* determine how many dates need to be passed through PROC Path to update ; %* the TOP_REFERRER_HX and TOP_ENTRY_PATH_HX data sets. These are the ; %* dates that were in the latest ETL. ; %***************************************************************************; proc sql noprint; select strip(put(max(last_dttm),best.)) into :etl_last_dttm from summary.pathing_sess_count; quit; %PUT ETL_LAST_DTTM: >>>&ETL_LAST_DTTM<<<; data _null_; set summary.pathing_sess_count end=eof; PUT LAST_DTTM= BEST.; if strip(put(last_dttm,best.)) eq strip("&etl_last_dttm") then do; extr_date_cnt+1; call symput('extr_date_' || strip(put(extr_date_cnt,3.)) ,strip(put(date,yymmddn8.)) ); end; if eof then call symput('extr_date_cnt',strip(put(extr_date_cnt,3.))); run; %PUT EXTR_DATE_CNT: >>>&EXTR_DATE_CNT<<<; %do i=1 %to &extr_date_cnt; %************************************************************************; %* TOP ENTRY PATHS ; %* NOTE: SUPPORT is set to the configurable value in the ; %* WAB_TOP_PATH_PAGES parameter and the MAX parameter in the ; %* SEQUENCE statement is hardcoded to 7 (this means that only ; %* the 1st seven requests are considered for ; %* TOP_ENTRY_PATHS_HX, which is acceptable because only paths ; %* that begin with the first request are candidates and paths ; %* are limited to 7 items) ; %************************************************************************; proc path data=summary.pathing_&&extr_date_&i (where=(sequence between 1 and 7) keep=session_id sequence date sequenced_reference ) out=&temp_lib..top_entry_paths_A&&extr_date_&i support=&waconvrt_support items=&wab_itemset_size; customer session_id; sequence sequence; target sequenced_reference; funnel out = &temp_lib..top_entry_funnels_A&&extr_date_&i; run; data &temp_lib..top_entry_paths_B&&extr_date_&i; %*********************************************************************; %* make sure that the date contains the maximum number of ITEMx ; %*********************************************************************; length item1-item&wab_itemset_size $ 9; merge &temp_lib..top_entry_paths_A&&extr_date_&i (in=in_left) &temp_lib..top_entry_funnels_A&&extr_date_&i (in=in_right); by ruleid; if in_left; run; proc sort data=&temp_lib..top_entry_paths_B&&extr_date_&i; by item1-item&wab_itemset_size; run; data &temp_lib..top_entry_paths_C&&extr_date_&i; set &temp_lib..top_entry_paths_B&&extr_date_&i; by item1-item&wab_itemset_size; array counts {1:&wab_itemset_size} count1-count&wab_itemset_size; array item {1:&wab_itemset_size} $ item1-item&wab_itemset_size; date=input("&&extr_date_&i",yymmdd8.); output; if first.item1 then do; count=count1; rule=item1; size=1; do i=2 to &wab_itemset_size; counts{i}=.; item{i}=' '; end; output; end; drop i; format date date9.; run; %************************************************************************; %* NOTE: append done with DATA Step because attrib for RULE can vary ; %************************************************************************; data &temp_lib..top_entry_paths_C&&extr_date_&i; set summary.top_entry_paths_hx &temp_lib..top_entry_paths_C&&extr_date_&i(in=in_new); length item_new $ 1037; if in_new then do; %******************************************************************; %* keep only paths that start with an entry page ; %* NOTE: the literals for entry pages have a _1 suffix ; %******************************************************************; item_new=put(strip(item1),$idx2srl.); if prxmatch('/_1$/',strip(item_new)); end; drop item_new; run; %************************************************************************; %* sum the results of each dates tally ; %************************************************************************; proc summary data=&temp_lib..top_entry_paths_C&&extr_date_&i nway; class date rule; id item1-item&wab_itemset_size size; var count; output out=summary.top_entry_paths_hx(drop=_type_ _freq_) sum=; run; %************************************************************************; %* TOP REFERRER PATHS ; %* NOTE: SUPPORT is set to the configurable value in the ; %* WAB_TOP_PATH_PAGES parameter and the MAX parameter in the ; %* SEQUENCE statement is hardcoded to 7 (this means that only ; %* the 1st seven requests are considered for ; %* TOP_REFERRER_PATHS_HX, which is acceptable because only ; %* paths that begin with the first request are candidates and ; %* paths are limited to 7 items) ; %************************************************************************; proc path data=summary.pathing_&&extr_date_&i (where=(sequence between 0 and 6) keep=session_id sequence date sequenced_reference ) out=&temp_lib..top_referrer_paths_A&&extr_date_&i support=&waconvrt_support items=&wab_itemset_size; customer session_id; sequence sequence; target sequenced_reference; funnel out = &temp_lib..top_referrer_funnels_A&&extr_date_&i; run; data &temp_lib..top_referrer_paths_B&&extr_date_&i; %*********************************************************************; %* make sure that the date contains the maximum number of ITEMx ; %*********************************************************************; length item1-item&wab_itemset_size $ 9; merge &temp_lib..top_referrer_paths_A&&extr_date_&i (in=in_left) &temp_lib..top_referrer_funnels_A&&extr_date_&i (in=in_right); by ruleid; if in_left; run; proc sort data=&temp_lib..top_referrer_paths_B&&extr_date_&i; by item1-item&wab_itemset_size; run; data &temp_lib..top_referrer_paths_C&&extr_date_&i; set &temp_lib..top_referrer_paths_B&&extr_date_&i; by item1-item&wab_itemset_size; array counts {1:&wab_itemset_size} count1-count&wab_itemset_size; array item {1:&wab_itemset_size} $ item1-item&wab_itemset_size; date=input("&&extr_date_&i",yymmdd8.); output; if first.item1 then do; count=count1; rule=item1; size=1; do i=2 to &wab_itemset_size; counts{i}=.; item{i}=' '; end; output; end; drop i; format date date9.; run; %************************************************************************; %* NOTE: append done with DATA Step because attrib for RULE can vary ; %************************************************************************; data &temp_lib..top_referrer_paths_C&&extr_date_&i; set summary.top_referrer_paths_hx &temp_lib..top_referrer_paths_C&&extr_date_&i(in=in_new); length item_new $ 1037; if in_new then do; %******************************************************************; %* keep only paths that start with a referrer ; %* NOTE: the literals for referrers have a _0 suffix ; %******************************************************************; item_new=put(strip(item1),$idx2srl.); if prxmatch('/_0$/',strip(item_new)); end; drop item_new; run; %************************************************************************; %* sum the results of each dates tally ; %************************************************************************; proc summary data=&temp_lib..top_referrer_paths_C&&extr_date_&i nway; class date rule; id item1-item&wab_itemset_size size; var count; output out=summary.top_referrer_paths_hx(drop=_type_ _freq_) sum=; run; %end; %let msg_ii_y=%unquote(&wab_note) SUMMARY library now has %QCMPRES( TOP_ENTRY_PATHS_HX) and TOP_REFERRER_PATHS_HX data sets.; %end; %II_Y_EXIT:; %******************************************************************************; %* MODULE: II.Z Create the SUMMARY.NEXT_PAGE_REFERENCE data set ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_Z; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %******************************************************************************; %* check if there is already a SUMMARY.NEXT_PAGE_REFERENCE data set ; %******************************************************************************; proc sql; create table &temp_lib..next_page_ref_dset as select memname from dictionary.tables where libname="SUMMARY" and memname in ('NEXT_PAGE_REFERENCE'); quit; %if %get_observation_count(indsn=&temp_lib..next_page_ref_dset) ne 0 %then %do; %let msg_ii_z=%unquote(&wab_note) SUMMARY library already has a %QCMPRES( NEXT_PAGE_REFERENCE) data set.; %put %unquote(&wab_note) SUMMARY library already has a %QCMPRES( NEXT_PAGE_REFERENCE) data set.; %end; %else %do; %***************************************************************************; %* SUMMARY.PAGE_FREQUENCIES and SUMMARY.PAGE_FREQUENCIES_OBSOLETE must ; %* both exist so that the highest START value from both of them can be ; %* used to initialize the value for REFERENCE in NEXT_PAGE_REFERNCE. ; %***************************************************************************; %if %sysfunc(exist(summary.page_frequencies)) eq 0 or %sysfunc(exist(summary.page_frequencies_obsolete)) eq 0 %then %do; %let msg_ii_z=%unquote(&wab_error) The WaConvrt macro cannot %QCMPRES( create) the NEXT_PAGE_REFERENCE data set in the SUMMARY %QCMPRES( library.) because the SUMMARY.PAGE_FREQUENCIES and %QCMPRES( SUMMARY.PAGE_FREQUENCIES_OBSOLETE) data sets do not exist.; %goto II_Z_EXIT; %end; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_z=%unquote(&wab_warning) The WaConvrt macro will %QCMPRES( create) the NEXT_PAGE_REFERENCE data set in the SUMMARY %QCMPRES( library.); %goto II_Z_EXIT; %end; %***************************************************************************; %* find the highest value in START (NOTE: because START is a character ; %* field, it is converted to a numeric to determine the highest ; %* value). add 1 to the highest value to create the value to be ; %* assigned to the next REFERENCE field. ; %***************************************************************************; data _null_; set summary.page_frequencies summary.page_frequencies_obsolete end=eof; retain max_ref; reference=input(start,12.); if reference gt max_ref then max_ref=reference; if eof then do; next_ref=max_ref+1; call symput('next_ref',strip(put(next_ref,12.))); end; run; %***************************************************************************; %* create SUMMARY.NEXT_PAGE_REFERENCE to provide continuity for REFERENCE ; %* assignments across ETL runs. ; %***************************************************************************; data summary.next_page_reference; reference=&next_ref; etl_dttm=datetime(); format etl_dttm datetime19.; run; %let msg_ii_z=%unquote(&wab_note) SUMMARY library now has the %QCMPRES( NEXT_PAGE_REFERENCE) data set.; %end; %II_Z_EXIT:; %******************************************************************************; %* MODULE: II.Z1 Create the RECOVERY.PAGE_INVENTORY data set ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_Z1; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %******************************************************************************; %* check if there is already a RECOVERY.PAGE_INVENTORY data set ; %******************************************************************************; proc sql; create table &temp_lib..recovery_dset as select memname from dictionary.tables where libname="RECOVERY" and memname in ('PAGE_INVENTORY'); quit; %if %get_observation_count(indsn=&temp_lib..recovery_dset) ne 0 %then %do; %let msg_ii_z1=%unquote(&wab_note) RECOVERY library already has a %QCMPRES( PAGE_INVENTORY) data set.; %put %unquote(&wab_note) RECOVERY library already has a %QCMPRES( PAGE_INVENTORY) data set.; %end; %else %do; %if %sysfunc(exist(summary.page_frequencies)) eq 0 or %sysfunc(exist(summary.page_frequencies_obsolete)) eq 0 %then %do; %let msg_ii_z1=%unquote(&wab_error) The WaConvrt macro cannot %QCMPRES( create) the PAGE_INVENTORY data set in the RECOVERY %QCMPRES( library.) because the SUMMARY.PAGE_FREQUENCIES and %QCMPRES( SUMMARY.PAGE_FREQUENCIES_OBSOLETE) data sets do not exist.; %goto II_Z1_EXIT; %end; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_z1=%unquote(&wab_warning) The WaConvrt macro will %QCMPRES( create) the PAGE_INVENTORY data set in the RECOVERY %QCMPRES( library.); %goto II_Z1_EXIT; %end; %***************************************************************************; %* Create the RECOVERY directory -- if it does not already exist ; %***************************************************************************; %let wab_recovery_dir=&swamart.&separator.data&separator.recovery; %if not %sysfunc(fileexist(&wab_recovery_dir)) %then %do; %make_directory(dirname=&wab_recovery_dir ,retcode=wab_rc); %end; %if &wab_rc ne 0 %then %do; %let msg_ii_z1=%unquote(&wab_error) The WaConvrt macro was %QCMPRES( unable) to create the RECOVERY library at &wab_recovery_dir.; %goto II_Z1_EXIT; %end; libname recovery "&wab_recovery_dir"; %***************************************************************************; %* Create the PAGE_INVENTORY data set from the PAGE_FREQUENCIES and ; %* PAGE_FREQUENCIES_OBSOLETE data sets in the SUMMARY library ; %***************************************************************************; data page_inventory (keep=requested_file reference); length requested_file $ 1024 reference 8; set summary.page_frequencies summary.page_frequencies_obsolete; requested_file=label; reference=input(start,12.); run; proc sort data=page_inventory out =recovery.page_inventory; by reference; run; %let msg_ii_z1=%unquote(&wab_note) RECOVERY library now has the %QCMPRES( PAGE_INVENTORY) data set.; %end; %II_Z1_EXIT:; %******************************************************************************; %* MODULE: II.Z2 Address problems with PATHING_yyyymmdd and TOP_x_PATHS_HX ; %* SUPPORT: frroed ; %******************************************************************************; %let module=II_Z2; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %******************************************************************************; %* check if the pathing updates need to be applied (the updates are needed ; %* only for Web marts that have already been converted) ; %* the II_Z2 pathing updates are NOT needed if the Step II_W update AND the ; %* Step II_Y update were made during this execution of WaConvrt ; %******************************************************************************; %if %upcase(&step_ii_w) eq UPDATE and %upcase(&step_ii_y) eq UPDATE %then %do; %let msg_ii_z2=%unquote(&wab_note) No pathing updates are needed %QCMPRES( for) this Web mart.; %put %unquote(&wab_note) No pathing updates are needed %QCMPRES( for) this Web mart.; %end; %else %do; %if %upcase(&mode) eq DIAGNOSE %then %do; %let msg_ii_z2=%unquote(&wab_warning) The WaConvrt macro will %QCMPRES( perform) the necessary pathing updates.; %goto II_Z2_EXIT; %end; %***************************************************************************; %* Sort all the PATHING_yyyymmdd data sets ; %***************************************************************************; %* Identify the unsorted SUMMARY.PATHING_20yymmdd data sets ; %***************************************************************************; proc sql; create table &temp_lib..summary_pathing_yyyymmdd_dsets as select libname ,memname ,sorttype from dictionary.tables where libname eq 'SUMMARY' and index(memname,'PATHING_200') and sorttype ne 'S'; quit; %***************************************************************************; %* Create a macro variable for each unsorted SUMMARY.PATHING_20yymmdd data ; %* set. ; %***************************************************************************; %let pathing_ymd_cnt=0; data _null_; set &temp_lib..summary_pathing_yyyymmdd_dsets end=eof; call symput('pathing_ymd_' || strip(put(_n_,2.)) ,strip(memname) ); if eof then call symput('pathing_ymd_cnt' ,strip(put(_n_,2.)) ); run; %***************************************************************************; %* Sort each unsorted SUMMARY.PATHING_20yymmdd data set ; %***************************************************************************; %do i=1 %to &pathing_ymd_cnt; proc sort data=summary.&&pathing_ymd_&i; by session_id sequence; run; %end; %***************************************************************************; %* Re-create the TOP_x_PATHS_HX data sets ; %***************************************************************************; %* Create the $IDX2SRL. format ; %***************************************************************************; proc format cntlin=summary.cntlin_indtosequrl_; run; %***************************************************************************; %* create a template for TOP_ENTRY_PATHS_HX and for TOP_REFFERER_PATHS_HX ; %***************************************************************************; data summary.top_entry_paths_hx summary.top_referrer_paths_hx; length date 8 rule $ 100 item1-item&wab_itemset_size $ 9 size 8 count 8; stop; run; %***************************************************************************; %* determine how many dates need to be passed through PROC Path to rebuild ; %* the TOP_REFERRER_HX and TOP_ENTRY_PATH_HX data sets. ; %***************************************************************************; data _null_; set summary.pathing_sess_count end=eof; call symput('hx_date_' || strip(put(_n_,3.)) ,strip(put(date,yymmddn8.)) ); if eof then call symput('hx_date_cnt',strip(put(_n_,3.))); run; %do i=1 %to &hx_date_cnt; %************************************************************************; %* TOP ENTRY PATHS ; %* NOTE: SUPPORT is set to the configurable value in the ; %* WAB_TOP_PATH_PAGES parameter and the MAX parameter in the ; %* SEQUENCE statement is hardcoded to 7 (this means that only ; %* the 1st seven requests are considered for ; %* TOP_ENTRY_PATHS_HX, which is acceptable because only paths ; %* that begin with the first request are candidates and paths ; %* are limited to 7 items) ; %************************************************************************; proc path data=summary.pathing_&&hx_date_&i (where=(sequence between 1 and 7) keep=session_id sequence date sequenced_reference ) out=&temp_lib..top_entry_paths_A&&hx_date_&i support=&waconvrt_support items=&wab_itemset_size; customer session_id; sequence sequence; target sequenced_reference; funnel out = &temp_lib..top_entry_funnels_A&&hx_date_&i; run; data &temp_lib..top_entry_paths_B&&hx_date_&i; %*********************************************************************; %* make sure that the date contains the maximum number of ITEMx ; %*********************************************************************; length item1-item&wab_itemset_size $ 9; merge &temp_lib..top_entry_paths_A&&hx_date_&i (in=in_left) &temp_lib..top_entry_funnels_A&&hx_date_&i (in=in_right); by ruleid; if in_left; run; proc sort data=&temp_lib..top_entry_paths_B&&hx_date_&i; by item1-item&wab_itemset_size; run; data &temp_lib..top_entry_paths_C&&hx_date_&i; set &temp_lib..top_entry_paths_B&&hx_date_&i; by item1-item&wab_itemset_size; array counts {1:&wab_itemset_size} count1-count&wab_itemset_size; array item {1:&wab_itemset_size} $ item1-item&wab_itemset_size; date=input("&&hx_date_&i",yymmdd8.); output; if first.item1 then do; count=count1; rule=item1; size=1; do i=2 to &wab_itemset_size; counts{i}=.; item{i}=' '; end; output; end; drop i; format date date9.; run; %************************************************************************; %* NOTE: append done with DATA Step because attrib for RULE can vary ; %************************************************************************; data &temp_lib..top_entry_paths_C&&hx_date_&i; set summary.top_entry_paths_hx &temp_lib..top_entry_paths_C&&hx_date_&i (in=in_new); length item_new $ 1037; if in_new then do; %******************************************************************; %* keep only paths that start with an entry page ; %* NOTE: the literals for entry pages have a _1 suffix ; %******************************************************************; item_new=put(strip(item1),$idx2srl.); if prxmatch('/_1$/',strip(item_new)); end; drop item_new; run; %************************************************************************; %* sum the results of each dates tally ; %************************************************************************; proc summary data=&temp_lib..top_entry_paths_C&&hx_date_&i nway; class date rule; id item1-item&wab_itemset_size size; var count; output out=summary.top_entry_paths_hx(drop=_type_ _freq_) sum=; run; %************************************************************************; %* TOP REFERRER PATHS ; %* NOTE: SUPPORT is set to the configurable value in the ; %* WAB_TOP_PATH_PAGES parameter and the MAX parameter in the ; %* SEQUENCE statement is hardcoded to 6 (this means that only ; %* the Referrer plus the 1st six requests are considered for ; %* TOP_REFERRER_PATHS_HX, which is acceptable because only ; %* paths that begin with a referrer are candidates and paths ; %* are limited to 7 items) ; %************************************************************************; proc path data=summary.pathing_&&hx_date_&i (where=(sequence between 0 and 6) keep=session_id sequence date sequenced_reference ) out=&temp_lib..top_referrer_paths_A&&hx_date_&i support=&waconvrt_support items=&wab_itemset_size; customer session_id; sequence sequence; target sequenced_reference; funnel out = &temp_lib..top_referrer_funnels_A&&hx_date_&i; run; data &temp_lib..top_referrer_paths_B&&hx_date_&i; %*********************************************************************; %* make sure that the date contains the maximum number of ITEMx ; %*********************************************************************; length item1-item&wab_itemset_size $ 9; merge &temp_lib..top_referrer_paths_A&&hx_date_&i (in=in_left) &temp_lib..top_referrer_funnels_A&&hx_date_&i (in=in_right); by ruleid; if in_left; run; proc sort data=&temp_lib..top_referrer_paths_B&&hx_date_&i; by item1-item&wab_itemset_size; run; data &temp_lib..top_referrer_paths_C&&hx_date_&i; set &temp_lib..top_referrer_paths_B&&hx_date_&i; by item1-item&wab_itemset_size; array counts {1:&wab_itemset_size} count1-count&wab_itemset_size; array item {1:&wab_itemset_size} $ item1-item&wab_itemset_size; date=input("&&hx_date_&i",yymmdd8.); output; if first.item1 then do; count=count1; rule=item1; size=1; do i=2 to &wab_itemset_size; counts{i}=.; item{i}=' '; end; output; end; drop i; format date date9.; run; %************************************************************************; %* NOTE: append done with DATA Step because attrib for RULE can vary ; %************************************************************************; data &temp_lib..top_referrer_paths_C&&hx_date_&i; set summary.top_referrer_paths_hx &temp_lib..top_referrer_paths_C&&hx_date_&i (in=in_new); length item_new $ 1037; if in_new then do; %******************************************************************; %* keep only paths that start with a referrer ; %* NOTE: the literals for referrers have a _0 suffix ; %******************************************************************; item_new=put(strip(item1),$idx2srl.); if prxmatch('/_0$/',strip(item_new)); end; drop item_new; run; %************************************************************************; %* sum the results of each dates tally ; %************************************************************************; proc summary data=&temp_lib..top_referrer_paths_C&&hx_date_&i nway; class date rule; id item1-item&wab_itemset_size size; var count; output out=summary.top_referrer_paths_hx(drop=_type_ _freq_) sum=; run; %end; %***************************************************************************; %* regenerate the TOP_ENTRY_PATHS_Rx and TOP_REFERRER_PATHS_Rx data sets ; %***************************************************************************; %wapathdp(swamart=&swamart ,waetl_sw=N ,support_r1=&wab_r1_support ,support_r7=&wab_r7_support ,support_r30=&wab_r30_support ,retcode=wab_rc ); %***************************************************************************; %* re-assign the module name to MACNAME ; %***************************************************************************; %let macname=%scan(&macname,1,_)_&module; %let msg_ii_z2=%unquote(&wab_note) The necessary PATHING updates %QCMPRES( have) been completed.; %end; %II_Z2_EXIT:; %******************************************************************************; %* Display the messages that summarize the actions that %WACONVRT took ; %******************************************************************************; %let macname=&sysmacroname; %put; %put; %put; %put; %put; %if %upcase(&mode) eq DIAGNOSE %then %do; %put %unquote(&wab_note)------------ SUMMARY OF WACONVRT DIAGNOSIS %QCMPRES( --------------); %put; %put %unquote(&wab_note) This is a list of updates that the %QCMPRES( WaConvrt) macro will make if you re-submit it with Mode=UPDATE.; %end; %else %do; %put %unquote(&wab_note)-------------- SUMMARY OF WACONVRT UPDATES %QCMPRES( --------------); %put; %put %unquote(&wab_note) This is a list of updates that the %QCMPRES( WaConvrt) macro attempted to make to this Web mart with an %QCMPRES( indication) of whether the update was successful.; %put; %put %unquote(&wab_note) If WaConvrt successfully made the %QCMPRES( update), the list contains a NOTE: message.; %put; %put %unquote(&wab_note) If WaConvrt could NOT make the update, %QCMPRES( the) list contains an ERROR: message and a brief explanation of %QCMPRES( why) WaConvrt could not make the update.; %put; %put %unquote(&wab_note) Updates that have an ERROR: message will %QCMPRES( need) to be updated manually before the post-HotFix Web Analytics %QCMPRES( software) can be used for the Web mart.; %end; %put; %put; %put; %put &msg_ii_a; %put; %put &msg_ii_b; %put; %put &msg_ii_c; %put; %put &msg_ii_d; %put; %put &msg_ii_e; %put; %put &msg_ii_f; %put; %put &msg_ii_g; %put; %put &msg_ii_h; %put; %put &msg_ii_i; %put; %if &j1_cnt gt 0 %then %do; %put &msg_ii_j1; %end; %if &j2_cnt gt 0 %then %do; %put &msg_ii_j2; %end; %put; %put &msg_ii_k; %put; %put &msg_ii_l; %put; %put &msg_ii_m; %put; %put &msg_ii_n; %put; %put &msg_ii_o; %put; %put &msg_ii_p; %put; %put &msg_ii_q1; %if &q2_cnt gt 0 %then %do; %put &msg_ii_q2; %end; %if &q3_cnt gt 0 %then %do; %put &msg_ii_q3; %end; %put; %put &msg_ii_r; %put; %put &msg_ii_s; %do i=1 %to &msg_ii_s_cnt; %put -- &&msg_ii_s_&i; %end; %put; %put &msg_ii_t; %put; %put &msg_ii_u; %put; %put &msg_ii_v; %put; %put &msg_ii_w; %put; %put &msg_ii_x; %put; %put &msg_ii_y; %put; %put &msg_ii_z; %put; %put &msg_ii_z1; %put; %put &msg_ii_z2; %put; %put; %put; %put; %put; %put %unquote(&wab_note) WACONVRT ending.; %WACONVERT_END:; dm 'af catalog=SASHELP.WAGUI.WATRM.SCL'; %mend waconvrt;