/*----------------------Web Analytics-----------------------------------------*/ /* WAPATHDP */ /* Create TOP_ENTRY and TOP_REFERRER reports */ /* */ /* */ /*----------------------------------------------------------------------------*/ /* Supported By: Caroline Bahler, CABAHL; Frank Roediger, FRROED */ /* */ /*----------------------------------------------------------------------------*/ /* History: */ /* Date Description Username Change Code */ /* 20041213 Module Created cabahl NA */ /* 20050622 New version of PROC PATH no cabahl C0001 */ /* longer requires PROC DMDB */ /* 20050622 Second PROC PATH call added cabahl C0002 */ /* to use new funnel functionality */ /* 20050705 Removed the creation of cabahl C0003 */ /* summary.pathing to separate macro. Converted to be called */ /* outside %WAETL */ /* 20060718 Converted to handle daily frroed */ /* PATHING_yyyymmdd data sets. */ /*----------------------------------------------------------------------------*/ /* Dependencies: */ /* The following macros need to be available: */ /* %WAWEBMRT */ /* %WADECSUP */ /* */ /* The following macro variables need to be defined and assigned values */ /* in the invoking environment: */ /* none */ /* */ /*----------------------------------------------------------------------------*/ /* Parameters: */ /* SWAMART: the path to the root directory for the designated swamart */ /* TEMP_STORE: optional - path to the directory for the WORKLIB library */ /* WAETL_SW: indicator of whether the macro is invoked by %WAETL */ /* SUPPORT_R1: the minimum count of total sessions for a path to be */ /* stored in the _R1 data set */ /* SUPPORT_R7: the minimum count of total sessions for a path to be */ /* stored in the _R7 data set */ /* SUPPORT_R30: the minimum count of total sessions for a path to be */ /* stored in the _R30 data set */ /* RETCODE: mechanism for passing Return Codes */ /*----------------------------------------------------------------------------*/ /* Input: */ /* summary.pathing_yyyymmdd */ /* */ /*----------------------------------------------------------------------------*/ /* Output: */ /* summary.pathing_r1,7,30 */ /* summary.top_entry_paths_r1,7,30 */ /* summary.top_referrer_paths_r1,7,30 */ /* summary.path_start_list */ /* summary.path_end_list */ /*----------------------------------------------------------------------------*/ /* Copyright (c) 2004 SAS Institute Inc. All Rights Reserved */ /*----------------------------------------------------------------------------*/ %macro wapathdp(swamart= ,temp_store= ,waetl_sw=N ,support_r1= ,support_r7= ,support_r30= ,retcode= ); %let retcode = 0; %***************************************************************************; %* set up WAETL-esque environment -- if WAPATHDP was not invoked by %WAETL ; %***************************************************************************; %if &waetl_sw eq N %then %do; %************************************************************************; %* assign the standard WEBA macro variables ; %************************************************************************; options compress=yes nofmterr; %local wab_rc; %global _wab_rc_; %let _wab_rc_ =0; %let wab_rc =0; %let macname =&sysmacroname; %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.))); %************************************************************************; %* initialize WAB_PATH_RPT_CNT to 1 so that all error reports will be ; %* generated if the initialization fails ; %************************************************************************; %let wab_path_rpt_cnt=1; %************************************************************************; %* compile the standard WEBA macro programs ; %************************************************************************; %wautils; %Current_OS; %if &_wab_rc_ ne 0 %then %do; %put %unquote(&wab_error) CURRENT_OS macro did not run %QCMPRES( successfully). The SEPARATOR macro variable cannot be defined.; %goto ERREXIT; %end; %************************************************************************; %* check whether &SWAMART exists and has the appropriate sub-directories ; %************************************************************************; %if %quote(&swamart) eq %then %do; %put %unquote(&wab_error) The SWAMART argument was %QCMPRES(not provided.) It must be specified.; %goto ERREXIT; %end; %let swamart=%sysfunc(strip(%sysfunc(translate(&swamart," ","'")))); %let swamart=%sysfunc(strip(%sysfunc(translate(&swamart,' ','"')))); %if not %sysfunc(fileexist(&swamart)) %then %do; %put %unquote(&wab_error) The SWAMART argument -- &swamart %QCMPRES( --) is not a valid path.; %goto ERREXIT; %end; %************************************************************************; %* assign the necessary librefs ; %************************************************************************; %let wab_config_dir=&swamart.&separator.config; %if not %sysfunc(fileexist(&wab_config_dir)) %then %do; %put %unquote(&wab_error) The &wab_config_dir location for %QCMPRES( the) CONFIG library does not exist.; %goto ERREXIT; %end; libname config "&wab_config_dir"; %let wab_smy_dir=&swamart.&separator.data&separator.summary; %if not %sysfunc(fileexist(&wab_smy_dir)) %then %do; %put %unquote(&wab_error) The &wab_smy_dir location for the %QCMPRES( SUMMARY) library does not exist.; %goto ERREXIT; %end; libname summary "&wab_smy_dir"; %************************************************************************; %* Assign the libref for TEMP_LIB. ; %************************************************************************; %* TEMP_STORE was not specified: use the default -- /swawork under work. ; %************************************************************************; %if %bquote(&temp_store)eq %str() %then %do; %let temp_store=%sysfunc(pathname(work,L)); %let temp_store=&temp_store.&separator.swawork; %if not %sysfunc(fileexist(&temp_store)) %then %do; %make_directory(dirname=&temp_store ,retcode=wab_rc); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) A new directory could not be %QCMPRES( made) at the default TEMP_STORE location %QCMPRES( --) &temp_store..; %goto ERREXIT; %end; %end; libname worklib "&temp_store"; %let temp_lib=worklib; %end; %************************************************************************; %* TEMP_STORE was specified: make sure that it exists and does not point ; %* to the same directory as the SUMMARY library. ; %************************************************************************; %else %do; %if not %sysfunc(fileexist(&temp_store)) %then %do; %put %unquote(&wab_error) TEMP_STORE -- &temp_store -- %QCMPRES( argument) is a directory that does not exist.; %goto ERREXIT; %end; %if %sysfunc(strip(%sysfunc(lowcase("&temp_store")))) =%sysfunc(strip(%sysfunc(lowcase("&wab_smy_dir")))) %then %do; %put %unquote(&wab_error) TEMP_STORE -- &temp_store -- %QCMPRES( argument) cannot be the same as the permanent %QCMPRES( location) for the SUMMARY library.; %goto ERREXIT; %end; libname worklib "&temp_store"; %let temp_lib=worklib; %end; %************************************************************************; %* Create _WAB_MAX_DATE_ macro variable ; %************************************************************************; proc summary data=summary.pathing_sess_count (keep=date) nway sum; class date; output out=&temp_lib..max_date; run; data _null_; set &temp_lib..max_date end=eof; if eof then call symput('_wab_max_date_',strip(put(date,6.))); run; %************************************************************************; %* Create macro variables with the values of the configuration parms ; %************************************************************************; %Config_Parameters(retcode=wab_rc); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) All configuration parameters could %QCMPRES( not) be converted into macro variables.; %goto ERREXIT; %end; %************************************************************************; %* use the metadata-based assignments if the SUPPORT parameters have not ; %* been provided ; %************************************************************************; %if &support_r1 eq %str() %then %do; %let support_r1=&wab_r1_support; %end; %if &support_r7 eq %str() %then %do; %let support_r7=&wab_r7_support; %end; %if &support_r30 eq %str() %then %do; %let support_r30=&wab_r30_support; %end; %************************************************************************; %* Make sure that the formats that translate Reference --> URL and ; %* Sequenced_Reference --> URL are available ; %************************************************************************; %if %sysfunc(exist(summary.page_frequencies)) eq 0 %then %do; %put %unquote(&wab_error) The SUMMARY.PAGE_FREQUENCIES data %QCMPRES( set) does not exist.; %goto ERREXIT; %end; %locktest(dset=summary.page_frequencies); %if &lock_sw ne 0 %then %do; %put %unquote(&wab_error) There was a failure opening %QCMPRES( the) SUMMARY.PAGE_FREQUENCIES dataset.; %goto ERREXIT; %end; %if %get_observation_count(indsn=summary.page_frequencies)=0 %then %do; %put %unquote(&wab_error) The SUMMARY.PAGE_FREQUENCIES data %QCMPRES( set) contains 0 observations.; %goto ERREXIT; %end; proc format cntlin=summary.page_frequencies; run; %if %sysfunc(exist(summary.cntlin_indtosequrl_)) eq 0 %then %do; %put %unquote(&wab_error) The SUMMARY.CNTLIN_INDTOSEQURL_ %QCMPRES( data) set does not exist.; %goto ERREXIT; %end; %locktest(dset=summary.cntlin_indtosequrl_); %if &lock_sw ne 0 %then %do; %put %unquote(&wab_error) There was a failure opening %QCMPRES( the) SUMMARY.CNTLIN_INDTOSEQURL_ dataset.; %goto ERREXIT; %end; %if %get_observation_count(indsn=summary.cntlin_indtosequrl_)=0 %then %do; %put %unquote(&wab_error) The SUMMARY.CNTLIN_INDTOSEQURL_ %QCMPRES( data) set contains 0 observations.; %goto ERREXIT; %end; proc format cntlin=summary.cntlin_indtosequrl_; run; %************************************************************************; %* Check the availability and accessibility of SUMMARY.PATHING_yyyymmdd ; %************************************************************************; data _null_; set summary.pathing_sess_count end=eof; extr_date_cnt+1; call symput('extr_date_' || strip(put(extr_date_cnt,3.)) ,strip(put(date,yymmddn8.)) ); if eof then call symput('extr_date_cnt',strip(put(extr_date_cnt,3.))); run; %do i=1 %to &extr_date_cnt; %if %sysfunc(exist(summary.pathing_&&extr_date_&i)) eq 0 %then %do; %put %unquote(&wab_error) The SUMMARY.PATHING_&&extr_date_&i %QCMPRES( data) set does not exist.; %goto ERREXIT; %end; %locktest(dset=summary.pathing_&&extr_date_&i); %if &lock_sw ne 0 %then %do; %put %unquote(&wab_error) There was a failure opening %QCMPRES( the) SUMMARY.PATHING_&&extr_date_&i data set.; %goto ERREXIT; %end; %if %get_observation_count(indsn=summary.pathing_&&extr_date_&i)=0 %then %do; %put %unquote(&wab_error) The SUMMARY.PATHING_&&extr_date_&i %QCMPRES( data) set contains 0 observations.; %goto ERREXIT; %end; %end; %* DO I=1 %TO &EXTR_DATE_CNT --------------------------------- ; %end; %* %IF &WAETL_SW EQ N ------------------------------------------- ; %************************************************************************; %* initialization succeeded -- reset WAB_PATH_RPT_CNT to 7 so that no ; %* error reports will be created ; %************************************************************************; %let wab_path_rpt_cnt=7; %***************************************************************************; %* get total number of sessions for R1, R7, and R30 time periods ; %* (used to calculate PERCENT OF VISITS) ; %***************************************************************************; proc sql noprint; select strip(put(sum(sess_count),12.)) into :_r1_total_visits_ from summary.pathing_sess_count where date eq &_wab_max_date_; select strip(put(sum(sess_count),12.)) into :_r7_total_visits_ from summary.pathing_sess_count where date gt &_wab_max_date_-7; select strip(put(sum(sess_count),12.)) into :_r30_total_visits_ from summary.pathing_sess_count where date gt &_wab_max_date_-30; quit; %put %unquote(&wab_note) Total R1 Visits = &_r1_total_visits_ ; %put %unquote(&wab_note) Total R7 Visits = &_r7_total_visits_ ; %put %unquote(&wab_note) Total R30 Visits = &_r30_total_visits_ ; %***************************************************************************; %* create the 6 TOP_ pathing reports: ; %* TOP_ENTRY_PATHS_R1 ; %* TOP_ENTRY_PATHS_R7 ; %* TOP_ENTRY_PATHS_R30 ; %* TOP_REFERRER_PATHS_R1 ; %* TOP_REFERRER_PATHS_R7 ; %* TOP_REFERRER_PATHS_R30 ; %***************************************************************************; %do i=1 %to 6; %if &i eq 1 %then %do; %let days=1; %let hx_source=ENTRY; %let subset_where=date eq &_wab_max_date_; %let summry_where=count ge &support_r1; %let denominator=&_r1_total_visits_; %end; %else %if &i eq 2 %then %do; %let days=7; %let hx_source=ENTRY; %let subset_where=date gt &_wab_max_date_ -7; %let summry_where=count ge &support_r7; %let denominator=&_r7_total_visits_; %end; %else %if &i eq 3 %then %do; %let days=30; %let hx_source=ENTRY; %let subset_where=date gt &_wab_max_date_ -30; %let summry_where=count ge &support_r30; %let denominator=&_r30_total_visits_; %end; %else %if &i eq 4 %then %do; %let days=1; %let hx_source=REFERRER; %let subset_where=date eq &_wab_max_date_; %let summry_where=count ge &support_r1; %let denominator=&_r1_total_visits_; %end; %else %if &i eq 5 %then %do; %let days=7; %let hx_source=REFERRER; %let subset_where=date gt &_wab_max_date_ -7; %let summry_where=count ge &support_r7; %let denominator=&_r7_total_visits_; %end; %else %do; %let days=30; %let hx_source=REFERRER; %let subset_where=date gt &_wab_max_date_ -30; %let summry_where=count ge &support_r30; %let denominator=&_r30_total_visits_; %end; %************************************************************************; %* sum the results of each dates tallies ; %************************************************************************; proc summary data=summary.top_&hx_source._paths_hx (where=(&subset_where)) nway; class rule; id item1-item&wab_itemset_size size; var count; output out=&temp_lib..pathing_y (where=(&summry_where) drop=_type_ _freq_) sum=; run; %************************************************************************; %* determine if only path roots have been extracted ; %************************************************************************; data &temp_lib..non_roots; set &temp_lib..pathing_y; if size gt 1; run; %************************************************************************; %* Determine if any paths found ; %************************************************************************; %if %get_observation_count(indsn=&temp_lib..pathing_y) eq 0 or %get_observation_count(indsn=&temp_lib..non_roots) eq 0 %then %do; %put %unquote(&wab_warning) SUMMARY.TOP_&hx_source._PATHS_R&days %QCMPRES( contains) 0 paths. The R&days support; %put %unquote(&wab_warning) at &&support_r&days is set too high %QCMPRES( and) needs to be set to a lower number.; %put %unquote(&wab_warning) To change the R&days support number %QCMPRES( either) change the value of; %put %unquote(&wab_warning) WAB_R&days._SUPPORT in WACONFIG or %QCMPRES( set) SUPPORT_R&days in the WAPATHDP; %put %unquote(&wab_warning) call to a lower number and rerun %QCMPRES( WAPATHDP) outside of WAETL.; data summary.top_&hx_source._paths_r&days; length item1-item7 $1024 conf count support pc_root size date 8; format date date9.; date=&_wab_max_date_; item1='Information is not available for ' || put(&_wab_max_date_, date9.) || " because R&days Support at &&support_r&days is too high."; run; %end; %* %if %get_observation_count... ----------------------------- ; %else %do; %*********************************************************************; %* Assign the format that will translate the ITEMx fields from their ; %* SEQUENCED_REFERENCE values to their REQUESTED_FILE values ; %*********************************************************************; %* identify the SEQUENCED_REFERENCEs that are in the ITEMx fields ; %*********************************************************************; data &temp_lib..seq_refs; set &temp_lib..pathing_y; length start $ 1037; array items {1:&wab_itemset_size} $ item1-item&wab_itemset_size; do i=1 to &wab_itemset_size while(items{i} gt ' '); start=items{i}; output; end; keep start; run; proc sort data=&temp_lib..seq_refs out =&temp_lib..uniq_seq_refs nodupkeys; by start; run; %*********************************************************************; %* create a subset of SUMMARY.PAGE_FREQUENCIES that contains only the ; %* SEQUENCED_REFERENCEs in the PATHING_Y data set ; %*********************************************************************; data &temp_lib..pathing_format; length start $ 1037; if _n_ eq 1 then do; declare hash h(dataset: "&temp_lib..uniq_seq_refs" ,hashexp: 10 ); h.defineKey('start'); h.defineDone(); end; set summary.cntlin_indtosequrl_; rc=h.find(); if rc eq 0 then output; drop rc; run; %*********************************************************************; %* build the format from the subset data set ; %*********************************************************************; proc format cntlin=&temp_lib..pathing_format; run; %*********************************************************************; %* create the SUMMARY pathing report ; %*********************************************************************; proc sort data=&temp_lib..pathing_y; by item1-item&wab_itemset_size; run; data summary.top_&hx_source._paths_r&days; length item_new $ 1037 item_new_1 $ 1037 item1-item&wab_itemset_size $ 1037 ; %wametatr(srce_dsn=paths ,retcode=wab_rc ); set &temp_lib..pathing_y; by item1-item&wab_itemset_size; label %do j=1 %to &wab_itemset_size; item&j="Item &j" %end; ; retain count1-count&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; %******************************************************************; %* exclude paths that are only a root node (there is only one row ; %* for ITEM1) ; %******************************************************************; if first.item1 and last.item1 then do; delete; return; end; do i=1 to &wab_itemset_size while(item{i} gt ' '); item_new=put(strip(item{i}),$idx2srl.); if i=1 then item_new_1=item_new; %***************************************************************; %* remove the 2-digit '_xx' suffix from the formatted ITEMx ; %***************************************************************; npos=prxmatch('/_\d{1,2}$/',strip(item_new))-1; if npos ge 1 then do; item_new=substr(strip(item_new) ,1 ,npos); item{i}=item_new; end; else do; item{i}=item_new; end; end; %if &hx_source eq ENTRY %then %do; %***************************************************************; %* subsetting IF to limit ENTRY output to paths starting with ; %* sessions 1st request -- the ITEM1 name has a '_1' suffix ; %***************************************************************; if prxmatch('/_1$/',strip(item_new_1)); %end; %else %do; %***************************************************************; %* subsetting IF to limit REFERRER output to paths starting with; %* sessions 0th request -- the ITEM1 name has a '_0' suffix ; %***************************************************************; if prxmatch('/_0$/',strip(item_new_1)); %end; top_path=0; if first.item1 then do; top_path=1; count1=count; do i=2 to &wab_itemset_size; counts{i}=.; end; end; else if first.item2 then do; count2=count; do i=3 to &wab_itemset_size; counts{i}=.; end; end; else if first.item3 then do; count3=count; do i=4 to &wab_itemset_size; counts{i}=.; end; end; else if first.item4 then do; count4=count; do i=5 to &wab_itemset_size; counts{i}=.; end; end; else if first.item5 then do; count5=count; do i=6 to &wab_itemset_size; counts{i}=.; end; end; else if first.item6 then do; count6=count; count7=.; end; else if first.item7 then count7=count; pc_visits=round(100*(count/&denominator),.01); if size eq 1 then do; pc_root=.; pc_prev=.; conf=.; end; else do; pc_root=round(100*(count/count1),.01); pc_prev=round(100*(count/counts{size-1}),.01); conf=100-pc_prev; end; support=pc_visits; date=&_wab_max_date_; format date date9.; keep date item1-item&wab_itemset_size count support conf top_path pc_root size ; run; proc sort data=summary.top_&hx_source._paths_r&days; by item1-item7; run; %end; %* %ELSE %DO ------------------------------------------------- ; %end; %* %DO I=1 %TO 6 ------------------------------------------------ ; %goto EXIT; %ERREXIT:; %put %unquote(&wab_error) Failure in WAPathDP.; %**************************************************************************; %* generate place-holding error reports for all the PROC Path analyses ; %* that could not be created. ; %**************************************************************************; %do i=&wab_path_rpt_cnt %to 6; %if &i eq 1 %then %do; %let path_rpt=top_entry_paths_r1; %end; %else %if &i eq 2 %then %do; %let path_rpt=top_entry_paths_r7; %end; %else %if &i eq 3 %then %do; %let path_rpt=top_entry_paths_r30; %end; %else %if &i eq 4 %then %do; %let path_rpt=top_referrer_paths_r1; %end; %else %if &i eq 5 %then %do; %let path_rpt=top_referrer_paths_r7; %end; %else %if &i eq 6 %then %do; %let path_rpt=top_referrer_paths_r30; %end; data summary.&path_rpt; length item1-item7 $1024 conf count support pc_root size date 8; format date date9.; date=&_wab_max_date_; item1='Information is not available for ' || put(&_wab_max_date_, date9.) || ' because of Path failure during WAETL.'; run; %end; %let retcode=1; %EXIT:; %put %unquote(&wab_note) WAPathDP ending.; %mend wapathdp;