%******************************************************************************; %* Copyright (C) 2004 by SAS Institute Inc., Cary, NC 27512-8000 ; %* ; %* Name: Waetl ; %* ; %* Purpose: Initialize or Build and Update the Web Analytics Web Mart ; %* ; %* Parameters: ; %* ; %* name: Specifies the name of the Web Analytics and e-Data ETL ; %* web mart. ; %* ; %* When program=warehouse ; %* If name is specified it is used to identify ; %* the ETL data store location in the SASUSER.WBINIT data set; %* Once the data store has been identified, a search for ; %* the default detail data set(i.e. weblog_detail_1) ; %* is initiated. If successful in locating detail the ; %* warehouse build process is started. Name is also used to ; %* identify the Web Analytics Web Mart(i.e. swamart) location; %* in the SASUSER.WAINIT data set. ; %* ; %* When program=initialize ; %* name is stored in the SASUSER.WBINIT for the purpose of ; %* identifying the e-Data ETL data store. It is also stored ; %* in the SASUSER.WAINIT data set for the purpose of ; %* identifying the Web Analytics Web Mart(i.e. swamart). ; %* ; %* data_store: Specifies the location of the e-Data ETL web mart. ; %* ; %* When program=warehouse ; %* This parameter is used to specify the location of the; %* e-Data ETL web mart. Then the default detail data ; %* set is searched. If the default data set is located ; %* then the warehouse build process is started. ; %* data store overrides the specification name but does ; %* not override the specification of detail. ; %* ; %* detail: Specifies the detail data set to be processed. ; %* ; %* When program=warehouse ; %* This value is used to locate the detail data set. ; %* If it exists then proceed with the warehouse build. ; %* ; %* temp_store: Specifies a location for temporary data sets. ; %* ; %* If this location is not specified or does not exist ; %* then the work library location is used for temporary; %* files and data sets. ; %* ; %******************************************************************************; %macro waetl(name= ,data_store= ,temp_store= ,detail= ,swamart= ,wapathdp_sw=Y ,program=); %******************************************************************************; %* SECTION: Preamble ; %* Compile the Web Analytics Utility Macros ; %******************************************************************************; %wautils; %******************************************************************************; %* SECTION: I - Preliminary ; %******************************************************************************; %******************************************************************************; %* MODULE: I_A - Environment Configuration ; %* SUPPORT:saskxs ; %* Declare and Initialize the necessary macro variables and determine the OS. ; %******************************************************************************; %local macname; %let macname=&sysmacroname; %let module=I_A; %let macname=&macname._&module; %put macname: >>>&macname<<<; %******************************************************************************; %* Create global macro variables ; %******************************************************************************; %global _wab_rc_; %let _wab_rc_=0; %******************************************************************************; %* Create and initialize local macro variables ; %******************************************************************************; %local wab_rc; %local wab_note wab_warning wab_error wab_iter; %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.))); %local wab_program; %let wab_program=; %******************************************************************************; %* Validate the WAPATHDP_SW parameter ; %******************************************************************************; %if %upcase(&wapathdp_sw) ne Y and %upcase(&wapathdp_sw) ne N %then %do; %put %unquote(&wab_error) The WAPATHDP_SW that you are using %QCMPRES( --) &wapathdp_sw -- is not valid. It must be Y or N.; %put %unquote(&wab_error) Web Analytics program exiting due to error.; %goto EXIT; %end; %******************************************************************************; %* If the program parameter is specified then compress and lowcase its value ; %******************************************************************************; %if %quote(&program) ne %then %do; %let wab_program=%sysfunc(compress(%sysfunc(lowcase(&program)))); %end; %******************************************************************************; %* If the swa_mart or data_store parameters are specified then translate quotes; %* to blanks ; %******************************************************************************; %if %quote(&swamart) ne %then %do; %let swamart=%sysfunc(strip(%sysfunc(translate(&swamart," ","'")))); %let swamart=%sysfunc(strip(%sysfunc(translate(&swamart,' ','"')))); %end; %if %quote(&data_store) ne %then %do; %let data_store=%sysfunc(strip(%sysfunc(translate(&data_store," ","'")))); %let data_store=%sysfunc(strip(%sysfunc(translate(&data_store,' ','"')))); %end; %******************************************************************************; %* Create local macro variable for the data set that contains the most recent ; %* date. ; %******************************************************************************; %local wab_datecheck_dsn; %******************************************************************************; %* Determine the Operating System ; %******************************************************************************; dm 'af catalog=SASHELP.WAGUI.WAINI.SCL'; %Current_OS; %if &_wab_rc_ ne 0 %then %goto ERREXIT; %******************************************************************************; %* Declare and Initialize the proc summary file count. ; %******************************************************************************; %local wab_summary_count; %let wab_summary_count=0; %******************************************************************************; %* Initialize the minimum date to keep in the daily visitor table to missing. ; %* This will prevent any subsetting of the table in case it is not initialized ; %* to keep the most current number of days in history as specified by ; %* wab_visitor_days_in_history. ; %******************************************************************************; data _null_; call symput("_wab_visitor_min_date_",".z"); run; %******************************************************************************; %* Initialize global and local return codes. ; %******************************************************************************; %if %quote(&_wab_rc_)= %then %do; %let _wab_rc_=0; %end; %if %quote(&wab_rc)= %then %do; %let wab_rc=0; %end; %******************************************************************************; %* Save system options to be restored upon exit ; %******************************************************************************; %let notes_setting = %sysfunc(getoption(NOTES)); %let source_setting = %sysfunc(getoption(SOURCE)); %let fmterr_setting = %sysfunc(getoption(FMTERR)); %let fmtsearch_setting= %sysfunc(getoption(FMTSEARCH)); %let source2_setting = %sysfunc(getoption(SOURCE2)); %let mprint_setting = %sysfunc(getoption(MPRINT)); %let compress_setting = %sysfunc(getoption(COMPRESS)); %let ovp_setting = %sysfunc(getoption(OVP)); %let obs_setting = %sysfunc(getoption(OBS)); %if x&obs_setting eq x9223372036854775807 %then %let obs_setting = MAX; options compress=yes noovp obs=max nofmterr; %No_Notes; %* Turn off notes to start; %Maybe_Notes; %* Enable notes if not Quiet; %******************************************************************************; %* Print out version information and memory option information. ; %******************************************************************************; %put %unquote(&wab_note) %qcmpres(Starting Web Analytics running on SAS &sysvlong.); %put %unquote(&wab_note) %qcmpres( Options memsize %sysfunc(getoption(memsize))%str(,) sumsize %sysfunc(getoption(sumsize))%str(,) sortsize %sysfunc(getoption(sortsize))).; %******************************************************************************; %* If the location and name of the Web Analytics Web Mart are not specified ; %* fail. ; %******************************************************************************; %if %quote(&swamart)= and %quote(&name)= %then %do; %put %unquote(&wab_error) Both the %str(%")swamart%str(%") %QCMPRES( parameter)and the %str(%")name%str(%")%QCMPRES( parameter) were not provided. One of them must be specified %QCMPRES( in) a call to WAETL.; %goto ERREXIT; %end; %******************************************************************************; %* MODULE: I_B - Web Analytics Web Mart Initialization ; %* SUPPORT:saskxs ; %* Create the directory structure for the Web Analytics Web Mart. ; %* Copy necessary files to the configuration directory. ; %******************************************************************************; %let module=I_B; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %let insert_name= ; %if %quote(&wab_program)=initialize %then %do; %***************************************************************************; %* If the Web Analytics Web Mart location was not specified then fail. ; %***************************************************************************; %if %quote(&swamart)= %then %do; %put %unquote(&wab_error) The %str(%")swamart%str(%") %QCMPRES( parameter) was not provided and must be specified.; %goto ERREXIT; %end; %***************************************************************************; %* Get the name of the Web Analytics Web Mart either from the name parameter; %* or from the name of the deepest subfolder in the swamart path. ; %***************************************************************************; data _null_; %if %quote(&name)= %then %do; call symput("_wab_swaname_" ,reverse(scan(reverse(strip("&swamart")) ,1 ,"&separator"))); %end; %else %do; call symput("_wab_swaname_" ,strip("&name")); %end; run; %***************************************************************************; %* If the name of the Web Analytics Web Mart contains only whitespace then ; %* issue an error and exit Web Analytics. ; %***************************************************************************; %if %quote(&_wab_swaname_)= %then %do; %put %unquote(&wab_error) The name for the Web Analytics %QCMPRES( could) Mart contains only whitespace. Please %QCMPRES( specify) the %str(%")name%str(%") parameter.; %goto ERREXIT; %end; %***************************************************************************; %* SASUSER.WBINIT ; %***************************************************************************; %* If it does NOT exist: ; %* - create it ; %* - seed it with the current name, web mart location, and description. ; %***************************************************************************; %if %sysfunc(exist(sasuser.wbinit))=0 %then %do; data sasuser.wbinit; attrib Name length=$32 label="Webmart Name" Desc length=$200 label="Description" Perm length=$200 label="Webmart Location" Flag length=3 label="Internal use" ; Name="&_wab_swaname_"; Desc="Created by SAS Web-Analytics"; Perm="&swamart.&separator.e-data-etl"; Flag=1; run; %end; %***************************************************************************; %* If it does exist: ; %* - if it cannot be opened fail WAETL ; %* - if it does not have any obs, create one and seed it with the ; %* current name, web mart location, and description ; %* - else if _WAB_SWANAME_ is in the NAME field for one of the obs: ; %* - if SWAMART path exists fail WAETL ; %* - if SWAMART path equals PERM path issue warning ; %* - create an obs and seed it with the SWANAME (+ suffix), ; %* web mart location, and description ; %* - else (_WAB_SWANAME_ is NOT in the NAME field for one of the obs): ; %* - create DATA_STORE to identify the path ; %* - if the path exists fail WAETL ; %* - else create an obs and seed it with the current name, web mart ; %* location, and description ; %***************************************************************************; %else %do; %* - if it cannot be opened fail WAETL ; %locktest(dset=sasuser.wbinit); %if &lock_sw ne 0 %then %do; %put %unquote(&wab_error) There was a failure opening the %QCMPRES( SASUSER.WBINIT) dataset.; %goto ERREXIT; %end; %else %if %get_observation_count(indsn=sasuser.wbinit)=0 %then %do; %* - if it does not have any obs, create one and seed it with the ; %* current name, web mart location, and description ; data sasuser.wbinit; attrib Name length=$32 label="Webmart Name" Desc length=$200 label="Description" Perm length=$200 label="Webmart Location" Flag length=3 label="Internal use" ; Name="&_wab_swaname_"; Desc="Created by SAS Web-Analytics"; Perm="&swamart.&separator.e-data-etl"; Flag=1; run; %end; %else %do; %* - if _WAB_SWANAME_ is in the NAME field for one of the obs: ; data subset; set sasuser.wbinit (where=(upcase(name) eq upcase("&_wab_swaname_"))); run; data _null_; set sasuser.wbinit end=eof; if upcase(name) eq upcase("&_wab_swaname_") then do; numobs+1; call symput('data_store',strip(perm)); end; if eof then do; call symput('numobs',strip(put(numobs,5.))); end; run; %if &numobs ge 1 %then %do; %* &_WAB_SWANAME_ is already in WBINIT -- check if PERM exists ; %let new_path=&swamart&separator.e-data-etl; %if %sysfunc(fileexist(&new_path)) %then %do; %* if the path identified by &NEW_PATH exists fail WAETL ; %put %unquote(&wab_error) The SWAMART location that you %QCMPRES( have) specified already exists. To initialize a Web %QCMPRES( mart), please specify another location.; %goto ERREXIT; %end; %if %quote(%upcase(&new_path)) eq %quote(%upcase(&data_store)) %then %do; %* if SWAMART path equals PERM path issue WARNING ; %put %unquote(&wab_warning) SASUSER.WBINIT already %QCMPRES( has) an entry for &_wab_swaname_ at %QCMPRES( &data_store). Another entry will be specified for %QCMPRES( this) location.; %end; %* identify the next usable suffix for &_WAB_SWANAME_ ; data suffix_list (keep=name); set sasuser.wbinit; name=upcase(name); run; proc sort data=suffix_list (where=(index(upcase(name),upcase("&_wab_swaname_")))); by name; run; %if %get_observation_count(indsn=suffix_list) eq 0 %then %do; %let insert_name=&_wab_swaname_; %end; %else %do; data _null_; set suffix_list end=eof; length new_suffix $ 2; if eof then do; new_suffix=tranwrd(strip(upcase(name)) ,strip(upcase("&_wab_swaname_")) ,' ' ); new_suffix=strip(new_suffix); if new_suffix eq ' ' or new_suffix ge 'A' then new_suffix='1'; else new_suffix=put((input(new_suffix,2.)+1),2.); call symput('insert_name' ,strip("&_wab_swaname_") || strip(new_suffix) ); end; run; %put %unquote(&wab_warning) SASUSER.WBINIT already %QCMPRES( has) an entry for &_wab_swaname_ -- its e-Data-ETL %QCMPRES( root) is at &data_store.. WAETL will refer to %QCMPRES( your) new Web mart as &insert_name to distinguish it %QCMPRES( from) its predecessors.; %end; %* else create an obs and seed it with the curr name (+ suffix), ; %* web mart location, and description ; data sasuser.wbinit; set sasuser.wbinit end=eof; output; if eof then do; Name="&insert_name"; Desc="Created by SAS Web Analytics"; Perm="&swamart.&separator.e-data-etl"; Flag=1; output; end; run; %end; %else %do; %* &_WAB_SWANAME_ is NOT in the NAME field for one of the obs: ; %let data_store=&swamart.&separator.e-data-etl; %if %sysfunc(fileexist(&data_store)) %then %do; %* if the path exists fail WAETL ; %put %unquote(&wab_error) SASUSER.WBINIT already has an %QCMPRES( entry) for the location you have specified %QCMPRES( --) &data_store.. To initialize a Web mart, please %QCMPRES( specify) another location.; %goto ERREXIT; %end; %* else create an obs and seed it with the current name, web mart ; %* location, and description ; data sasuser.wbinit; set sasuser.wbinit end=eof; output; if eof then do; Name="&_wab_swaname_"; Desc="Created by SAS Web Analytics"; Perm="&swamart.&separator.e-data-etl"; Flag=1; output; end; run; %end; %end; %end; %***************************************************************************; %* SASUSER.WAINIT ; %***************************************************************************; %* If it does NOT exist: ; %* - create it ; %* - seed it with the current name, web mart location, and description. ; %***************************************************************************; %if %sysfunc(exist(sasuser.wainit))=0 %then %do; data sasuser.wainit; attrib Name length=$32 label="Webmart Name" Desc length=$200 label="Description" Perm length=$200 label="Webmart Location" Flag length=3 label="Internal use" ; Name="&_wab_swaname_"; Desc="Created by SAS Web-Analytics"; Perm="&swamart.&separator.e-data-etl"; Flag=1; run; %end; %***************************************************************************; %* If it does exist: ; %* - if it cannot be opened fail WAETL ; %* - if it does not have any obs, create one and seed it with the ; %* current name, web mart location, and description ; %* - else if _WAB_SWANAME_ is in the NAME field for one of the obs: ; %* - if SWAMART path exists fail WAETL ; %* - if SWAMART path equals PERM path issue warning ; %* - create an obs and seed it with the SWANAME (+ suffix), ; %* web mart location, and description ; %* - else (_WAB_SWANAME_ is NOT in the NAME field for one of the obs): ; %* - create DATA_STORE to identify the path ; %* - if the path exists fail WAETL ; %* - else create an obs and seed it with the current name, web mart ; %* location, and description ; %***************************************************************************; %else %do; %* - if it cannot be opened fail WAETL ; %locktest(dset=sasuser.wainit); %if &lock_sw ne 0 %then %do; %put %unquote(&wab_error) There was a failure opening the %QCMPRES( SASUSER.WAINIT) dataset.; %goto ERREXIT; %end; %else %if %get_observation_count(indsn=sasuser.wainit)=0 %then %do; %* - if it does not have any obs, create one and seed it with the ; %* current name, web mart location, and description ; data sasuser.wainit; attrib Name length=$32 label="Webmart Name" Desc length=$200 label="Description" Perm length=$200 label="Webmart Location" Flag length=3 label="Internal use" ; Name="&_wab_swaname_"; Desc="Created by SAS Web-Analytics"; Perm="&swamart.&separator.e-data-etl"; Flag=1; run; %end; %else %do; %* - if _WAB_SWANAME_ is in the NAME field for one of the obs: ; data subset; set sasuser.wainit (where=(upcase(name) eq upcase("&_wab_swaname_"))); run; data _null_; set sasuser.wainit end=eof; if upcase(name) eq upcase("&_wab_swaname_") then do; numobs+1; call symput('data_store',strip(perm)); end; if eof then do; call symput('numobs',strip(put(numobs,5.))); end; run; %if &numobs ge 1 %then %do; %* &_WAB_SWANAME_ is already in WAINIT -- check if PERM exists ; %let new_path=&swamart&separator.e-data-etl; %if %sysfunc(fileexist(&new_path)) %then %do; %* if the path identified by &NEW_PATH exists fail WAETL ; %put %unquote(&wab_error) The SWAMART location that you %QCMPRES( have) specified already exists. To initialize a Web %QCMPRES( mart), please specify another location.; %goto ERREXIT; %end; %if %quote(%upcase(&new_path)) eq %quote(%upcase(&data_store)) %then %do; %* if SWAMART path equals PERM path issue WARNING ; %put %unquote(&wab_warning) SASUSER.WAINIT already %QCMPRES( has) an entry for &_wab_swaname_ at %QCMPRES( &data_store). Another entry will be specified for %QCMPRES( this) location.; %end; %* identify the next usable suffix for &_WAB_SWANAME_ ; data suffix_list (keep=name); set sasuser.wainit; name=upcase(name); run; proc sort data=suffix_list (where=(index(upcase(name),upcase("&_wab_swaname_")))); by name; run; %if %get_observation_count(indsn=suffix_list) eq 0 %then %do; %let insert_name=&_wab_swaname_; %end; %else %do; data _null_; set suffix_list end=eof; length new_suffix $ 2; if eof then do; new_suffix=tranwrd(strip(upcase(name)) ,strip(upcase("&_wab_swaname_")) ,' ' ); new_suffix=strip(new_suffix); if new_suffix eq ' ' or new_suffix ge 'A' then new_suffix='1'; else new_suffix=put((input(new_suffix,2.)+1),2.); call symput('insert_name' ,strip("&_wab_swaname_") || strip(new_suffix) ); end; run; %put %unquote(&wab_warning) SASUSER.WAINIT already %QCMPRES( has) an entry for &_wab_swaname_ -- its e-Data-ETL %QCMPRES( root) is at &data_store.. WAETL will refer to %QCMPRES( your) new Web mart as &insert_name to distinguish it %QCMPRES( from) its predecessors.; %end; %* else create an obs and seed it with the curr name (+ suffix), ; %* web mart location, and description ; data sasuser.wainit; set sasuser.wainit end=eof; output; if eof then do; Name="&insert_name"; Desc="Created by SAS Web Analytics"; Perm="&swamart.&separator.e-data-etl"; Flag=1; output; end; run; %end; %else %do; %* &_WAB_SWANAME_ is NOT in the NAME field for one of the obs: ; %let data_store=&swamart.&separator.e-data-etl; %if %sysfunc(fileexist(&data_store)) %then %do; %* if the path exists fail WAETL ; %put %unquote(&wab_error) SASUSER.WAINIT already has an %QCMPRES( entry) for the location you have specified %QCMPRES( --) &data_store.. To initialize a Web mart, please %QCMPRES( specify) another location.; %goto ERREXIT; %end; %* else create an obs and seed it with the current name, web mart ; %* location, and description ; data sasuser.wainit; set sasuser.wainit end=eof; output; if eof then do; Name="&_wab_swaname_"; Desc="Created by SAS Web Analytics"; Perm="&swamart.&separator.e-data-etl"; Flag=1; output; end; run; %end; %end; %end; %***************************************************************************; %* Build the new location ; %***************************************************************************; %make_directory(dirname=&swamart ,retcode=wab_rc); %if &wab_rc ne 0 %then %goto ERREXIT; %***************************************************************************; %* Create local macro variables to contain directory structure. ; %***************************************************************************; %local wab_config_dir wab_logs_dir wab_sas_dir wab_data_dir wab_dated_dir wab_detl_dir wab_etl_dir wab_doc_dir wab_stp_dir wab_smy_dir ; %let wab_config_dir=&swamart.&separator.config; %let wab_logs_dir=&swamart.&separator.logs; %let wab_sas_dir=&swamart.&separator.sas; %let wab_data_dir=&swamart.&separator.data; %let wab_dated_dir=&swamart.&separator.data&separator.dated; %let wab_detl_dir=&swamart.&separator.data&separator.detail; %let wab_etl_dir=&swamart.&separator.e-data-etl; %let wab_doc_dir=&swamart.&separator.data&separator.documents; %let wab_stp_dir=&swamart.&separator.data&separator.stp; %let wab_smy_dir=&swamart.&separator.data&separator.summary; %let wab_recovery_dir=&swamart.&separator.data&separator.recovery; %***************************************************************************; %* Create the directory structure for Web Analytics. ; %***************************************************************************; %if not %sysfunc(fileexist(&wab_config_dir)) %then %do; %make_directory(dirname=&wab_config_dir ,retcode=wab_rc); %end; %if &wab_rc ne 0 %then %goto ERREXIT; %if not %sysfunc(fileexist(&wab_logs_dir)) %then %do; %make_directory(dirname=&wab_logs_dir ,retcode=wab_rc); %end; %if &wab_rc ne 0 %then %goto ERREXIT; %if not %sysfunc(fileexist(&wab_sas_dir)) %then %do; %make_directory(dirname=&wab_sas_dir ,retcode=wab_rc); %end; %if &wab_rc ne 0 %then %goto ERREXIT; %if not %sysfunc(fileexist(&wab_data_dir)) %then %do; %make_directory(dirname=&wab_data_dir ,retcode=wab_rc); %end; %if &wab_rc ne 0 %then %goto ERREXIT; %if not %sysfunc(fileexist(&wab_dated_dir)) %then %do; %make_directory(dirname=&wab_dated_dir ,retcode=wab_rc); %end; %if &wab_rc ne 0 %then %goto ERREXIT; %if not %sysfunc(fileexist(&wab_detl_dir)) %then %do; %make_directory(dirname=&wab_detl_dir ,retcode=wab_rc); %end; %if &wab_rc ne 0 %then %goto ERREXIT; %if not %sysfunc(fileexist(&wab_etl_dir)) %then %do; %make_directory(dirname=&wab_etl_dir ,retcode=wab_rc); %end; %if &wab_rc ne 0 %then %goto ERREXIT; %if not %sysfunc(fileexist(&wab_doc_dir)) %then %do; %make_directory(dirname=&wab_doc_dir ,retcode=wab_rc); %end; %if &wab_rc ne 0 %then %goto ERREXIT; %if not %sysfunc(fileexist(&wab_stp_dir)) %then %do; %make_directory(dirname=&wab_stp_dir ,retcode=wab_rc); %end; %if &wab_rc ne 0 %then %goto ERREXIT; %if not %sysfunc(fileexist(&wab_smy_dir)) %then %do; %make_directory(dirname=&wab_smy_dir ,retcode=wab_rc); %end; %if &wab_rc ne 0 %then %goto ERREXIT; %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 %goto ERREXIT; libname recovery "&wab_recovery_dir"; data recovery.page_inventory; length requested_file $ 1024 reference 8; stop; run; %***************************************************************************; %* Assign the Web Mart config library and move the necessary metadata to ; %* the config library location. If the copy fails then exit with error. ; %***************************************************************************; libname config "&wab_config_dir"; proc copy in=sashelp out=config memtype=data; select waadmsum waautinp waautosg waconfig wadshbrd wadshinp waengvar wafunnel wapath waresrce wascrcrd wascrinp watbldsc ; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) WAADMSUM, WAAUTINP, WAAUTOSG, %QCMPRES( WACONFIG), WADSHBRD, WADSHINP, WAENGVAR, WAFUNNEL, %QCMPRES( WAPATH), WARESRCE, WASCRCRD, WASCRINP, or %QCMPRES( WATBLDSC) could not be copied into CONFIG.; %goto ERREXIT; %end; %***************************************************************************; %* Assign the Web Mart summary library and create the NEXT_SESS_ID and the ; %* NEXT_PAGE_REFERENCE datasets ; %***************************************************************************; libname summary "&wab_smy_dir"; data summary.next_sess_id (keep=session_id etl_dttm ); session_id=1; etl_dttm=datetime(); format etl_dttm datetime19.; run; data summary.next_page_reference; reference=1; etl_dttm=datetime(); format etl_dttm datetime19.; run; libname summary clear; %***************************************************************************; %* Initialize the e-Data ETL environment. ; %***************************************************************************; %webhound(data_store=&swamart.&separator.e-data-etl); %***************************************************************************; %* Create the standard .sas programs in the Web marts \sas sub-directory ; %***************************************************************************; %wasasdir(retcode=wab_rc); %if &wab_rc ne 0 %then %goto ERREXIT; %***************************************************************************; %* Everything OK go to exit. ; %***************************************************************************; %if &insert_name gt %then %do; %let rpt_name=%quote(&insert_name); %end; %else %do; %let rpt_name=%quote(&_wab_swaname_); %end; %put %unquote(&wab_note) The Web Analytics Web Mart %QCMPRES( &rpt_name) at location &swamart was %QCMPRES( successfully) initialized. Please configure e-Data ETL.; %goto EXIT; %end; %***************************************************************************; %* If an invalid value for the program parameter was passed then issue an ; %* error and fail. Valid values are initialize and warehouse. ; %***************************************************************************; %else %if %quote(&wab_program) ne warehouse %then %do; %put %unquote(&wab_error) You must specify a program parameter %QCMPRES( for %str(%)WAETL) to execute with the PROGRAM parameter.; %goto ERREXIT; %end; %******************************************************************************; %*Program=warehouse, proceed to check for swamart location and detail ; %*location. ; %******************************************************************************; %if %quote(&swamart) ne %then %do; %if not %sysfunc(fileexist(&swamart)) %then %do; %put %unquote(&wab_error) The &swamart location for the Web %QCMPRES( Analytics) Web Mart does not exist.; %goto ERREXIT; %end; %end; %******************************************************************************; %* If swamart was not specified and name was specified then look in the Web ; %* Analytics listing data set for the location and verify its existence. ; %******************************************************************************; %if %quote(&swamart)= and %quote(&name) ne %then %do; %if %sysfunc(exist(sasuser.wainit))=0 %then %do; %put %unquote(&wab_error) The SASUSER.WAINIT data set does not exist.; %goto ERREXIT; %end; %locktest(dset=sasuser.wainit); %if &lock_sw ne 0 %then %do; %put %unquote(&wab_error) There was a failure opening %QCMPRES( the) SASUSER.WAINIT dataset.; %goto ERREXIT; %end; %if %get_observation_count(indsn=sasuser.wainit)=0 %then %do; %put %unquote(&wab_error) The SASUSER.WAINIT data set %QCMPRES( contains) 0 observations.; %goto ERREXIT; %end; %***************************************************************************; %* Identify the swamart via the name and store it. ; %***************************************************************************; proc sql noprint; select perm into :swamart from sasuser.wainit where strip(upcase(name)) eq strip(upcase("&name")) ; quit; %***************************************************************************; %* Make sure there are no duplicate Data Stores. ; %***************************************************************************; %if &sqlobs gt 1 %then %do; %put %unquote(&wab_error) Found multiple entries for environment %QCMPRES( &name) in SASUSER.WAINIT.; %goto ERREXIT; %end; %***************************************************************************; %* Make sure Data Store was set to something. ; %***************************************************************************; %else %if &sqlobs eq 0 %then %do; %put %unquote(&wab_error) Cannot locate entry in %QCMPRES( SASUSER.WAINIT) for Web Mart: &name.; %put %unquote(&wab_error) Check the value of the %QCMPRES( %str(%"))name%str(%") parameter.; %goto ERREXIT; %end; %***************************************************************************; %* Make sure the Data Store was set to something. ; %***************************************************************************; %if %quote(&swamart)= %then %do; %put %unquote(&wab_error) Cannot locate a value for the %QCMPRES( %str(%")data_store%str(%")) parameter in %QCMPRES( SASUSER.WAINIT) for environment: &name.; %put %unquote(&wab_error) Check the value of the %QCMPRES( %str(%"))name%str(%") parameter.; %goto ERREXIT; %end; %end; %let swamart=%sysfunc(strip(&swamart)); %******************************************************************************; %* Create local macro variables to contain directory structure. ; ; %******************************************************************************; %local wab_config_dir wab_logs_dir wab_sas_dir wab_data_dir wab_dated_dir wab_detl_dir wab_etl_dir wab_doc_dir wab_stp_dir wab_smy_dir; %let wab_config_dir=&swamart.&separator.config; %let wab_data_dir=&swamart.&separator.data; %let wab_dated_dir=&swamart.&separator.data&separator.dated; %let wab_detl_dir=&swamart.&separator.data&separator.detail; %let wab_doc_dir=&swamart.&separator.data&separator.documents; %let wab_stp_dir=&swamart.&separator.data&separator.stp; %let wab_smy_dir=&swamart.&separator.data&separator.summary; %let wab_etl_dir=&swamart.&separator.e-data-etl; %let wab_logs_dir=&swamart.&separator.logs; %let wab_sas_dir=&swamart.&separator.sas; %let wab_recovery_dir=&swamart.&separator.data&separator.recovery; %******************************************************************************; %*Verify the existence of the Web Analtics directory structure. ; %* The following locations must exist: ; %* ; %* swamart--+ ; %* | ; %* +config ; %* | ; %* | ; %* +data--+ ; %* | ; %* +dated ; %* | ; %* +detail ; %* | ; %* +documents ; %* | ; %* +stp ; %* | ; %* +summary ; %* | ; %* | ; %* +e-data-etl ; %* | ; %* | ; %* +logs ; %* | ; %* | ; %* +sas ; %* ; %******************************************************************************; %if not %sysfunc(fileexist(&wab_config_dir)) %then %do; %put %unquote(&wab_error) The &wab_config_dir location for the Web %QCMPRES( Analytics) Web Mart configuration data directory does not exist.; %goto ERREXIT; %end; %if not %sysfunc(fileexist(&wab_data_dir)) %then %do; %put %unquote(&wab_error) The &wab_data_dir location for the Web %QCMPRES( Analytics) Web Mart data directory does not exist.; %goto ERREXIT; %end; %if not %sysfunc(fileexist(&wab_dated_dir)) %then %do; %put %unquote(&wab_error) The &wab_dated_dir location for the Web %QCMPRES( Analytics) Web Mart date partitioned data directory does not exist.; %goto ERREXIT; %end; %if not %sysfunc(fileexist(&wab_detl_dir)) %then %do; %put %unquote(&wab_error) The &wab_detl_dir location for the Web %QCMPRES( Analytics) Web Mart detail data directory does not exist.; %goto ERREXIT; %end; %if not %sysfunc(fileexist(&wab_doc_dir)) %then %do; %put %unquote(&wab_error) The &wab_doc_dir location for the Web %QCMPRES( Analytics) Web Mart documents data directory does not exist.; %goto ERREXIT; %end; %if not %sysfunc(fileexist(&wab_stp_dir)) %then %do; %put %unquote(&wab_error) The &wab_stp_dir location for the Web %QCMPRES( Analytics) Web Mart STP data directory does not exist.; %goto ERREXIT; %end; %if not %sysfunc(fileexist(&wab_smy_dir)) %then %do; %put %unquote(&wab_error) The &wab_smy_dir location for the Web %QCMPRES( Analytics) Web Mart summary data directory does not exist.; %goto ERREXIT; %end; %if not %sysfunc(fileexist(&wab_logs_dir)) %then %do; %put %unquote(&wab_error) The &wab_logs_dir location for the Web %QCMPRES( Analytics) Web Mart sas logs directory does not exist.; %goto ERREXIT; %end; %if not %sysfunc(fileexist(&wab_sas_dir)) %then %do; %put %unquote(&wab_error) The &wab_sas_dir location for the Web %QCMPRES( Analytics) Web Mart sas programs directory does not exist.; %goto ERREXIT; %end; %******************************************************************************; %* Assign the necessary data libraries for Web Analytics. ; %******************************************************************************; libname config "&wab_config_dir"; libname dated "&wab_dated_dir"; libname summary "&wab_smy_dir"; libname wadetail "&wab_detl_dir"; libname document "&wab_doc_dir"; libname recovery "&wab_recovery_dir"; %******************************************************************************; %* Make sure that the SUMMARY.NEXT_SESS_ID data set exists and create the ; %* NEXT_SESS_ID macro variable ; %******************************************************************************; %if not %sysfunc(exist(summary.next_sess_id)) %then %do; %put %unquote(&wab_warning) The SUMMARY.NEXT_SESS_ID data set does %QCMPRES( not) exist.; %***************************************************************************; %* If there are no data sets in the DATED, WADETAIL, or SUMMARY libraries ; %* that have a SESSION_ID variable, assume that the Web mart admin has ; %* cleaned out the Web mart and create a SUMMARY.NEXT_SESS_ID data set ; %* with SESSION_ID eq 1. ; %* ; %* If the DATED, WADETAIL, or SUMMARY libraries do have data sets, check ; %* whether any of those data sets have numeric SESSION_ID fields. ; %* ; %* If the data sets do not have numeric SESSION_ID fields, issue an error ; %* message explaining that the WaConvrt macro needs to be run. ; %* ; %* If the data sets do have numeric SESSION_ID fields, issue an error ; %* message explaining that cleaning out an existing Web mart must ; %* include removing all the data sets in the DATED, WADETAIL, and ; %* SUMMARY libraries. ; %***************************************************************************; proc sql; create table session_id_dsets as select libname ,memname ,name ,type from dictionary.columns where libname in ('DATED','WADETAIL','SUMMARY') and upcase(name) eq 'SESSION_ID'; quit; %if %get_observation_count(indsn=session_id_dsets) eq 0 %then %do; %************************************************************************; %* create SUMMARY.NEXT_SESS_ID to provide continuity for SESSION_ID ; %* assignments across ETL runs. ; %************************************************************************; data summary.next_sess_id; session_id=1; etl_dttm=datetime(); format etl_dttm datetime19.; run; %put %unquote(&wab_warning) The SUMMARY.NEXT_SESS_ID data set %QCMPRES( is) being created and primed with the value of 1.; %end; %else %do; %************************************************************************; %* If SESSION_ID is a character field, then the Web mart is running the ; %* pre-v5.2 HotFix software and needs to run the WaConvrt macro to ; %* become a v5.2 HotFix installation. ; %************************************************************************; %let non_char_sess_id=No; data _null_; set session_id_dsets; if type eq 'num' then do; call symput('non_char_sess_id','Yes'); stop; end; run; %if &non_char_sess_id eq No %then %do; %put %unquote(&wab_error) There are no numeric SESSION_ID %QCMPRES( fields) in the DATED, WADETAIL, and SUMMARY libraries. %QCMPRES( The) data sets for this Web mart have not all been %QCMPRES( converted) to the v5.2 HotFix standard.; %goto ERREXIT; %end; %else %do; %put %unquote(&wab_error) There are numeric SESSION_ID %QCMPRES( fields) in the DATED, WADETAIL, and/or SUMMARY libraries. %QCMPRES( If) you are trying to clear out a Web mart, please be sure %QCMPRES( to) delete all the data sets in these libraries.; %goto ERREXIT; %end; %end; %end; %global next_sess_id; data _null_; set summary.next_sess_id; call symput('next_sess_id',strip(put(session_id,12.))); run; %******************************************************************************; %* Make sure that the SUMMARY.NEXT_PAGE_REFERENCE data set exists and create ; %* the NEXT_REF macro variable ; %******************************************************************************; %if not %sysfunc(exist(summary.next_page_reference)) %then %do; %put %unquote(&wab_warning) The SUMMARY.NEXT_PAGE_REFERENCE data %QCMPRES( set) does not exist.; %***************************************************************************; %* If there are no SUMMARY.PATHING_yyyymmdd data sets and ; %* SUMMARY.PAGE_FREQUENCIES and SUMMARY.PAGE_FREQUENCIES_OBSOLETE do not ; %* exist either, assume that everything has been deleted from the ; %* SUMMARY library and create SUMMARY.NEXT_PAGE_REFERENCE with an ; %* initial value of 1. ; %***************************************************************************; proc sql; create table pathing_yyyy_dsets as select libname ,memname from dictionary.tables where libname eq 'SUMMARY' and index(memname,'PATHING_200'); create table page_freq_dsets as select libname ,memname from dictionary.tables where libname eq 'SUMMARY' and memname in ('PAGE_FREQUENCIES','PAGE_FREQUENCIES_OBSOLETE'); quit; %if %get_observation_count(indsn=pathing_yyyy_dsets) eq 0 and %get_observation_count(indsn=page_freq_dsets) eq 0 %then %do; %************************************************************************; %* 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; %put %unquote(&wab_warning) The SUMMARY.NEXT_PAGE_REFERENCE %QCMPRES( data) set is being created and primed with the value of 1.; %end; %else %do; %put %unquote(&wab_error) The SUMMARY library has %QCMPRES( PATHING_yyyymmdd) data sets as well as PAGE_FREQUENCIES %QCMPRES( and) PAGE_FREQUENCIES_OBSOLETE, but is missing the %QCMPRES( NEXT_PAGE_REFERENCE) data set. Please restore the %QCMPRES( NEXT_PAGE_REFERENCE) data set from backup.; %goto ERREXIT; %end; %end; %global next_ref; data _null_; set summary.next_page_reference; call symput('next_ref',strip(put(reference,12.))); run; %******************************************************************************; %* 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 ERREXIT; %end; %******************************************************************************; %* Create SUMMARY.WADTSORT ; %******************************************************************************; %wametdef(def_dsn=wadtsort ,retcode=wab_rc ); %*****************************************************************************; %* Ensure that either the location of, ETL web mart name of or the actual ; %* detail data set has been specified. ; %*****************************************************************************; %if %quote(&data_store)= and %quote(&name)= and %quote(&detail)= %then %do; %if %sysfunc(fileexist(&wab_etl_dir)) %then %do; libname detail "&wab_etl_dir.&separator.detail"; %let detail=detail.weblog_detail_1; %***************************************************************************; %* If the ETL detail data set has been specified then ensure its ; %* existence, a positive number of records, and that it can be opened. ; %***************************************************************************; %if %sysfunc(exist(&detail))=0 %then %do; %put %unquote(&wab_error) The detail data set does not exist.; %goto ERREXIT; %end; %locktest(dset=&detail); %if &lock_sw ne 0 %then %do; %put %unquote(&wab_error) There was a failure opening the detail dataset.; %goto ERREXIT; %end; %if %get_observation_count(indsn=&detail)=0 %then %do; %put %unquote(&wab_error) The detail data set contains 0 observations.; %goto ERREXIT; %end; %end; %else %do; %put %unquote(&wab_error) The %str(%")data_store%str(%") %QCMPRES( parameter)%str(,) the %str(%")name%str(%")%str(,) or the %QCMPRES( %str(%")detail%str(%")) parameter of the %nrstr(%%)waetl %QCMPRES( macro) must be specified.; %goto ERREXIT; %end; %end; %******************************************************************************; %* If the ETL detail data set has been specified then ensure its ; %* existence, a positive number of records, and that it can be opened. ; %******************************************************************************; %if %quote(&detail) ne %then %do; %if %sysfunc(exist(&detail))=0 %then %do; %put %unquote(&wab_error) The detail data set does not exist.; %goto ERREXIT; %end; %locktest(dset=&detail); %if &lock_sw ne 0 %then %do; %put %unquote(&wab_error) There was a failure opening the detail dataset.; %goto ERREXIT; %end; %if %get_observation_count(indsn=&detail)=0 %then %do; %put %unquote(&wab_error) The detail data set contains 0 observations.; %goto ERREXIT; %end; %end; %******************************************************************************; %* If the ETL data store has been specified then ensure its existence. ; %******************************************************************************; %if %quote(&detail)= and %quote(&data_store) ne %then %do; %if not %sysfunc(fileexist(&data_store.&separator.detail)) %then %do; %put %unquote(&wab_error) The location of the ETL detail data %QCMPRES( set) does not exist.; %goto ERREXIT; %end; libname detail "&data_store.&separator.detail"; libname control "&data_store.&separator.control"; %let detail=detail.weblog_detail_1; %if %sysfunc(exist(&detail))=0 %then %do; %put %unquote(&wab_error) The detail data set does not exist.; %goto ERREXIT; %end; %locktest(dset=&detail); %if &lock_sw ne 0 %then %do; %put %unquote(&wab_error) There was a failure opening the detail dataset.; %goto ERREXIT; %end; %if %get_observation_count(indsn=&detail)=0 %then %do; %put %unquote(&wab_error) The detail data set contains 0 observations.; %goto ERREXIT; %end; %end; %******************************************************************************; %* If the web mart name has been specified then ensure its existence in ; %* the e-Data ETL listing. ; %******************************************************************************; %if %quote(&detail)= and %quote(&data_store)= and %quote(&name) ne %then %do; %if %sysfunc(exist(sasuser.wbinit))=0 %then %do; %put %unquote(&wab_error) The sasuser.wbinit data set does not exist.; %goto ERREXIT; %end; %locktest(dset=sasuser.wbinit); %if &lock_sw ne 0 %then %do; %put %unquote(&wab_error) There was a failure opening the sasuser.wbinit dataset.; %goto ERREXIT; %end; %if %get_observation_count(indsn=sasuser.wbinit)=0 %then %do; %put %unquote(&wab_error) The sasuser.wbinit data set contains 0 observations.; %goto ERREXIT; %end; %***************************************************************************; %* Identify the data store via the name and store it. ; %***************************************************************************; proc sql noprint; select perm into :data_store from sasuser.wbinit where strip(upcase(name)) eq strip(upcase("&name")); quit; %***************************************************************************; %* Make sure there are no duplicate Data Stores. ; %***************************************************************************; %if &sqlobs gt 1 %then %do; %put %unquote(&wab_error) Found multiple entries for environment %QCMPRES( &name) in sasuser.wbinit.; %goto ERREXIT; %end; %***************************************************************************; %* Make sure Data Store was set to something. ; %***************************************************************************; %else %if &sqlobs eq 0 %then %do; %put %unquote(&wab_error) Cannot locate entry in %QCMPRES( SASUSER.WBINIT) for environment: &name.; %put %unquote(&wab_error) Check the value of the %QCMPRES( %str(%"))name%str(%") parameter.; %goto ERREXIT; %end; %***************************************************************************; %* Make sure the Data Store was set to something. ; %***************************************************************************; %if %quote(&data_store)= %then %do; %put %unquote(&wab_error) Cannot locate a value for the %QCMPRES( %str(%")data_store%str(%")) parameter in %QCMPRES( SASUSER.WBINIT) for environment: &name.; %put %unquote(&wab_error) Check the value of the %QCMPRES( %str(%"))name%str(%") parameter.; %goto ERREXIT; %end; %let data_store=%sysfunc(strip(&data_store)); %if not %sysfunc(fileexist(&data_store.&separator.detail)) %then %do; %put %unquote(&wab_error) The location of the ETL detail data %QCMPRES( set) does not exist.; %goto ERREXIT; %end; libname detail "&data_store.&separator.detail"; libname control "&data_store.&separator.control"; %let detail=detail.weblog_detail_1; %***************************************************************************; %* If the ETL detail data set has been specified then ensure its ; %* existence, a positive number of records, and that it can be opened. ; %***************************************************************************; %if %sysfunc(exist(&detail))=0 %then %do; %put %unquote(&wab_error) The detail data set does not exist.; %goto ERREXIT; %end; %locktest(dset=&detail); %if &lock_sw ne 0 %then %do; %put %unquote(&wab_error) There was a failure opening the detail dataset.; %goto ERREXIT; %end; %if %get_observation_count(indsn=&detail)=0 %then %do; %put %unquote(&wab_error) The detail data set contains 0 observations.; %goto ERREXIT; %end; %end; %******************************************************************************; %* Initialize the temporary library to the physical location specified by ; %* temp_store. If temp_store does not exist then let the temporary library be ; %* to the location /swawork under work. Write warning out to the log if ; %* physical location does not exist. ; %******************************************************************************; %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); %end; %if &wab_rc ne 0 %then %goto ERREXIT; libname worklib "&temp_store"; %let temp_lib=worklib; %end; %else %do; %***************************************************************************; %* If the temporary location exists then ensure that it does not equal any ; %* of the permanent data locations in the web mart. If it passes this test ; %* Set the temporary library to worklib. ; %***************************************************************************; %if %sysfunc(fileexist(&temp_store)) %then %do; %if %sysfunc(strip(%sysfunc(lowcase("&temp_store")))) =%sysfunc(strip(%sysfunc(lowcase("&wab_dated_dir")))) %then %do; %put %unquote(&wab_error) The temporary location for data %QCMPRES( sets) cannot be the same as the permanent location for %QCMPRES( date) partitioned data sets.; %goto ERREXIT; %end; %if %sysfunc(strip(%sysfunc(lowcase("&temp_store")))) =%sysfunc(strip(%sysfunc(lowcase("&wab_detl_dir")))) %then %do; %put %unquote(&wab_error) The temporary location for data %QCMPRES( sets) cannot be the same as the permanent location for %QCMPRES( detail) data sets.; %goto ERREXIT; %end; %if %sysfunc(strip(%sysfunc(lowcase("&temp_store")))) =%sysfunc(strip(%sysfunc(lowcase("&wab_doc_dir")))) %then %do; %put %unquote(&wab_error) The temporary location for data %QCMPRES( sets) cannot be the same as the permanent location for %QCMPRES( document) data sets.; %goto ERREXIT; %end; %if %sysfunc(strip(%sysfunc(lowcase("&temp_store")))) =%sysfunc(strip(%sysfunc(lowcase("&wab_stp_dir")))) %then %do; %put %unquote(&wab_error) The temporary location for data %QCMPRES( sets) cannot be the same as the permanent location for %QCMPRES( stored process) data sets.; %goto ERREXIT; %end; %if %sysfunc(strip(%sysfunc(lowcase("&temp_store")))) =%sysfunc(strip(%sysfunc(lowcase("&wab_smy_dir")))) %then %do; %put %unquote(&wab_error) The temporary location for data %QCMPRES( sets) cannot be the same as the permanent summary %QCMPRES( location) for data sets.; %goto ERREXIT; %end; libname worklib "&temp_store"; %let temp_lib=worklib; %end; %***************************************************************************; %* If the temporary location does not exist then set the temporary library ; %* to the location /swawork under work. ; %***************************************************************************; %else %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); %end; %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) The temporary storage location was %QCMPRES( not) specified and the default one could not be created.; %goto ERREXIT; %end; libname worklib "&temp_store"; %let temp_lib=worklib; %end; %end; %******************************************************************************; %* Create a list of data sets in the temporary library and use proc datasets ; %* to delete them all. ; %******************************************************************************; proc sql; create table &temp_lib..temp_tables as select memname from dictionary.tables where upcase(compress(libname))=upcase(compress("&temp_lib")); run; quit; %******************************************************************************; %* If the temp directory contains at least one SAS data set then call PROC ; %* DATASETS to delete the temp directorys contents ; %******************************************************************************; %if &sqlobs gt 0 %then %do; data _null_; set &temp_lib..temp_tables end=eof; call symput(compress("_wab_temp_memname_"||put(_n_,7.)),memname); if eof then call symput("_wab_tot_tables_",put(_n_,7.)); run; proc datasets library=&temp_lib nolist; delete %do wab_iter=1 %to &_wab_tot_tables_; &&_wab_temp_memname_&wab_iter %end; temp_tables; run; quit; %end; %******************************************************************************; %* Delete all files in the temporary location. ; %******************************************************************************; %if %upcase(&sysscp) eq WIN or %upcase(&sysscp) eq DNTHOST %then %do; %execcmd(&delete_all "&temp_store&separator.*.*" ,_rc=wab_rc); %end; %else %do; %execcmd(&delete_all "&temp_store&separator.*" ,_rc=wab_rc); %end; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) Legacy files in the temporary %QCMPRES( location:) &temp_store could not be deleted. Please check permissions.; %goto ERREXIT; %end; %******************************************************************************; %* Set the format search path to look in the following library order: ; %* 1. WORK ; %* 2. LIBRARY ; %* 1. CONFIG ; %* 2. CONTROL ; %******************************************************************************; options fmtsearch=(work library config control); %******************************************************************************; %* MODULE: I_C - Date Checking ; %* SUPPORT:saskxs ; %* Check Dates in ETL, Web Analytics Detail , and Web Analytics Session ; %* Data. ; %******************************************************************************; %let module=I_C; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; proc sql; %****************************************************************************; %* Get the uniques dates from ETL detail ; %* to process. ; %****************************************************************************; create table &temp_lib..etl_detail_dates as select distinct date from &detail where date gt .z; %****************************************************************************; %* Get the list of the dates from Web Analytics kept detail. ; %****************************************************************************; create table &temp_lib..detail_library_contents as select memname, input(scan(memname,2,"_"),YYMMDD10.) as date from dictionary.tables where upcase(libname)="WADETAIL" and prxmatch('/^DETAIL_\d{8}$/', upcase(left(trim(memname)))); %****************************************************************************; %* Create the list of kept session data sets from ; %* the dated directory. ; %****************************************************************************; create table &temp_lib..dated_library_contents as select memname, input(scan(memname,2,"_"),YYMMDD10.) as date from dictionary.tables where upcase(libname)="DATED" and prxmatch('/^SESSION_\d{8}$/', upcase(left(trim(memname)))); %****************************************************************************; %* Get the list of dates for data which needs to be updated. ; %* If the date exists in ETL detail, the kept detail, and ; %* in sessions data then the summaries will be updated. ; %****************************************************************************; create table &temp_lib..update_dates as select date from (select date from &temp_lib..etl_detail_dates where date in (select distinct date from &temp_lib..detail_library_contents)) where date in (select distinct date from &temp_lib..dated_library_contents); %****************************************************************************; %* Create the list of dates for which data needs to be created. ; %* If the date exists in ETL detail and does not exist in kept detail, and ; %* and does not exist in sessions data. New rows in the summaries will be ; %* created for these dates. ; %****************************************************************************; create table &temp_lib..new_dates as select date from (select date from &temp_lib..etl_detail_dates where date not in (select distinct date from &temp_lib..detail_library_contents)) where date not in (select distinct date from &temp_lib..dated_library_contents); %****************************************************************************; %* Create the list of dates for which we cannot create data. ; %* If we do not have detail in the warehouse for a day we have processed ; %* in the summaries then we cannot recreate session correctly because we ; %* do not have data for that day. ; %****************************************************************************; create table &temp_lib..invalid_dates as select date from (select date from &temp_lib..etl_detail_dates where date not in (select distinct date from &temp_lib..detail_library_contents)) where date in (select distinct date from &temp_lib..dated_library_contents); quit; %******************************************************************************; %* Ensure that the data set containing dates processed in the ETL detail ; %* data set exists, has observations, and can be opened. ; %******************************************************************************; %if %sysfunc(exist(&temp_lib..etl_detail_dates))=0 %then %do; %put %unquote(&wab_error) The &temp_lib..etl_detail_dates data set %QCMPRES( with) dates from etl does not exist.; %goto ERREXIT; %end; %locktest(dset=&temp_lib..etl_detail_dates); %if &lock_sw ne 0 %then %do; %put %unquote(&wab_error) The &temp_lib..etl_detail_dates data set %QCMPRES( with) dates from etl cannot be opened.; %goto ERREXIT; %end; %if %get_observation_count(indsn=&temp_lib..etl_detail_dates)=0 %then %do; %put %unquote(&wab_error) The &temp_lib..etl_detail_dates data set %QCMPRES( with) dates from etl contains 0 observations.; %goto ERREXIT; %end; %******************************************************************************; %* Ensure that the data set containing dates processed in the ETL detail ; %* data set and the Web Analytics warehouse exists, and can be opened. ; %******************************************************************************; %if %sysfunc(exist(&temp_lib..update_dates))=0 %then %do; %put %unquote(&wab_error) The &temp_lib..update_dates data set %QCMPRES( with) dates for which summaries will be %QCMPRES( updated) does not exist.; %goto ERREXIT; %end; %locktest(dset=&temp_lib..update_dates); %if &lock_sw ne 0 %then %do; %put %unquote(&wab_error) The &temp_lib..update_dates data set %QCMPRES( with) dates for which summaries will be %QCMPRES( updated) cannot be opened.; %goto ERREXIT; %end; %******************************************************************************; %* Ensure that the data set containing dates processed in the ETL detail ; %* data set but not in the Web Analytics warehouse exists, and can be opened. ; %******************************************************************************; %if %sysfunc(exist(&temp_lib..new_dates))=0 %then %do; %put %unquote(&wab_error) The &temp_lib..new_dates data set %QCMPRES( with) dates for which summaries will be %QCMPRES( added) does not exist.; %goto ERREXIT; %end; %locktest(dset=&temp_lib..new_dates); %if &lock_sw ne 0 %then %do; %put %unquote(&wab_error) The &temp_lib..new_dates data set %QCMPRES( with) dates for which summaries will be %QCMPRES( added) cannot be opened.; %goto ERREXIT; %end; %******************************************************************************; %* Write a warning to the log if the data set containing invalid dates can not ; %* be opened. These dates exist in etl detail, Web Analytics session data, but ; %* not in the Web Analytics detail data. ; %******************************************************************************; %if %sysfunc(exist(&temp_lib..invalid_dates))=0 %then %do; %put %unquote(&wab_warning) The &temp_lib..invalid_dates data set %QCMPRES( with) invalid dates does not exist.; %end; %locktest(dset=&temp_lib..invalid_dates); %if &lock_sw ne 0 %then %do; %put %unquote(&wab_warning) The &temp_lib..invalid_dates data set %QCMPRES( with) invalid dates cannot be opened.; %end; %******************************************************************************; %* Get the number of dates which need to be updated, created and are invalid ; %* and store them into sas macro variables. ; %******************************************************************************; data _null_; num_etl_dates=%get_observation_count(indsn=&temp_lib..etl_detail_dates); num_update_dates=%get_observation_count(indsn=&temp_lib..update_dates); num_new_dates=%get_observation_count(indsn=&temp_lib..new_dates); num_invalid_dates=%get_observation_count(indsn=&temp_lib..invalid_dates); call symput("_wab_num_etl_dates_",num_etl_dates); call symput("_wab_num_update_dates_",num_update_dates); call symput("_wab_num_new_dates_",num_new_dates); call symput("_wab_num_invalid_dates_",num_invalid_dates); run; %******************************************************************************; %* Store each date in etl detail into a separate macro variable. ; %******************************************************************************; %if &_wab_num_etl_dates_ gt 0 %then %do; data _null_; length etl_date_list $ 4096; format etl_date_list $4096.; informat etl_date_list $4096.; retain etl_date_list " "; set &temp_lib..etl_detail_dates end=eof; tempdate=strip(put(date,6.)); call symput("_wab_etl_sas_date_"||trim(left(put(_n_,6.))) ,tempdate); call symput("_wab_etl_date_"||trim(left(put(_n_,6.))) ,trim(left(put(date,yymmddn8.)))); if not eof then do; etl_date_list=strip(compress(tempdate ||"," ||etl_date_list)); end; else do; etl_date_list=strip(compress(etl_date_list ||tempdate)); call symput("_wab_etl_sas_date_list_" ,strip(etl_date_list)); end; run; %end; %******************************************************************************; %* Store each date whose detail is to be updated into a separate macro ; %* variable. ; %******************************************************************************; %if &_wab_num_update_dates_ gt 0 %then %do; data _null_; length update_date_list $ 4096; format update_date_list $4096.; informat update_date_list $4096.; retain update_date_list " "; set &temp_lib..update_dates end=eof; tempdate=strip(put(date,6.)); call symput("_wab_update_sas_date_"||trim(left(put(_n_,6.))) ,tempdate); call symput("_wab_update_date_"||trim(left(put(_n_,6.))) ,trim(left(put(date,yymmddn8.)))); if not eof then do; update_date_list=strip(compress(tempdate ||"," ||update_date_list)); end; else do; update_date_list=strip(compress(update_date_list ||tempdate)); call symput("_wab_update_sas_date_list_" ,strip(update_date_list)); end; run; %end; %******************************************************************************; %* Store each date whose detail is to be created into a separate macro ; %* variable. ; %******************************************************************************; %if &_wab_num_new_dates_ gt 0 %then %do; data _null_; length new_date_list $ 4096; format new_date_list $4096.; informat new_date_list $4096.; retain new_date_list " "; set &temp_lib..new_dates end=eof; tempdate=strip(put(date,6.)); call symput("_wab_new_sas_date_"||trim(left(put(_n_,6.))) ,tempdate); call symput("_wab_new_date_"||trim(left(put(_n_,6.))) ,trim(left(put(date,yymmddn8.)))); if not eof then do; new_date_list=strip(compress(tempdate ||"," ||new_date_list)); end; else do; new_date_list=strip(compress(new_date_list ||tempdate)); call symput("_wab_new_sas_date_list_" ,strip(new_date_list)); end; run; %end; %******************************************************************************; %* Create macro variables for each date that is to be updated or created. ; %******************************************************************************; data &temp_lib..all_dates; set &temp_lib..update_dates &temp_lib..new_dates end=eof; call symput("_wab_date_"||trim(left(put(_n_,6.))) ,trim(left(put(date,yymmddn8.)))); if eof then call symput("_wab_num_dates_" ,trim(left(put(_n_,6.)))); run; proc means data=&temp_lib..all_dates noprint min max; var date; output out=&temp_lib..wab_min_max_process_dates min=wab_min_date max=wab_max_date; run; %global _wab_min_process_date_ _wab_max_process_date_ ; data _null_; set &temp_lib..wab_min_max_process_dates; call symput("_wab_min_process_date_",strip(put(wab_min_date,6.))); call symput("_wab_max_process_date_",strip(put(wab_max_date,6.))); run; %******************************************************************************; %* Store each date in etl detail into a separate macro variable. ; %******************************************************************************; %if &_wab_num_invalid_dates_ gt 0 %then %do; data _null_; set &temp_lib..invalid_dates end=eof; call symput("_wab_invalid_date_"||trim(left(put(_n_,6.))) ,trim(left(put(date,yymmddn8.)))); run; %do wab_iter=1 %to &_wab_num_invalid_dates_; %***************************************************************************; %* Write notification message to the log. ; %***************************************************************************; %put %unquote(&wab_warning) %QCMPRES( &temp_lib..detail_&&_wab_invalid_date_&wab_iter) %QCMPRES( is) invalid because there is no %QCMPRES( corresponding) DETAIL data set for the date %QCMPRES( &&_wab_invalid_date_&wab_iter) but there is a corresponding %QCMPRES( SESSION) data set for the date &&_wab_invalid_date_&wab_iter.; %end; %end; %******************************************************************************; %* If there are no dates that need to be updated or created in the web mart ; %* from ETL detail then exit processing. ; %******************************************************************************; %if &_wab_num_update_dates_ le 0 and &_wab_num_new_dates_ le 0 %then %do; %put %unquote(&wab_error) There are no dates in ETL detail that %QCMPRES( need) to be created or updated in the Web Analytics web mart.; %goto ERREXIT; %end; %******************************************************************************; %* SECTION: II - Create Analysis Data Sets ; %******************************************************************************; %******************************************************************************; %* MODULE: II_A1 - Remove e-Data-ETL-related formats from &DETAIL ; %* SUPPORT: frroed ; %* Remove e-Data-ETL-related formats from the &DETAIL data set so that errors ; %* will not occur when/if users try to access data sets without NOFMTERR ; %******************************************************************************; %let module=II_A1; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; data _null_; length libname_memname $ 200; libname_memname="&detail"; if countc(libname_memname,'.') lt 1 then do; call symput('wab_detail_libname','WORK'); call symput('wab_detail_memname',strip(libname_memname)); end; else do; call symput('wab_detail_libname',strip(scan(libname_memname,1,'.'))); call symput('wab_detail_memname',strip(scan(libname_memname,2,'.'))); end; run; %remove_formats(libname=&wab_detail_libname ,memname=&wab_detail_memname ); %******************************************************************************; %* MODULE: II_A2 - Identify _TOKEN fields ; %* SUPPORT: frroed ; %* Identify the _TOKEN fields in the WAADMSUM metadata ; %******************************************************************************; %let module=II_A2; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %******************************************************************************; %* Identify _TOKEN CLASS variables that are in SUMMARY aggregations (_TOKEN ; %* variables are the smaller ($32) counterparts to large text fields and ; %* are used for more efficient summarization. ; %* the _TOKEN variables need to be reference-able as macro variables so ; %* that code can be executed in %CREATE_DETAIL and %CREATE_SESSION to ; %* create these _TOKEN variables and the HASH_ data sets that associate ; %* them with their corresponding strings. ; %* ; %* For example, REQUESTED_FILE is a long ($1024) string that is used as a ; %* CLASS variable in PROC Summary. It is much more efficient to use a ; %* 32-byte token instead of the actual string. However, users need to ; %* see the actual field because the token is meaningless to them. After ; %* all the summarizing is completed using the token, the corresponding ; %* string is added to the final aggregation. ; %******************************************************************************; data &temp_lib..all_detail_token_vars &temp_lib..all_session_token_vars; set config.waadmsum; length string_var token_var $32 ; %***************************************************************************; %* because there can be some contexts where the length of the STRING_ var ; %* cannot be assigned from actual data, the default length -- $ 1024, ; %* the length of the longest STRING_ variable -- is used instead. ; %* ; %* ; %* ; %* make sure to change this value if the longest STRING_ var ever is longer ; %* than $ 1024 : %* ; %* ; %* ; %***************************************************************************; retain string_var_length 1024; class_var_list=lowcase(class_var_list); source_table=lowcase(source_table); if index(class_var_list,'first_requested_file') or index(class_var_list,'referrer') or index(class_var_list,'referrer_domain') or index(class_var_list,'requested_file') or index(class_var_list,'search_term') or index(class_var_list,'visitor_id') then do; do i=1 to (1+countc(class_var_list,',')); if index(scan(class_var_list,i,','),'first_requested_file') or index(scan(class_var_list,i,','),'referrer') or index(scan(class_var_list,i,','),'referrer_domain') or index(scan(class_var_list,i,','),'requested_file') or index(scan(class_var_list,i,','),'search_term') or index(scan(class_var_list,i,','),'visitor_id') then do; string_var=scan(class_var_list,i,','); source_var=strip(string_var) || '_source'; token_var=strip(string_var) || '_token'; if source_table eq 'detail' then output &temp_lib..all_detail_token_vars; else output &temp_lib..all_session_token_vars; end; end; end; keep token_var source_var string_var string_var_length ; run; proc sort data=&temp_lib..all_detail_token_vars out =&temp_lib..uniq_detail_token_vars nodupkeys; by token_var; run; proc sort data=&temp_lib..all_session_token_vars out =&temp_lib..uniq_session_token_vars nodupkeys; by token_var; run; data _null_; set &temp_lib..uniq_detail_token_vars end=eof; call symput('detail_string_var_'||strip(put(_n_,3.)) ,strip(string_var) ); call symput('detail_token_var_'||strip(put(_n_,3.)) ,strip(token_var) ); call symput('detail_source_var_'||strip(put(_n_,3.)) ,strip(source_var) ); call symput('detail_string_len_'||strip(put(_n_,3.)) ,strip(put(string_var_length,5.)) ); if eof then call symput('detail_token_var_count',strip(put(_n_,3.))); run; data _null_; set &temp_lib..uniq_session_token_vars end=eof; call symput('session_string_var_'||strip(put(_n_,3.)) ,strip(string_var) ); call symput('session_token_var_'||strip(put(_n_,3.)) ,strip(token_var) ); call symput('session_source_var_'||strip(put(_n_,3.)) ,strip(source_var) ); call symput('session_string_len_'||strip(put(_n_,3.)) ,strip(put(string_var_length,5.)) ); if eof then call symput('session_token_var_count',strip(put(_n_,3.))); run; %******************************************************************************; %* MODULE: II_A3 - Partition ETL Detail ; %* SUPPORT:frroed ; %* Partition ETL Detail for appending and creating date partitioned Web ; %* Analytics Detail. ; %******************************************************************************; %let module=II_A3; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %******************************************************************************; %* Create the SUMMARY.WEBLOG_DETAIL_1_SAMPLE data set ; %* This data set is the source for abbreviated ETL executions that are ; %* needed to prime metadata for the SWA Administrator ; %******************************************************************************; data summary.weblog_detail_1_sample; set &detail; if _n_ gt 100 then stop; run; %******************************************************************************; %* Create one Partitioned ETL data set per date in ETL detail. ; %******************************************************************************; data %do wab_iter=1 %to &_wab_num_etl_dates_; &temp_lib..detail_&&_wab_etl_date_&wab_iter %end;; set &detail; %***************************************************************************; %* convert all missing STATUS_CODEs to zero ; %***************************************************************************; if status_code le .z then status_code=0; select (date); %do j=1 %to &_wab_num_etl_dates_; when (&&_wab_etl_sas_date_&j) output &temp_lib..detail_&&_wab_etl_date_&j; %end; otherwise; end; %***************************************************************************; %* make sure that STATUS_CODE does not have a custom format. the data ; %* set that WAETL inherits from WebHound has STATUS_CODE assigned the ; %* STATUS format, which causes WAETL to fail if one of the STATUS_CODEs ; %* it encounters is not defined in the format. because SWA cannot ; %* update the STATUS format, WAETL removes the format for STATUS_CODE. ; %***************************************************************************; format status_code 3.; run; %******************************************************************************; %* Process each Partitioned ETL data set. ; %******************************************************************************; %do wab_iter=1 %to &_wab_num_etl_dates_; %***************************************************************************; %* Check if the Partitioned ETL is sorted correctly. ; %***************************************************************************; data _null_; %************************************************************************; %* Keep only the Sort Variables to reduce I/O. ; %************************************************************************; set &temp_lib..detail_&&_wab_etl_date_&wab_iter (keep=session_id datetime); by session_id datetime; run; %***************************************************************************; %* Sort the Partitioned ETL (if necessary) ; %* Note: the previous DATA _NULL_ step sets &SYSERR to 1012 if the data ; %* set referenced in its SET statement is not in the order ; %* specified in the BY statement. ; %***************************************************************************; %if &syserr eq 1012 %then %do; %put %unquote(&wab_note) An ERROR message in the previous Data Step %QCMPRES( indicates) that an error has been detected and trapped. It %QCMPRES( does) not indicate a processing failure.; proc sort data=&temp_lib..detail_&&_wab_etl_date_&wab_iter; by session_id datetime; run; %end; %global wab_max_nv_pairs_&wab_iter; %Create_Detail(detail_sffx=&&_wab_etl_date_&wab_iter ,detail_iter=&wab_iter ); %end; %******************************************************************************; %* MODULE: II_B - Create Web Analytics Session Data ; %* SUPPORT:saskxs ; %* Create the session data sets from previously appended and/or created ; %* Web Analytics detail ; %******************************************************************************; %let module=II_B; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %******************************************************************************; %* For each date whose Web Analytics kept detail has been updated also ; %* update sessions. ; %******************************************************************************; %do wab_iter=1 %to &_wab_num_dates_; %Create_Session(session_sffx=&&_wab_date_&wab_iter ,session_iter=&wab_iter ,retcode=wab_rc ); %end; %******************************************************************************; %* MODULE: II_C - Create Web Analytics Summary Data ; %* SUPPORT:saskxs ; %* Create the summary data sets from Web Analytics detail and session data. ; %******************************************************************************; %let module=II_C; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %******************************************************************************; %* Convert the WAADMSUM metadata into the WASUMENG format. ; %******************************************************************************; data &temp_lib..waadmsum_extr; set &wab_admsum; if etl_run_sw eq 1; run; %waadmsum(meta_in=&temp_lib..waadmsum_extr ,meta_out=&wab_metadsn ,retcode=wab_rc ); %******************************************************************************; %* If there are failures then exit web analytics macro. ; %******************************************************************************; %if &wab_rc ne 0 %then %goto ERREXIT; %******************************************************************************; %* Call the summary macro to produce proc summary files for dated input files. ; %******************************************************************************; %wasumeng(meta_dsn=&wab_metadsn ,partition_flag=1 ,out_summary_file=&temp_store&separator.wab_summary ,retcode=wab_rc ,summcnt=wab_summary_count ); %******************************************************************************; %* If there are failures then exit web analytics macro. ; %******************************************************************************; %if &wab_rc ne 0 %then %goto ERREXIT; %******************************************************************************; %* Include each file if it exists. If it does not exist fail. ; %******************************************************************************; %do wab_iter=1 %to &wab_summary_count; %let summid=%sysfunc(putn(&wab_iter,z3.)); %if %sysfunc(fileexist(&temp_store&separator.wab_summary&summid..sas)) %then %do; %include "&temp_store&separator.wab_summary&summid..sas"; %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) The PROC SUMMARY -- %QCMPRES( &temp_store&separator.wab_summary&summid..sas) %QCMPRES(-- has) been created but cannot be INCLUDEd.; %goto ERREXIT; %end; %end; %else %do; %put %unquote(&wab_error) The location for the PROC SUMMARY %QCMPRES( step) &temp_store&separator.wab_summary&summid..sas does not exist.; %goto ERREXIT; %end; %end; %******************************************************************************; %* Get a list of unique output data sets. Store the full names, memnames and ; %* number of unique output data sets which were created from date partitioned ; %* input data sets. ; %******************************************************************************; proc sort data=&temp_lib.._wab_meta_data_(keep=output_table id ) out=&temp_lib.._wab_meta_data_nodup nodupkey; by output_table; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) The metadata data set containing %QCMPRES( the) summary output table names could not be sorted.; %goto ERREXIT; %end; data _null_; retain library_error 0; set &temp_lib.._wab_meta_data_nodup end=eof; call symput(compress("_wab_summary_dsn_"||put(_n_,7.)),output_table); libname=lowcase(strip(scan(output_table,1,'.'))); memname=lowcase(strip(scan(output_table,2,'.'))); if scan(output_table,2,'.')="" then do; memname=scan(output_table,1,'.'); libname="work"; end; call symput(compress("_wab_summary_memname_"||put(_n_,7.)) ,strip(memname)); call symput(compress("_wab_re_summary_id_"||put(_n_,7.)) ,strip(id)); if eof then call symput("_wab_num_summary_tables_",put(_n_,7.)); run; %******************************************************************************; %* Create a list of date literals that can be used by an IN function to ; %* extract from the SUMMARY warehouse the dates that match dates in the ; %* current ETL. These dates will need to be re-summarized. ; %******************************************************************************; data _null_; set &temp_lib..all_dates end=eof; length common_dates_day common_dates_week common_dates_month common_dates_qtr common_dates_year $ 400; retain common_dates_day common_dates_week common_dates_month common_dates_qtr common_dates_year; %***************************************************************************; %* assign a _DATE field to represent the starting date for each interval ; %***************************************************************************; week_date =intnx('week' || "&wab_week_shift",date,0); month_date=intnx('month',date,0); qtr_date =intnx('quarter',date,0); year_date =intnx('year',date,0); if _n_ eq 1 then do; common_dates_day ="('" || put(date,date9.) || "'d"; common_dates_week ="('" || put(week_date,date9.) || "'d"; common_dates_month="('" || put(month_date,date9.) || "'d"; common_dates_qtr ="('" || put(qtr_date,date9.) || "'d"; common_dates_year ="('" || put(year_date,date9.) || "'d"; end; else do; common_dates_day =strip(common_dates_day) || ",'" || put(date,date9.) || "'d"; %************************************************************************; %* concatenate the interval date only if not already in the string ; %************************************************************************; if not index(strip(common_dates_week),put(week_date,date9.)) then common_dates_week =strip(common_dates_week) || ",'" || put(week_date,date9.) || "'d"; if not index(strip(common_dates_month),put(month_date,date9.)) then common_dates_month=strip(common_dates_month) || ",'" || put(month_date,date9.) || "'d"; if not index(strip(common_dates_qtr),put(qtr_date,date9.)) then common_dates_qtr =strip(common_dates_qtr) || ",'" || put(qtr_date,date9.) || "'d"; if not index(strip(common_dates_year),put(year_date,date9.)) then common_dates_year =strip(common_dates_year) || ",'" || put(year_date,date9.) || "'d"; end; if eof then do; call symput('common_dates_day' ,strip(common_dates_day) || ')'); call symput('common_dates_week' ,strip(common_dates_week) || ')'); call symput('common_dates_month',strip(common_dates_month)|| ')'); call symput('common_dates_qtr' ,strip(common_dates_qtr) || ')'); call symput('common_dates_year' ,strip(common_dates_year) || ')'); end; run; %******************************************************************************; %* For each summary which was generated from a date partitioned data set, ; %* determine its time frame and append to an extract of the corresponding ; %* data set in the warehouse (the extract is to have only the DATEs that ; %* have entries in the current ETL). ; %******************************************************************************; proc datasets library=&temp_lib nolist nowarn; delete need_re_summary; quit; %do wab_iter=1 %to &_wab_num_summary_tables_; %***************************************************************************; %* The original assumption is that all initial summarizations will need ; %* to be re-summarized with existing data from the warehouse so that ; %* dates that have data from an earlier ETL and additional data from ; %* the current ETL will be combined. ; %* ; %* Re-summarization does NOT need to be done if ; %* 1) the summarization does not already have a counterpart in the ; %* warehouse ; %* 2) the summarization does not have any dates in common with the ; %* dates in its counterpart in the warehouse ; %* ; %* The mechanism for indicating whether the Re-Summarization needs to ; %* take place is the NEED_RE_SUMMARY macro variable. ; %***************************************************************************; %let need_re_summary=1; %if %sysfunc(exist(summary.&&_wab_summary_memname_&wab_iter)) %then %do; proc means data=summary.&&_wab_summary_memname_&wab_iter min max noprint; var date; output out=&temp_lib..min_current_date(keep=max_current_date min_current_date) min=min_current_date max=max_current_date; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) The PROC MEANS to determine the %QCMPRES( available) data for %QCMPRES( summary.&&_wab_summary_memname_&wab_iter) failed.; %goto ERREXIT; %end; data _null_; set &temp_lib..min_current_date; memname="&&_wab_summary_memname_&wab_iter"; memname=strip(lowcase(memname)); time_frame=strip(lowcase(reverse(scan(reverse(memname),1,"_")))); call symput("aging_date_cutoff",strip(put(min_current_date,5.))); if time_frame = "day" then do; if strip(scan(memname,1,"_"))="visitor" then frames_in_history=-1 * &wab_visitor_days_in_history; else frames_in_history=-1 * &wab_days_in_history; min_date=intnx(time_frame ,max_current_date ,frames_in_history); call symput("aging_date_cutoff" ,strip(put(min_date,5.))); call symput('common_dates_list',"&common_dates_day"); end; else if time_frame = "week" then do; frames_in_history=-1 * &wab_weeks_in_history; min_date=intnx(strip(time_frame) || "&wab_week_shift" ,max_current_date ,frames_in_history); call symput("aging_date_cutoff" ,strip(put(min_date,5.))); call symput('common_dates_list',"&common_dates_week"); end; else if time_frame = "month" then do; frames_in_history=-1 * &wab_months_in_history; min_date=intnx(time_frame ,max_current_date ,frames_in_history ,'sameday'); call symput("aging_date_cutoff" ,strip(put(min_date,5.))); call symput('common_dates_list',"&common_dates_month"); end; else if time_frame = "qtr" then do; frames_in_history=-1 * &wab_quarters_in_history; %* the INTERVAL argument for INTNX needs to be QUARTER ; min_date=intnx('quarter' ,max_current_date ,frames_in_history ,'sameday'); call symput("aging_date_cutoff" ,strip(put(min_date,5.))); call symput('common_dates_list',"&common_dates_qtr"); end; else if time_frame = "year" then do; frames_in_history=-1 * &wab_years_in_history; min_date=intnx(time_frame ,max_current_date ,frames_in_history ,'sameday'); call symput("aging_date_cutoff" ,strip(put(min_date,5.))); call symput('common_dates_list',"&common_dates_year"); end; run; %************************************************************************; %* Create DROP_LIST to identify the _TOKENs corresponding string ; %* descriptors. DROP_LIST is used to make sure that the obs from the ; %* warehouse that have dates in common with the current ETL do not ; %* have variables that are not in the output of the initial ; %* summarization. ; %************************************************************************; proc sql; create table &temp_lib..dset_token_vars as select lowcase(name) as name from dictionary.columns where libname eq 'SUMMARY' and memname eq upcase("&&_wab_summary_memname_&wab_iter") and index(lowcase(name),'_token'); quit; %************************************************************************; %* initialize DROP_LIST to blanks ; %************************************************************************; %let drop_list=; %if %get_observation_count(indsn=&temp_lib..dset_token_vars) ne 0 %then %do; %*********************************************************************; %* re-assign DROP_LIST if there are _TOKENs string descriptors ; %*********************************************************************; data _null_; set &temp_lib..dset_token_vars end=eof; length drop_list $1000 long_var $32; retain drop_list 'drop='; long_var=strip(tranwrd(name,'_token',' ')); drop_list=strip(drop_list) || ' ' || strip(long_var); if eof then call symput('drop_list',strip(drop_list)); run; %end; %************************************************************************; %* Divide the existing summarization in the warehouse into two ; %* subsets, 1) that has the obs with dates that are in common with ; %* the current ETL and 2) that has the rest of the obs that meet ; %* the aging cutoff criterion. ; %************************************************************************; data &temp_lib..warehouse_common_dates (&drop_list) summary.&&_wab_summary_memname_&wab_iter; set summary.&&_wab_summary_memname_&wab_iter; if date in &common_dates_list then output &temp_lib..warehouse_common_dates; else if date ge &aging_date_cutoff then output summary.&&_wab_summary_memname_&wab_iter; run; %if %get_observation_count(indsn=&temp_lib..warehouse_common_dates) eq 0 %then %do; %*********************************************************************; %* no common dates -- re-summarization is not needed ; %*********************************************************************; %let need_re_summary=0; %end; %else %do; %*********************************************************************; %* NOTE: PROC Append is used in this context because it forces the ; %* BASE and the DATA files to have the same set of variables. ; %* If an Administrator wants a different set of veriables, ; %* they will need to define a new Summarization. ; %*********************************************************************; proc append base=&&_wab_summary_dsn_&wab_iter (drop=_type_) data=&temp_lib..warehouse_common_dates; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) The PROC APPEND for %QCMPRES( &&_wab_summary_dsn_&wab_iter) and %QCMPRES( &temp_lib..warehouse_common_dates) failed.; %goto ERREXIT; %end; %end; %end; %else %do; %* no counterpart in warehouse -- re-summarization is not needed ; %let need_re_summary=0; %end; data temp; length id $ 36 need_re_summary 8; id="&&_wab_re_summary_id_&wab_iter"; partition_by_date=0; need_re_summary=&need_re_summary; run; proc append base=&temp_lib..need_re_summary data=temp; run; %end; %******************************************************************************; %* Update the NEED_RE_SUMMARY indicator in the &WAB_METADSN data set with ; %* the value that has been stored in the &TEMP_LIB..NEED_RE_SUMMARY ; %* data set ; %******************************************************************************; proc sort data=&temp_lib..need_re_summary; by id partition_by_date; run; proc sort data=&wab_metadsn; by id partition_by_date; run; data &wab_metadsn; merge &wab_metadsn (drop=need_re_summary) &temp_lib..need_re_summary; by id partition_by_date; run; %******************************************************************************; %* Call the summary macro to produce PROC Summary files for nondated input ; %* files. ; %******************************************************************************; %wasumeng(meta_dsn=&wab_metadsn ,partition_flag=0 ,out_summary_file=&temp_store&separator.wab_post_summary ,retcode=wab_rc ,summcnt=wab_summary_count ); %if &wab_rc ne 0 %then %goto ERREXIT; %******************************************************************************; %* Include each file if it exists. If it does not exist fail. ; %******************************************************************************; %do wab_iter=1 %to &wab_summary_count; %let summid=%sysfunc(putn(&wab_iter,z3.)); %if %sysfunc(fileexist(&temp_store&separator.wab_post_summary&summid..sas)) %then %do; %include "&temp_store&separator.wab_post_summary&summid..sas"; %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) The Summary Engine module -- %QCMPRES( &temp_store&separator.wab_post_summary&summid..sas) %QCMPRES( -- has) been created but cannot be INCLUDEd.; %goto ERREXIT; %end; %end; %else %do; %put %unquote(&wab_error) The location for the Summary Engine %QCMPRES( module) -- &temp_store&separator.wab_post_summary&summid..sas %QCMPRES( -- does) not exist.; %goto ERREXIT; %end; %end; %******************************************************************************; %* The result of re-summarizations is a &TEMP_LIB data set with a _Y suffix. ; %* If the re-summarization was actually needed, it was done with a PROC SQL ; %* GROUP BY step. If it was not needed, the _Y data set is created by ; %* re-naming the data set created during the initial summarization. If ; %* there are _TOKEN variables, their corresponding descriptors are present ; %* in the _Y data set. ; %* ; %* At this point, the _Y data sets are appended to their SUMMARY counterparts ; %* (which have already been culled of their aged-off records earlier in ; %* the ETL). ; %******************************************************************************; %do wab_iter=1 %to &_wab_num_summary_tables_; data _null_; raw_dset="&&_wab_summary_dsn_&wab_iter"; if index("&&_wab_summary_dsn_&wab_iter",'.') then memname=scan("&&_wab_summary_dsn_&wab_iter",2,'.'); else memname=scan("&&_wab_summary_dsn_&wab_iter",1,'.'); call symput('base_dsn',strip(memname)); call symput('data_dsn',strip(memname)||'_Y'); run; proc append base=summary.&base_dsn data=&temp_lib..&data_dsn; run; %end; %******************************************************************************; %* MODULE: II_D - Append and Copy Web Analytics detail and sessions data ; %* SUPPORT: frroed ; %* Append SESSION/DETAIL data sets to their warehouse counterparts for dates ; %* that already exist in the warehouse. Copy SESSION/DETAIL data sets to ; %* the warehouse for dates that are new. ; %******************************************************************************; %let module=II_D; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %******************************************************************************; %* Append to existing SESSION/DETAIL data sets for dates that are already in ; %* the warehouse. Because new variables can be introduced at any time, ; %* the appending is done with a DATA step, not PROC APPEND. ; %******************************************************************************; %if &_wab_num_update_dates_ gt 0 %then %do; %do wab_iter=1 %to &_wab_num_update_dates_; %************************************************************************; %* Append new SESSION data sets ; %************************************************************************; data dated.session_&&_wab_update_date_&wab_iter; set dated.session_&&_wab_update_date_&wab_iter &temp_lib..session_&&_wab_update_date_&wab_iter; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) PROC APPEND for the updated %QCMPRES( session_&&_wab_update_date_&wab_iter) data set to the %QCMPRES( web) mart failed.; %goto ERREXIT; %end; %************************************************************************; %* Append new DETAIL data sets ; %************************************************************************; data wadetail.detail_&&_wab_update_date_&wab_iter; set wadetail.detail_&&_wab_update_date_&wab_iter &temp_lib..detail_&&_wab_update_date_&wab_iter; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) PROC APPEND for the updated %QCMPRES( detail_&&_wab_update_date_&wab_iter) data set to the %QCMPRES( web) mart failed.; %goto ERREXIT; %end; data wadetail.detail_tally_&&_wab_update_date_&wab_iter; set wadetail.detail_tally_&&_wab_update_date_&wab_iter &temp_lib..detail_tally_&&_wab_update_date_&wab_iter; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) PROC APPEND for the updated %QCMPRES( detail_tally_&&_wab_update_date_&wab_iter) data set to %QCMPRES( the) web mart failed.; %goto ERREXIT; %end; %************************************************************************; %* Notify that the warehouse has been updated. ; %************************************************************************; %put %unquote(&wab_note) SESSION and DETAIL for %QCMPRES( &&_wab_update_date_&wab_iter) %QCMPRES( have) been appended to earlier entries in the %QCMPRES( warehouse.); %end; %end; %******************************************************************************; %* Copy new SESSION/DETAIL data sets for dates that are not yet in the ; %* warehouse. ; %******************************************************************************; %if &_wab_num_new_dates_ gt 0 %then %do; proc copy in=&temp_lib out=dated memtype=data; select %do wab_iter=1 %to &_wab_num_new_dates_; session_&&_wab_new_date_&wab_iter %end;; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) PROC COPY for the newly created %QCMPRES( session_&&_wab_new_date_&wab_iter) data set to the web mart failed.; %goto ERREXIT; %end; proc copy in=&temp_lib out=wadetail memtype=data; select %do wab_iter=1 %to &_wab_num_new_dates_; detail_&&_wab_new_date_&wab_iter %end;; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) PROC COPY for the newly created %QCMPRES( detail_&&_wab_new_date_&wab_iter) data set to the web mart failed.; %goto ERREXIT; %end; proc copy in=&temp_lib out=wadetail memtype=data; select %do wab_iter=1 %to &_wab_num_new_dates_; detail_tally_&&_wab_new_date_&wab_iter %end;; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) PROC COPY for the newly created %QCMPRES( detail_tally_&&_wab_new_date_&wab_iter) data set to the %QCMPRES( web) mart failed.; %goto ERREXIT; %end; %do wab_iter=1 %to &_wab_num_new_dates_; %************************************************************************; %* Notify that the warehouse has been added to. ; %************************************************************************; %put %unquote(&wab_note) SESSION and DETAIL for %qcmpres( &&_wab_new_date_&wab_iter) %QCMPRES( have) been added to the warehouse.; %end; %end; %******************************************************************************; %* MODULE: II_E1 - Create Web Analytics SUMMARY.PATHING_yyyymmdd Data Sets ; %* SUPPORT: frroed ; %* Create the SUMMARY.PATHING_yyyymmdd data sets ; %******************************************************************************; %let module=II_E1; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %let wab_rc = 0; %******************************************************************************; %* Create pathing data set to be used within interactive pathing and funnels, ; %* and subset for entry and referrer paths ; %******************************************************************************; %******************************************************************************; %* Replace data for which detail in the wab warehouse has been updated. ; %* New dates will be appended to the pathing data sets for the following time ; %* frames: ; %* ; %* most recent day, most recent 7 days, and most recent 30 ; %* days. ; %* This process will depend on the creation of summary.daily_totals_day. ; %******************************************************************************; %******************************************************************************; %* Determine the data set which contains the most recent date processed. ; %******************************************************************************; %let wab_datecheck_dsn=&temp_lib..all_dates; %if %sysfunc(exist(summary.daily_totals_day)) %then %do; %if %get_observation_count(indsn=summary.daily_totals_day)=0 %then %let wab_datecheck_dsn=&temp_lib..all_dates; %else %let wab_datecheck_dsn=summary.daily_totals_day; %end; proc summary data=&wab_datecheck_dsn max; var date; output out=&temp_lib..max_date(keep=max_date) max=max_date; run; data _null_; set &temp_lib..max_date; call symput("_wab_max_date_",max_date); run; %******************************************************************************; %* Build the temporary data sets containing the top entry, and referrer ; %* pathing variables. ; %******************************************************************************; data &temp_lib..pathing(Keep=Date Datetime Session_ID Sequence Requested_File Referrer Sequenced_Requested_File Entry_Point where=(date ge (&_wab_max_date_ - &wab_pathing_days_in_history))) &temp_lib..referrer_input(keep=Date Datetime Session_ID Sequence Requested_File Referrer Referrer_URL Entry_Point rename=(Referrer_URL=Sequenced_Requested_File) where=(date ge (&_wab_max_date_ - &wab_pathing_days_in_history))); set %do wab_iter=1 %to &_wab_num_dates_; &temp_lib..detail_&&_wab_date_&wab_iter(keep=Date Datetime Session_ID Requested_File Referrer Entry_Point where=(date ge (&_wab_max_date_ -&wab_pathing_days_in_history))) %end; ; by session_id; if first.session_id then do; sequence=.; entry_pt_cnt=0; end; %***************************************************************************; %* output referrer information ; %***************************************************************************; %* NOTE: there are some rare instances when e-Data-ETL sets more than one ; %* ENTRY_POINT flag per SESSION_ID. This block makes sure that only ; %* the first setting of the ENTRY_POINT flag is used in initializing ; %* a SESSION_IDs SEQUENCE values (PROC Path requires that there not ; %* be duplicate SESSION_ID/SEQUENCE values in its source data set). ; %***************************************************************************; if entry_point eq 1 then do; if entry_pt_cnt eq 0 then do; entry_pt_cnt+1; sequence=0; end; else sequence+1; Referrer_URL=strip(strip(Referrer) || '_' || strip(compress(put(Sequence,9.)))); output &temp_lib..referrer_input; sequence+1; end; else if sequence gt . then sequence+1; if sequence gt .; Sequenced_Requested_File=strip(strip(Requested_File) || '_' || strip(compress(put(Sequence,9.)))); %***************************************************************************; %* Output path information ; %***************************************************************************; output &temp_lib..pathing; run; %******************************************************************************; %* Append the &TEMP_LIB..REFERRER_INPUT data set to &TEMP_LIB..PATHING ; %******************************************************************************; proc append base=&temp_lib..pathing data=&temp_lib..referrer_input; run; %******************************************************************************; %* sort &TEMP_LIB..PATHING so that the REFERRER obs (SEQUENCE=0) are the first ; %* ones within each session ; %******************************************************************************; proc sort data=&temp_lib..pathing; by session_id sequence; run; %if %get_observation_count(indsn=&temp_lib..pathing)=0 %then %do; %let wab_rc = 1; %put %unquote(&wab_error) &temp_lib..pathing has 0 observations.; %end; %******************************************************************************; %* Create one record per SESSION_ID and ; %* REQUESTED_FILE/SEQUENCED_REQUESTED_FILE (SESSION_ID is a CLASS ; %* variable in this initial step so that SESSION_HIT_COUNT can be created ; %* later on) ; %******************************************************************************; proc summary data=&temp_lib..pathing (keep=date sequenced_requested_file requested_file session_id) missing chartype sum; class date requested_file sequenced_requested_file session_id; types date*sequenced_requested_file date*requested_file*session_id ; output out=&temp_lib.._wab_seq_urls ( where=(_type_ eq '1010') drop=_freq_ ); output out=&temp_lib.._wab_page_sessions( where=(_type_ eq '1101') drop=_freq_ ); run; %******************************************************************************; %* Create the formats for REQUESTED_FILE ; %******************************************************************************; %* Create a place-holding version of SUMMARY.PAGE_FREQUENCIES if the actual ; %* data set does not exist ; %******************************************************************************; %if %sysfunc(exist(summary.page_frequencies)) eq 0 %then %do; data summary.page_frequencies; attrib start length=$1024 label length=$1024 date length=8 format =date9. id length=$36 format =$36. informat=$36. label='ID for metadata maintenance' session_hit_count length=8 fmtname length=$8 ; stop; run; %end; %******************************************************************************; %* Create a place-holding version of SUMMARY.PAGE_FREQUENCIES_OBSOLETE if the ; %* actual data set does not exist ; %******************************************************************************; %if %sysfunc(exist(summary.page_frequencies_obsolete)) eq 0 %then %do; data summary.page_frequencies_obsolete; attrib start length=$1024 label length=$1024 date length=8 format =date9. id length=$36 format =$36. informat=$36. label='ID for metadata maintenance' session_hit_count length=8 fmtname length=$8 ; stop; run; %end; %******************************************************************************; %* Create one record per REQUESTED_FILE and count the number of sessions ; %* in &TEMP_LIB...PATHING that reference it. ; %******************************************************************************; proc summary data=&temp_lib.._wab_page_sessions (drop=_type_) chartype sum; class date requested_file; output out=&temp_lib.._wab_urls (where=(_type_ eq '11') rename=(_freq_=session_hit_count) ); run; %******************************************************************************; %* Create a list of the URLs that have already been inventoried (NOTE: ; %* inventoried URLs that are no longer part of a Web site -- have not been ; %* referred to in &DETAIL for &WAB_PATHING_DAYS_IN_HISTORY days -- are ; %* stored in SUMMARY.PAGE_FREQUENCIES_OBSOLETE. ; %******************************************************************************; data &temp_lib..current_page_inventory; set summary.page_frequencies summary.page_frequencies_obsolete; run; %******************************************************************************; %* NOTE: the NEXT_REF macro variable is created in section I_B above from the ; %* REFERENCE value stored in the SUMMARY.NEXT_PAGE_REFERENCE data set ; %******************************************************************************; %PUT next_ref: >>>&next_ref<<<; %******************************************************************************; %* Create &TEMP_LIB.._WAB_URL_INVENTORY by concatenating &TEMP_LIB.._WAB_URLS ; %* to &TEMP_LIB..CURRENT_PAGE_INVENTORY ; %******************************************************************************; data &temp_lib.._wab_url_inventory; set &temp_lib.._wab_urls (drop=_type_ rename=(requested_file=label) ) &temp_lib..current_page_inventory; run; %******************************************************************************; %* Sort by Descending START so that already-assigned STARTs appear first. ; %******************************************************************************; proc sort data=&temp_lib.._wab_url_inventory; by label descending start; run; %******************************************************************************; %* Create SUMMARY.PAGE_FREQUENCIES and SUMMARY.PAGE_FREQUENCIES_OBSOLETE. ; %* the REQUESTED_FILEs that are being inventoried for the first time are ; %* given a START/Id whose values begin with &NEXT_REF ; %******************************************************************************; data summary.page_frequencies_obsolete summary.page_frequencies &temp_lib..raw_page_inventory (keep=start label) ; set &temp_lib.._wab_url_inventory end=eof; by label; retain hold_start max_date; %* decrement NEXT_REF to initialize the value of INDEX ; retain index %eval(&next_ref-1) new_rec ; if first.label then do; ttl_session_hit_count=0; if start eq ' ' then do; new_rec=1; index+1; hold_start=strip(put(index,12.)); end; else do; hold_start=start; new_rec=0; end; max_date=.; end; ttl_session_hit_count+session_hit_count; if date gt max_date then max_date=date; %* make sure that the START/LABEL associations do not vary ; if start gt ' ' and strip(start) ne strip(hold_start) then put @ 1 'ERROR: START/LABEL association has varied: ' +1 start= +1 hold_start= +1 date= date9.; if last.label then do; fmtname='$idxtorf'; start=strip(hold_start); id=strip(start); session_hit_count=ttl_session_hit_count; date=max_date; if date ge (&_wab_max_date_ - &wab_pathing_days_in_history) then output summary.page_frequencies; else output summary.page_frequencies_obsolete; if new_rec then output &temp_lib..raw_page_inventory; end; drop index max_date ttl_session_hit_count hold_start ; if eof then do; %* add 1 to INDEX to create the NEXT_REF value ; index+1; call symput('next_ref',strip(put(index,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; %******************************************************************************; %* add the contents of RAW_PAGE_INVENTORY to RECOVERY.PAGE_INVENTORY ; %******************************************************************************; data &temp_lib..page_inventory (keep=requested_file reference); length requested_file $ 1024 reference 8; set &temp_lib..raw_page_inventory; requested_file=label; reference=input(start,12.); run; proc append base=recovery.page_inventory data=&temp_lib..page_inventory; run; %******************************************************************************; %* Create the companion format to translate the numeric IDs back into their ; %* corresponding REQUESTED_FILEs. ; %******************************************************************************; data &temp_lib..cntlin_urltoind_ (keep=label start fmtname ); set summary.page_frequencies; length fmtname $ 8 requested_file $ 1024; %***************************************************************************; * Format to convert requested_file value to index number ; %***************************************************************************; requested_file=label; label=start; start=requested_file; fmtname='$url2idx'; output; run; %if &syserr gt 4 %then %do; %let retcode=1; %put %unquote(&wab_error) REQUESTED_FILEs cannot be created %QCMPRES(for Interactive) Funnel.; %goto ERREXIT; %end; proc format cntlin=&temp_lib..cntlin_urltoind_; run; proc format cntlin=summary.page_frequencies; run; %******************************************************************************; %* Create the formats for SEQUENCED_REQUESTED_FILE ; %* NOTE: because these formats are NOT used in saved Funnel or Path ; %* Definitions, the way that the REQUESTED_FILE formats are, their ; %* references do not need to be maintained across ETLs. Therefore, ; %* these formats are created fresh for each ETL, which is a much ; %* simpler process than the one for the REQUESTED_FILE formats. ; %******************************************************************************; %* Create a place-holding version of SUMMARY.CNTLIN_INDTOSEQURL if the actual ; %* data set does not exist ; %******************************************************************************; %if %sysfunc(exist(summary.cntlin_indtosequrl_)) eq 0 %then %do; data summary.cntlin_indtosequrl_; attrib start length=$1037 label length=$1024 fmtname length=$8 ; stop; run; %end; data &temp_lib.._wab_seq_url_combined; set &temp_lib.._wab_seq_urls (keep=sequenced_requested_file) summary.cntlin_indtosequrl_ (keep=label rename=(label=sequenced_requested_file) ); run; proc sort data=&temp_lib.._wab_seq_url_combined out =&temp_lib.._wab_seq_url_inventory nodupkeys; by sequenced_requested_file; run; data &temp_lib..cntlin_sequrltoind_ summary.cntlin_indtosequrl_; length start $1037 label $1037; set &temp_lib.._wab_seq_url_inventory; length fmtname $ 8; index+1; start=sequenced_requested_file; label=strip(put(index,12.)); fmtname='$srl2idx'; output &temp_lib..cntlin_sequrltoind_; start=strip(put(index,12.)); label=sequenced_requested_file; fmtname='$idx2srl'; output summary.cntlin_indtosequrl_; keep start label fmtname; run; %if &syserr gt 4 %then %do; %let retcode=1; %put %unquote(&wab_error) CNTLIN_SEQURLTOIND_ cannot be written %QCMPRES(to the) SUMMARY library.; %put %unquote(&wab_error) Top Entry Paths cannot be created.; %goto ERREXIT; %end; proc format cntlin=&temp_lib..cntlin_sequrltoind_; run; proc format cntlin=summary.cntlin_indtosequrl_; run; %******************************************************************************; %* Identify all the dates that are in TEMP_LIB..PATHING ; %* TEMP_LIB..PATHING_SESS_COUNT contains one record for each pathing date ; %* in the current ETL with the tally of pathing sessions for that date. ; %* it avoids having to re-count PATHING sessions whenever this ; %* information is needed. ; %******************************************************************************; proc summary data=&temp_lib..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 &temp_lib..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; %******************************************************************************; %* create the PATHDATE_ and SELECT_WHEN macro variables to create the date- ; %* specific SUMMARY.PATHING_yyyymmdd data sets ; %******************************************************************************; data _null_; set &temp_lib..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 " || '&TEMP_LIB..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; %******************************************************************************; %* create the date-specific PATHING_yyyymmdd data sets (they are created in ; %* TEMP_LIB first and then appended to the corresponding ; %* SUMMARY.PATHING_yyyymmdd data set. if the corresponding SUMMARY data ; %* set does not yet exist, it is created. ; %******************************************************************************; data %do i=1 %to &pathdate_cnt; &temp_lib..pathing_&&pathdate_&i (bufsize=256k) %end; ; length reference sequenced_reference $ 12; set &temp_lib..pathing; by session_id notsorted; retain sess_date; %***************************************************************************; %* assign all obs for a session to the date of its first request. this ; %* way, sessions that span dates will all be part of the same ; %* PATHING_yyyymmdd data set. ; %***************************************************************************; if first.session_id then sess_date=date; reference=strip(put(requested_file,$url2idx.)); sequenced_reference=strip(put(sequenced_requested_file,$srl2idx.)); select (sess_date); &select_when otherwise; end; drop sess_date referrer requested_file sequenced_requested_file; run; %******************************************************************************; %* get a list of dates that are already in SUMMARY.PATHING_yyyymmdd data sets ; %******************************************************************************; proc sql; create table &temp_lib..char_pathing_dates as select substr(memname,9) as pathing_date_c ,input(substr(memname,9,4),4.) as yyyy ,input(substr(memname,13,2),2.) as mm ,input(substr(memname,15,2),2.) as dd from dictionary.tables where libname="SUMMARY" and memname ne 'PATHING' and memname like 'PATHING_%' and notdigit(trim(memname),9)=0 order by pathing_date_c; quit; %******************************************************************************; %* identify the existing PATHING_yyyymmdd data sets that need to be retained ; %* and the ones that need to be aged off because they are too old ; %******************************************************************************; data &temp_lib..retained_pathing_dates &temp_lib..too_old_pathing_dates; set &temp_lib..char_pathing_dates; date=mdy(mm,dd,yyyy); if date gt (&_wab_max_date_ - &wab_pathing_days_in_history) then output &temp_lib..retained_pathing_dates; else output &temp_lib..too_old_pathing_dates; keep pathing_date_c date; run; %******************************************************************************; %* age off - delete - the SUMMARY.PATHING_yyyymmdd data sets that are too old ; %******************************************************************************; %if %get_observation_count(indsn=&temp_lib..too_old_pathing_dates) gt 0 %then %do; data _null_; set &temp_lib..too_old_pathing_dates end=eof; call symput('too_old_path_' || strip(put(_n_,2.)) ,strip(pathing_date_c) ); if eof then do; call symput('too_old_path_cnt' ,strip(put(_n_,2.)) ); end; run; proc datasets library=summary nowarn nolist; %do i=1 %to &too_old_path_cnt; delete pathing_&&too_old_path_&i; %end; quit; %end; %******************************************************************************; %* append the TEMP_LIB..PATHING_yyyymmdd data sets from the current ETL to the ; %* corresponding data sets in the SUMMARY library. if the corresponding ; %* data set is not yet in SUMMARY, the TEMP_LIB version is copied there. ; %******************************************************************************; %let etl_path_cnt=0; data _null_; set &temp_lib..pathing_sess_count end=eof; call symput('etl_path_' || strip(put(_n_,2.)) ,strip(put(date,yymmddn8.)) ); if eof then do; call symput('etl_path_cnt' ,strip(put(_n_,2.)) ); end; run; %do i=1 %to &etl_path_cnt; proc append base=summary.pathing_&&etl_path_&i data=&temp_lib..pathing_&&etl_path_&i; run; %end; %******************************************************************************; %* Concatenate the historical information for date-based Pathing session ; %* counts with the tallies from the current ETL ; %******************************************************************************; %if %sysfunc(exist(summary.pathing_sess_count)) ne 0 %then %do; proc append base=&temp_lib..pathing_sess_count data=summary.pathing_sess_count; run; %end; %******************************************************************************; %* Re-summarize the concatenated Pathing session counts and keep only those ; %* that are within the last WAB_PATHING_DAYS_IN_HISTORY ; %******************************************************************************; proc summary data=&temp_lib..pathing_sess_count (where=(date gt (&_wab_max_date_ - &wab_pathing_days_in_history ) ) ) nway; class date; var sess_count last_dttm; output out=summary.pathing_sess_count (keep=date sess_count max_last rename=(max_last=last_dttm) ) sum= max(last_dttm)=max_last; run; %******************************************************************************; %* MODULE: II_E2 - TOP_REFERRER_PATHS_HX & TOP_ENTRY_PATHS_HX ; %* SUPPORT: frroed ; %* Create/update the TOP_REFERRER_PATHS_HX and TOP_ENTRY_PATHS_HX data sets ; %******************************************************************************; %let module=II_E2; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %******************************************************************************; %* assign a macro variable to control SUPPORT settings for this modules PROC ; %* Path calls. ; %******************************************************************************; %let ii_e2_path_support=3; %******************************************************************************; %* 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 (they are also the latest date/time ; %* in the PATHING_SESS_COUNT data set). ; %******************************************************************************; proc sql noprint; select strip(put(max(last_dttm),best.)) into :etl_last_dttm from summary.pathing_sess_count; quit; data _null_; set summary.pathing_sess_count end=eof; 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; %do i=1 %to &extr_date_cnt; %***************************************************************************; %* TOP_ENTRY_PATHS_HX ; %* NOTE: SUPPORT has been hardcoded as 3 so that results will not be ; %* affected by increases/decreases in the WAB_Rx_SUPPORT values ; %***************************************************************************; 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=&ii_e2_path_support items=&wab_itemset_size; customer session_id; sequence sequence / max=&wab_max_path_pages; target sequenced_reference; funnel out = &temp_lib..top_entry_funnels_A&&extr_date_&i; run; %***************************************************************************; %* make sure that TOP_ENTRY_PATHS_A has &WAB_ITEMSET_SIZE ITEMx columns ; %* NOTE: $9 is the standard attribute for ITEMx columns in ; %* TOP_ENTRY_PATHS_HX ; %***************************************************************************; data &temp_lib..top_entry_paths_A&&extr_date_&i; length item1-item&wab_itemset_size $ 9; set &temp_lib..top_entry_paths_A&&extr_date_&i; run; data &temp_lib..top_entry_paths_B&&extr_date_&i; 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; length rule $ 100; 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. ; %* ; %* There is a filter for the output SUMMARY.TOP_ENTRY_PATHS_HX data ; %* set to age off the records older than the retention threshold. ; %* ; %* There is a filter for the input SUMMARY.TOP_ENTRY_PATHS_HX data ; %* set so that any earlier results for the date will be replaced. ; %* Otherwise, those earlier results would be double-counted. ; %***************************************************************************; %if not %sysfunc(exist(summary.top_entry_paths_hx)) %then %do; data summary.top_entry_paths_hx (where=(date gt (&_wab_max_date_ - &wab_pathing_days_in_history))); set &temp_lib..top_entry_paths_C&&extr_date_&i; length item_new $ 1037; %*********************************************************************; %* 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)); drop item_new; run; %end; %else %do; data summary.top_entry_paths_hx (where=(date gt (&_wab_max_date_ - &wab_pathing_days_in_history))); set summary.top_entry_paths_hx (where=(date ne input("&&extr_date_&i",yymmdd8.))) &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; %end; %***************************************************************************; %* TOP_REFERRER_PATHS_HX ; %* NOTE: SUPPORT has been hardcoded as 3 so that results will not be ; %* affected by increases/decreases in the WAB_Rx_SUPPORT values ; %***************************************************************************; 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=&ii_e2_path_support items=&wab_itemset_size; customer session_id; sequence sequence / max=&wab_max_path_pages; target sequenced_reference; funnel out = &temp_lib..top_referrer_funnels_A&&extr_date_&i; run; %***************************************************************************; %* make sure that TOP_REFERRER_PATHS_A has &WAB_ITEMSET_SIZE ITEMx columns ; %* NOTE: $9 is the standard attribute for ITEMx columns in ; %* TOP_REFERRER_PATHS_HX ; %***************************************************************************; data &temp_lib..top_referrer_paths_A&&extr_date_&i; length item1-item&wab_itemset_size $ 9; set &temp_lib..top_referrer_paths_A&&extr_date_&i; run; data &temp_lib..top_referrer_paths_B&&extr_date_&i; 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; length rule $ 100; 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. ; %* ; %* There is a filter for the output SUMMARY.TOP_REFERRER_PATHS_HX ; %* SET set to age off the records older than the retention ; %* threshold. ; %* ; %* There is a filter for the input SUMMARY.TOP_REFERRER_PATHS_HX data ; %* set so that any earlier results for the date will be replaced. ; %* Otherwise, those earlier results would be double-counted. ; %***************************************************************************; %if not %sysfunc(exist(summary.top_referrer_paths_hx)) %then %do; data summary.top_referrer_paths_hx (where=(date gt (&_wab_max_date_ - &wab_pathing_days_in_history))); set &temp_lib..top_referrer_paths_C&&extr_date_&i; length item_new $ 1037; %*********************************************************************; %* 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)); drop item_new; run; %end; %else %do; data summary.top_referrer_paths_hx (where=(date gt (&_wab_max_date_ - &wab_pathing_days_in_history))); set summary.top_referrer_paths_hx (where=(date ne input("&&extr_date_&i",yymmdd8.))) &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; %end; %***************************************************************************; %* Create lists of path entry and ending points ; %* NOTE: because these lists are derived from PROC Path output that ; %* uses REFERENCE for TARGET, and REFERENCE is assigned from ; %* the stable REQUESTED_FILE/REFERENCE association in the ; %* $IDXTORF format, these REFERENCEs can also be translated by ; %* the $IDXTORF format. ; %***************************************************************************; proc path data=summary.pathing_&&extr_date_&i (where=(sequence gt 0) keep=session_id sequence date reference ) out=&temp_lib..proc_path_out_&&extr_date_&i support=&wab_interactive_support items=2; customer session_id; sequence sequence / max=&wab_max_path_pages; target reference; run; %***************************************************************************; %* Determine if any paths found ; %***************************************************************************; %if %get_observation_count(indsn=&temp_lib..proc_path_out_&&extr_date_&i) eq 0 %then %do; %put %unquote(&wab_warning) &TEMP_LIB..PROC_PATH_OUT contains %QCMPRES( 0) records. The interactive support at &wab_interactive_support; %put %unquote(&wab_warning) is set too high and needs to be %QCMPRES( set) to a lower number.; %put %unquote(&wab_warning) To change the interactive support %QCMPRES( number) change the value of; %put %unquote(&wab_warning) WAB_INTERACTIVE_SUPPORT in %QCMPRES( WACONFIG) and rerun WAPATHDP outside of; %put %unquote(&wab_warning) WAETL.; %end; %else %do; %************************************************************************; %* create the final lists of pathing start pages (ITEM1s) ; %************************************************************************; proc sort data=&temp_lib..proc_path_out_&&extr_date_&i (keep=item1) out =&temp_lib..uniq_start_list_&&extr_date_&i nodupkeys; by item1; run; data &temp_lib..path_start_list_&&extr_date_&i; set &temp_lib..uniq_start_list_&&extr_date_&i %if %sysfunc(exist(summary.path_start_list)) ne 0 %then %do; summary.path_start_list %end; ; length requested_file $1024; index=input(item1,12.); requested_file=put(strip(item1),$idxtorf.); keep index requested_file; run; proc sort data=&temp_lib..path_start_list_&&extr_date_&i out =summary.path_start_list nodupkeys; by requested_file; run; %************************************************************************; %* create the final lists of pathing end pages (ITEM2s) ; %************************************************************************; proc sort data=&temp_lib..proc_path_out_&&extr_date_&i (keep=item2) out =&temp_lib..uniq_end_list_&&extr_date_&i nodupkeys; by item2; run; data &temp_lib..path_end_list_&&extr_date_&i; set &temp_lib..uniq_end_list_&&extr_date_&i %if %sysfunc(exist(summary.path_end_list)) ne 0 %then %do; summary.path_end_list %end; ; length requested_file $1024; index=input(item2,12.); requested_file=put(strip(item2),$idxtorf.); keep index requested_file; run; proc sort data=&temp_lib..path_end_list_&&extr_date_&i out =summary.path_end_list nodupkeys; by requested_file; run; %end; %* %ELSE %DO ---------------------------------------------------- ; %end; %* %DO I=1 %TO &EXTR_DATE_CNT -------------------------------------- ; %******************************************************************************; %* MODULE: II_E3 - Create Web Analytics Path Analysis Data ; %* SUPPORT: cabahl ; %* Create the top entry paths and top referrer paths summary data sets for 1, ; %* 7, and 30 days. ; %******************************************************************************; %let module=II_E3; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %if %upcase(&wapathdp_sw) eq Y %then %do; %wapathdp(swamart=&swamart ,waetl_sw=Y ,support_r1=&wab_r1_support ,support_r7=&wab_r7_support ,support_r30=&wab_r30_support ,retcode=wab_rc ); %end; %else %do; %put %unquote(&wab_note) The WAPATHDP macro is being bypassed %QCMPRES( because) WAETL was invoked with WAPATHDP_SW equal to &WAPATHDP_SW..; %end; %******************************************************************************; %* MODULE: II_F - Data for Interactive Funnels ; %* SUPPORT:frroed ; %* Process Create/maintain the data that supports the REQUESTED_FILE list for ; %* the Interactive Funnel Reports. ; %******************************************************************************; %let module=II_F; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; data summary.page_dates; attrib date format=date9.; set summary.pathing_sess_count; date_c=put(date,date9.); keep date date_c; run; %******************************************************************************; %* MODULE: II_G - Scorecard ; %* SUPPORT:sassyw ; %* Process the scorecard based on the wascrcrd metadata data set. ; %******************************************************************************; %let module=II_G; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %wascordp(begin_date=&_wab_min_process_date_ ,end_date=&_wab_max_process_date_ ,definition_to_run=_etl_ ,retcode=wab_rc); %if &wab_rc ne 0 %then %goto ERREXIT; %******************************************************************************; %* MODULE: II_H - Dashboard ; %* SUPPORT:sassyw ; %* Process the dashboard based on the wadshbrd metadata data set. ; %******************************************************************************; %let module=II_H; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %wadashdp(begin_date=&_wab_min_process_date_ ,end_date=&_wab_max_process_date_ ,definition_to_run=_etl_ ,retcode=wab_rc ); %if &wab_rc ne 0 %then %goto ERREXIT; %******************************************************************************; %* MODULE: II_I - Data for Interactive Pathing ; %* SUPPORT:frroed ; %* Process Create/maintain the data that supports the two REQUESTED_FILE lists ; %* (one for path starts, one for path ends) for Interactive Pathing. ; %******************************************************************************; %* NOTE: this module has been replaced by the %WAPATHDP macro ; %******************************************************************************; %******************************************************************************; %* MODULE: II_J - Create tallies for Visitors/Repeat Visitors ; %* SUPPORT:cabahl ; %* Process Create/maintain SUMMARY data sets with tallies for Visitors/ ; %* Repeat Visitors. ; %******************************************************************************; %let module=II_J; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %wavisitr(min_date=&_wab_min_process_date_ ,max_date=&_wab_max_process_date_ ,retcode=wab_rc ); %if &wab_rc ne 0 %then %goto ERREXIT; %******************************************************************************; %* MODULE: II_K - Automatic Segmentation ; %* SUPPORT:frroed ; %* Perform analytic segmentation based on all instances specified in the. ; %* config.waautosg data set. The purpose of analytic segmenatation is to relate; %* a set of input/predictor variables to a target/response variable. Analytic ; %* segmentation partitions the input variables in such a way that variation in ; %* reponse variable is minimized. ; %******************************************************************************; %let module=II_K; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %waautosg(begin_date=&_wab_min_process_date_ ,end_date=&_wab_max_process_date_ ,definition_to_run=_etl_ ,retcode=wab_rc ); %if &wab_rc ne 0 %then %goto ERREXIT; %******************************************************************************; %* MODULE: II_L - Static Funnels ; %* SUPPORT: frroed ; %* Create the SUMMARY FUNNELS_R1, FUNNELS_R7, and FUNNELS_R30 data sets ; %******************************************************************************; %let module=II_L; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %******************************************************************************; %* make sure that CONFIG.WAFUNNEL exists ; %******************************************************************************; %if %sysfunc(exist(config.wafunnel)) = 0 %then %do; %put %unquote(&wab_error) The CONFIG.WAFUNNEL data set does not exist.; %put %unquote(&wab_error) Static Funnels cannot be created.; %goto ERREXIT; %end; %******************************************************************************; %* make sure that CONFIG.WAFUNNEL can be opened. ; %******************************************************************************; %locktest(dset=config.wafunnel); %if &lock_sw ne 0 %then %do; %put %unquote(&wab_error) There was a failure opening CONFIG.WAFUNNEL.; %put %unquote(&wab_error) Static Funnels cannot be created.; %goto ERREXIT; %end; %******************************************************************************; %* make sure that CONFIG.WAFUNNEL has observations. ; %******************************************************************************; %if %get_observation_count(indsn=config.wafunnel)=0 %then %do; %put %unquote(&wab_warning) CONFIG.WAFUNNEL contains 0 observations.; %put %unquote(&wab_warning) No Static Funnels have been defined.; %let _wab_static_funnel_count=0; %goto II_L_EXIT; %end; %******************************************************************************; %* Retrieve metadata about Static Funnels ; %******************************************************************************; data _null_; set config.wafunnel end=eof; retain static_cnt 0; if IsStatic then do; static_cnt+1; call symput('_wab_static_funnel_id_' || strip(put(static_cnt,3.)) ,strip(id) ); call symput('_wab_static_funnel_url_' || strip(put(static_cnt,3.)) ,strip(url) ); call symput('_wab_static_funnel_adj_' || strip(put(static_cnt,3.)) ,strip(adjacent) ); end; if eof then call symput('_wab_static_funnel_count',strip(put(static_cnt,3.))); run; %******************************************************************************; %* Process each Static Funnel ; %******************************************************************************; data _null_; %***************************************************************************; %* create a unique id for the DSNs used to create the funnel report ; %***************************************************************************; call symput("_funnel_data_uid_" ,compress( translate( strip(put(mod(datetime(),10**6),6.)) || "_" || strip(put(ranuni(-1),7.6)) ,' ' ,'.' ) ) ); run; %do ii_l_i=1 %to &_wab_static_funnel_count; %***************************************************************************; %* Create the LEVELn and xTHENy macro variables needed by the current ; %* Static Funnel. ; %***************************************************************************; data _null_; length urls adjs $255 url $12 adj $2 u_list1-u_list7 $10 a_list1-a_list6 $13 ; array u_list {1:7} $ u_list1-u_list7; array a_list {1:6} $ a_list1-a_list6; retain u_list1 'levelOne' u_list2 'levelTwo' u_list3 'levelThree' u_list4 'levelFour' u_list5 'levelFive' u_list6 'levelSix' u_list7 'levelSeven' a_list1 'oneThenTwo' a_list2 'twoThenThree' a_list3 'threeThenFour' a_list4 'fourThenFive' a_list5 'fiveThenSix' a_list6 'sixThenSeven' ; urls="&&_wab_static_funnel_url_&ii_l_i"; adjs="&&_wab_static_funnel_adj_&ii_l_i"; %* NOTE: there should be one more URL than ADJ indicator ; num_url_commas=countc(urls,','); num_adj_commas=countc(adjs,','); do i=1 to (1+num_url_commas); url=scan(urls,i,','); call symput(strip(u_list{i}),strip(url)); end; do j=i to 7; call symput(strip(u_list{j}),' '); end; do i=1 to (1+num_adj_commas); adj=scan(adjs,i,','); if strip(adj) eq '1' then adj='on'; else adj=' '; call symput(strip(a_list{i}),strip(adj)); end; do j=i to 6; call symput(strip(a_list{j}),' '); end; run; %***************************************************************************; %* Create the three DATES macro variables for the current Static Funnel. ; %* NOTE: if the PATHING data has fewer than 7 days, then the DATES macro ; %* variable for the 7-day interval will list all the dates that ; %* are in the SUMMARY.PATHING_yyyymmdd data sets. ; %* also, if the PATHING data has fewer than 30 days, then the ; %* DATES macro variable for the 30-day interval will list all the ; %* dates that are in the SUMMARY.PATHING_yyyymmdd data sets. ; %***************************************************************************; proc sort data=summary.pathing_sess_count (keep=date) out =path_dates; by descending date; run; data _null_; set path_dates end=eof; length dates date_list_01 date_list_07 date_list_30 $ 1024; retain dates date_list_01 date_list_07 date_list_30 ; if _n_ eq 1 then do; dates=put(date,yymmdd10.); date_list_01=dates; date_list_07=dates; date_list_30=dates; end; else do; dates=strip(dates) || ',' || put(date,yymmdd10.); if _n_ le 7 then date_list_07=dates; if _n_ le 30 then date_list_30=dates; end; if eof then do; call symput('_wab_static_funnel_date_list_1',strip(date_list_01)); call symput('_wab_static_funnel_date_list_2',strip(date_list_07)); call symput('_wab_static_funnel_date_list_3',strip(date_list_30)); end; run; %let rpt_1=R1; %let rpt_2=R7; %let rpt_3=R30; %***************************************************************************; %* Create the _R1, _R7, and _R30 reports for the current Static Funnel. ; %***************************************************************************; %do j=1 %to 3; %let dates=&&_wab_static_funnel_date_list_&j; %Create_Funnel(rpt_lib=work ); %************************************************************************; %* add the ID column to the Funnel Report. ; %************************************************************************; data &temp_lib..funnel_rpt; length id $ 36; attrib date label='Latest Date in SUMMARY.PATHING_yyyymmdd' length=8 format=date9.; set work.fun_f&_funnel_data_uid_; id="&&_wab_static_funnel_id_&ii_l_i"; date=input("&_wab_static_funnel_date_list_1",yymmdd10.); run; %************************************************************************; %* append current report to the TEMP_LIB copy of _R1/_R7/_R30 ; %************************************************************************; proc append base=&temp_lib..funnels_&&rpt_&j data=&temp_lib..funnel_rpt; run; %end; %***************************************************************************; %* Sort the appended _R1, _R7, and _R30 reports and store in SUMMARY ; %***************************************************************************; %do i=1 %to 3; proc sort data=&temp_lib..funnels_&&rpt_&i out =summary.funnels_&&rpt_&i; by id seq; run; %end; %end; %II_L_EXIT:; %******************************************************************************; %* SECTION: III - Housekeeping ; %******************************************************************************; %******************************************************************************; %* MODULE: III_A - Age Detail and Session Data sets ; %* SUPPORT:saskxs ; %* Remove Detail and Session Data sets older than a certain age range. ; %* By default 7 days of detail and 14 days of sessions are stored. ; ; %******************************************************************************; %let module=III_A; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; proc sort data=&temp_lib..detail_library_contents; by descending date ; run; data _null_; call symput("_wab_detail_count_",'0'); run; data _null_; retain detail_data_count 0; set &temp_lib..detail_library_contents; detail_data_name=strip(compress(memname)); if _n_ ge &wab_num_detail_data_sets then do; detail_data_count=detail_data_count+1; call symput(compress("_wab_detail_name_" ||strip(put(detail_data_count,7.))) ,detail_data_name); tally_data_name=tranwrd(upcase(detail_data_name) ,'DETAIL_' ,'DETAIL_TALLY_' ); call symput(compress("_wab_tally_name_" ||strip(put(detail_data_count,7.))) ,tally_data_name); end; call symput("_wab_detail_count_",strip(put(detail_data_count,7.))); run; %if &_wab_detail_count_ ge 1 %then %do; proc datasets library=wadetail nolist; delete %do wab_iter=1 %to &_wab_detail_count_; &&_wab_detail_name_&wab_iter %end;; run; quit; %end; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) Detail data sets in the web mart could %QCMPRES( not) be aged.; %goto ERREXIT; %end; %if &_wab_detail_count_ ge 1 %then %do; proc datasets library=wadetail nolist; delete %do wab_iter=1 %to &_wab_detail_count_; &&_wab_tally_name_&wab_iter %end;; run; quit; %end; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) Detail _TALLY data sets in the web mart %QCMPRES( could) not be aged.; %goto ERREXIT; %end; proc sort data=&temp_lib..dated_library_contents; by descending date ; run; data _null_; call symput("_wab_session_count_",'0'); run; data _null_; retain session_data_count 0; set &temp_lib..dated_library_contents; session_data_name=strip(compress(memname)); if _n_ ge &wab_num_session_data_sets then do; session_data_count=session_data_count+1; call symput(compress("_wab_session_name_" ||strip(put(session_data_count,7.))) ,session_data_name); end; call symput("_wab_session_count_" ,strip(put(session_data_count,7.))); run; %put &dated_msg; %if &_wab_session_count_ ge 1 %then %do; proc datasets library=dated nolist; delete %do wab_iter=1 %to &_wab_session_count_; &&_wab_session_name_&wab_iter %end;; quit; %end; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) Session data sets in the web mart could %QCMPRES( not) be aged.; %goto ERREXIT; %end; %******************************************************************************; %* MODULE: III_B - Create Updated WAENGVAR ; %* SUPPORT:frroed ; %* Process: Create an updated version of WAENGVAR from the variables in the ; %* DETAIL_yyyymmdd and SESSION_yyyymmdd data sets. ; %******************************************************************************; %let module=III_B; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %* assign libraries (in UPCASE) where DETAIL and SESSION data sets are located ; %let det_lib=WADETAIL; %let ses_lib=DATED; %wametdef(def_dsn=waengvar ,retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Update failure executing WAMETDEF.; %goto ERREXIT; %end; %******************************************************************************; %* MODULE: III_C - Create the _STATUS Reports ; %* SUPPORT:frroed ; %* Process Create/maintain a data set that contains date ranges for Traffic ; %* and Decision Support Reports. ; %* ; %* NOTE: %WASTATUS is also invoked by Module IV_A. Logically, either III_C ; %* (normal termination) or IV_A (error exit) will be executed. ; %******************************************************************************; %let module=III_C; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %wastatus(swamart=&swamart ,waetl_sw=Y ,retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Failure executing WASTATUS.; %goto ERREXIT; %end; %******************************************************************************; %* MODULE: III_D - Generate List Data Sets for UI Selection Lists ; %* SUPPORT: frroed ; %* Process: Create/maintain data sets that contain selection list entries. ; %* ; %* NOTE: %WALISTS is also invoked by Module IV_B. Logically, either III_D ; %* (normal termination) or IV_B (error exit) will be executed. ; %******************************************************************************; %let module=III_D; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %walists(retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Failure executing WALISTS.; %goto ERREXIT; %end; %******************************************************************************; %* Notify that WAETL is completed successfully. ; %******************************************************************************; %put %unquote(&wab_note) WAETL completed successfully.; %goto EXIT; %******************************************************************************; %* Exit point when fatal errors encountered. ; %******************************************************************************; %ERREXIT: %put %unquote(&wab_error) Web Analytics program exiting due to error.; %******************************************************************************; %* SECTION: IV - Post-processing ; %******************************************************************************; %******************************************************************************; %* MODULE: IV_A - Create the _STATUS Reports ; %* SUPPORT:frroed ; %* Process Create/maintain a data set that contains date ranges for Traffic ; %* and Decision Support Reports. ; %* ; %* NOTE: %WASTATUS is also invoked by Module III_C. Logically, either III_C ; %* (normal termination) or IV_A (error exit) will be executed. ; %******************************************************************************; %let module=IV_A; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %wastatus(swamart=&swamart ,waetl_sw=Y ,retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Post-Process failure executing WASTATUS.; %end; %******************************************************************************; %* MODULE: IV_B - Generate List Data Sets for UI Selection Lists ; %* SUPPORT: frroed ; %* Process: Create/maintain data sets that contain selection list entries. ; %* ; %* NOTE: %WALISTS is also invoked by Module IV_B. Logically, either III_D ; %* (normal termination) or IV_B (error exit) will be executed. ; %******************************************************************************; %let module=IV_B; %let macname=%scan(&macname,1,_)_&module; %put macname: >>>&macname<<<; %walists(retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Post-Process failure executing WALISTS.; %end; %No_Notes; %Maybe_Notes; %* Reset system options to original settings ; dm 'af catalog=SASHELP.WAGUI.WATRM.SCL'; options COMPRESS=&compress_setting; options &ovp_setting; options OBS=&obs_setting; options &mprint_setting; options &source2_setting; options &source_setting; options ¬es_setting; options &fmterr_setting; options fmtsearch=&fmtsearch_setting; %* ABORT/ABEND only if the job is being run in batch -- it terminates ; %* interactive sessions! ; %* Interactive sessions have DMS Process, batch has Program 'xxx.sas' ; %if not %index(%upcase(&sysprocessname),DMS PROCESS) or %index(%upcase(&sysprocessname),.SAS) or %index(%upcase(&sysprocessname),PROGRAM) %then %do; data _null_; abort abend 2; run; %end; %******************************************************************************; %* Normal exit point. ; %******************************************************************************; %EXIT: %let module=EXIT; %let macname=%scan(&macname,1,_)_&module; %put %unquote(&wab_note) WAETL ending.; %mend waetl;