/*----------------------Web Analytics-----------------------------------------*/ /* WASUMTST */ /* Run Summaries outside WAETL (their ETL_RUN_SW in WAADMSUM is set to 0) */ /* */ /*----------------------------------------------------------------------------*/ /* Supported By: Frank Roediger, FRROED */ /* */ /*----------------------------------------------------------------------------*/ /* History: */ /* Date Description Username Change Code */ /* 20050606 Program Created frroed NA */ /* */ /*----------------------------------------------------------------------------*/ /* Dependencies: */ /* The following macros need to be available: */ /* %WAADMSUM */ /* %WASUMENG */ /* %WAUTILS */ /* */ /* 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 */ /* RUN_TYPE: TEST or PROD to indicate whether the summarizations should */ /* be copied to SUMMARY (for TEST), or appended to the SUMMARY */ /* counterparts and then re-summarized (for PROD). */ /* DATE_RANGE: ALL, LAST, or ETL to indicate whether to use ALL available */ /* dates, just the LAST available date, or the dates already */ /* in WORKLIB from a just-completed execution of %WAETL */ /* (default is LAST) */ /* DATASRCE: DETAIL, SESSION, or BOTH, to indicate whether to run the */ /* non-ETL summaries that are derived from DETAIL, SESSION, or */ /* BOTH detail and session data sets (default is BOTH) */ /* SMMYSRCE: optional - the LIBNAME.MEMNAME for the data set with the */ /* Summary metadata. by default, it is CONFIG.WAADMSUM. */ /* TEMP_STORE: optional - path to the directory for the WORKLIB library */ /* SUMMARIZATION_TO_RUN: optional - the LABEL from the WAADMSUM metadata */ /* that identifies the specific family of non-ETL */ /* summarizations that you want to create. to specify more */ /* than one summarization, use a comma-demilited list within */ /* the %bquote function. other valid arguments are blank and */ /* _ALL_, both of which indicate that all non-ETL */ /* summarizations are to be created. */ /* */ /*----------------------------------------------------------------------------*/ /* Input: */ /* The arguments for this macro provide a WAETL-like environment */ /* (swamart= */ /* ,run_type= */ /* ,date_range= */ /* ,datasrce= */ /* ,smmysrce= */ /* ,temp_store= */ /* ,summarization_to_run */ /* ); */ /* */ /*----------------------------------------------------------------------------*/ /* Output: */ /* */ /*----------------------------------------------------------------------------*/ /* Copyright (c) 2005 SAS Institute Inc. All Rights Reserved */ /*----------------------------------------------------------------------------*/ %macro wasumtst(swamart= ,run_type= ,date_range=last ,datasrce=both ,smmysrce=config.waadmsum ,temp_store= ,summarization_to_run= ); %******************************************************************************; %* assign the standard WEBA macro variables ; %******************************************************************************; options compress=yes nofmterr; %local wab_rc wab_summary_count; %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.))); %let wab_summary_count=0; %******************************************************************************; %* compile the standard WEBA macro programs ; %******************************************************************************; dm 'af catalog=SASHELP.WAGUI.WAINI.SCL'; %wautils; %Current_OS; %if &_wab_rc_ ne 0 %then %do; %put %unquote(&wab_error) CURRENT_OS macro did not run %QCMPRES( successfully). The SEPARATOR macro variable cannot be defined.; %goto ERREXIT; %end; %******************************************************************************; %* validate the arguments/values that have been provided for the parameters. ; %******************************************************************************; %* SWAMART ; %******************************************************************************; %if %bquote(&swamart) eq %str() %then %do; %put %unquote(&wab_error) The SWAMART argument was not provided. %QCMPRES( It) must be specified.; %goto ERREXIT; %end; %******************************************************************************; %* RUN_TYPE ; %******************************************************************************; %if %bquote(&run_type) eq %str() %then %do; %put %unquote(&wab_error) The RUN_TYPE argument was not provided. %QCMPRES( It) must be PROD or TEST.; %goto ERREXIT; %end; %if %upcase(&run_type) ne PROD and %upcase(&run_type) ne TEST %then %do; %put %unquote(&wab_error) The RUN_TYPE value -- &run_type -- %QCMPRES( is) invalid. It must be PROD or TEST.; %goto ERREXIT; %end; %******************************************************************************; %* DATE_RANGE ; %******************************************************************************; %if %bquote(&date_range) eq %str() %then %do; %put %unquote(&wab_error) The DATE_RANGE argument cannot be blank. %QCMPRES( If) you want to use the default, omit the DATE_RANGE parameter %QCMPRES( when) you invoke the WASUMTST macro.; %goto ERREXIT; %end; %if %upcase(&date_range) ne ALL and %upcase(&date_range) ne LAST and %upcase(&date_range) ne ETL %then %do; %put %unquote(&wab_error) The DATE_RANGE value -- &date_range -- %QCMPRES( is) invalid. It must be ALL, LAST, or ETL.; %goto ERREXIT; %end; %******************************************************************************; %* DATASRCE ; %******************************************************************************; %if %bquote(&datasrce) eq %str() %then %do; %put %unquote(&wab_error) The DATASRCE argument cannot be blank. %QCMPRES( If) you want to use the default, omit the DATASRCE parameter %QCMPRES( when) you invoke the WASUMTST macro.; %goto ERREXIT; %end; %if %upcase(&datasrce) ne DETAIL and %upcase(&datasrce) ne SESSION and %upcase(&datasrce) ne BOTH %then %do; %put %unquote(&wab_error) The DATASRCE value -- &datasrce -- %QCMPRES( is) invalid. It must be DETAIL, SESSION, or BOTH.; %goto ERREXIT; %end; %******************************************************************************; %* SMMYSRCE ; %******************************************************************************; %if %bquote(&smmysrce) eq %str() %then %do; %put %unquote(&wab_error) The SMMYSRCE argument cannot be blank. %QCMPRES( If) you want to use the default, omit the SMMYSRCE parameter %QCMPRES( when) you invoke the WASUMTST macro.; %goto ERREXIT; %end; %******************************************************************************; %* TEMP_STORE -- does not require validation ; %******************************************************************************; %* SUMMARIZATION_TO_RUN -- does not require validation ; %******************************************************************************; %******************************************************************************; %* check whether &SWAMART exists and has the appropriate sub-directories ; %******************************************************************************; %let swamart=%sysfunc(strip(%sysfunc(translate(&swamart," ","'")))); %let swamart=%sysfunc(strip(%sysfunc(translate(&swamart,' ','"')))); %if not %sysfunc(fileexist(&swamart)) %then %do; %put %unquote(&wab_error) The SWAMART argument -- &swamart -- is %QCMPRES( not) a valid path.; %goto 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 the %QCMPRES( 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"; %let wab_dated_dir=&swamart.&separator.data&separator.dated; %if not %sysfunc(fileexist(&wab_dated_dir)) %then %do; %put %unquote(&wab_error) The &wab_dated_dir location for the %QCMPRES( DATED) library does not exist.; %goto ERREXIT; %end; libname dated "&wab_dated_dir"; %let wab_detl_dir=&swamart.&separator.data&separator.detail; %if not %sysfunc(fileexist(&wab_detl_dir)) %then %do; %put %unquote(&wab_error) The &wab_detl_dir location for the %QCMPRES( WADETAIL) library does not exist.; %goto ERREXIT; %end; libname wadetail "&wab_detl_dir"; %******************************************************************************; %* Assign the libref for TEMP_LIB -- if DATE_RANGE ne ETL ; %* NOTE: DATE_RANGE eq ETL requires that WORKLIB already exist from an ; %* execution of the %WAETL macro. ; %******************************************************************************; %if %upcase(&date_range) eq ETL %then %do; %if %sysfunc(libref(worklib)) ne 0 %then %do; %put %unquote(&wab_error) You can only specify &date_range for %QCMPRES( the) DATE_RANGE parameter if the WORKLIB libref still %QCMPRES( exists) from an earlier WAETL run.; %goto ERREXIT; %end; %let temp_store=%sysfunc(pathname(worklib,L)); %let temp_lib=worklib; %end; %else %do; %***************************************************************************; %* 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 -- &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 location %QCMPRES( for) the SUMMARY library.; %goto ERREXIT; %end; libname worklib "&temp_store"; %let temp_lib=worklib; %end; %end; %******************************************************************************; %* save all the &TEMP_LIB..SESSION_yyyymmdd and DETAIL_yyyymmdd data sets to ; %* WORK ; %******************************************************************************; %if %upcase(&date_range) eq ETL %then %do; proc sql; create table &temp_lib..detail_dsets as select memname, input(scan(memname,2,"_"),YYMMDD10.) as date from dictionary.tables where upcase(libname) eq upcase("&TEMP_LIB") and prxmatch('/^DETAIL_\d{8}$/', upcase(strip(memname))) order by date descending; create table &temp_lib..session_dsets as select memname, input(scan(memname,2,"_"),YYMMDD10.) as date from dictionary.tables where upcase(libname) eq upcase("&TEMP_LIB") and prxmatch('/^SESSION_\d{8}$/', upcase(strip(memname))) order by date descending; quit; data _null_; set &temp_lib..detail_dsets end=eof; call symput('_wab_temp_detail_' || strip(put(_n_,3.)) ,strip(memname) ); if eof then call symput('_wab_temp_detail_cnt_',strip(put(_n_,3.))); run; data _null_; set &temp_lib..session_dsets end=eof; call symput('_wab_temp_session_' || strip(put(_n_,3.)) ,strip(memname) ); if eof then call symput('_wab_temp_session_cnt_',strip(put(_n_,3.))); run; proc copy in=&temp_lib out=work; select %do i=1 %to &_wab_temp_detail_cnt_; &&_wab_temp_detail_&i %end; %do j=1 %to &_wab_temp_session_cnt_; &&_wab_temp_session_&j %end; ; 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 location %QCMPRES( --) &temp_store -- could not be deleted. Please check permissions.; %goto ERREXIT; %end; %******************************************************************************; %* restore the WORK.SESSION_yyyymmdd and DETAIL_yyyymmdd data sets to ; %* &TEMP_LIB ; %******************************************************************************; %if %upcase(&date_range) eq ETL %then %do; proc copy in=work out=&temp_lib move; select %do i=1 %to &_wab_temp_detail_cnt_; &&_wab_temp_detail_&i %end; %do j=1 %to &_wab_temp_session_cnt_; &&_wab_temp_session_&j %end; ; quit; %end; %*****************************************************************************; %* Create macro variables with the values of the configuration parameters ; %*****************************************************************************; %Config_Parameters(retcode=wab_rc); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) All configuration parameters could not %QCMPRES( be) converted into macro variables.; %goto ERREXIT; %end; %******************************************************************************; %* perform the standard tests on the SMMYSRCE parameter ; %******************************************************************************; %if %sysfunc(exist(&smmysrce,data))=0 %then %do; %put %unquote(&wab_error) &smmysrce, the data set that you %QCMPRES( designated) as SMMYSRCE, does not exist.; %goto ERREXIT; %end; %locktest(dset=&smmysrce); %if &lock_sw ne 0 %then %do; %put %unquote(&wab_error) There was a failure opening SMMYSRCE %QCMPRES( --) &smmysrce -- the non-ETL Summarization metadata data set.; %goto ERREXIT; %end; %if %get_observation_count(indsn=&smmysrce) eq 0 %then %do; %put %unquote(&wab_error) &smmysrce, the data set that you %QCMPRES( designated) as SMMYSRCE, contains 0 observations.; %goto ERREXIT; %end; %***************************************************************************; %* Configuration data Validation: ; %* Does metadata dataset have all the standard variables (& attribs)? ; %***************************************************************************; %watstdsn(test_dsn=&smmysrce ,tmpl_dsn=waadmsum ,retcode=wab_rc ); %if &wab_rc=1 %then %do; %* %WATSTDSN failed ; %put %unquote(&wab_error) &smmysrce, the data set that you %QCMPRES( designated) as SMMYSRCE, is invalid.; %goto ERREXIT; %end; %******************************************************************************; %* validate the DATASRCE parameter ; %******************************************************************************; %if %upcase(&datasrce) eq DETAIL %then %do; %let extr_where=(where=(upcase(source_table) eq 'DETAIL')); %end; %else %if %upcase(&datasrce) eq SESSION %then %do; %let extr_where=(where=(upcase(source_table) eq 'SESSION')); %end; %else %if %upcase(&datasrce) eq BOTH %then %do; %let extr_where=; %end; %******************************************************************************; %* make sure that there are non-WAETL Summaries in the Summary metadata source ; %******************************************************************************; data &temp_lib..waadmsum_extr; set &smmysrce &extr_where; if etl_run_sw eq 0; run; %if %get_observation_count(indsn=&temp_lib..waadmsum_extr) eq 0 %then %do; %put %unquote(&wab_error) The extract of the Summary metadata -- %QCMPRES( &smmysrce) -- does not have any non-ETL summaries.; %if %bquote(&extr_where) ne %str() %then %do; %put %unquote(&wab_error) The restriction that you have imposed %QCMPRES( with) the DATASRCE parameter -- &datasrce -- may be responsible.; %end; %goto ERREXIT; %end; %******************************************************************************; %* validate the SUMMARIZATION_TO_RUN parameter ; %******************************************************************************; %* Use the argument for the SUMMARIZATION_TO_RUN parameter to control the ; %* selection of which non-ETL summarizations to run. ; %******************************************************************************; %if %quote(&summarization_to_run) eq %str() or %quote(%upcase(&summarization_to_run)) eq _ALL_ %then %do; %let nsummarizations=%get_observation_count(indsn=&temp_lib..waadmsum_extr); %put %unquote(&wab_note) SUMMARIZATION_TO_RUN parameter value %QCMPRES(will select) all &nsummarizations non-ETL summarizations.; %if &nsummarizations eq 0 %then %do; %put %unquote(&wab_error) No summarization will be processed.; %let retcode=1; %goto ERREXIT; %end; %end; %else %do; %***************************************************************************; %* id each of the requested summarizations in SUMMARIZATION_TO_RUN ; %***************************************************************************; data &temp_lib..def_list; length defs $ 5000; length label $ 256; defs="&summarization_to_run"; do while(defs gt ' '); label=scan(defs,1,','); output; defs=strip(tranwrd(defs,strip(label),' ')); if substr(defs,1,1) eq ',' then defs=strip(substr(defs,2)); end; keep label; run; %***************************************************************************; %* m/m DEF_LIST with WAADMSUM_EXTR to establish the metadata that is for ; %* only the requested summarizations ; %***************************************************************************; proc sort data=&temp_lib..def_list; by label; run; proc sort data=&temp_lib..waadmsum_extr; by label; run; data &temp_lib..waadmsum_extr only_a only_b; merge &temp_lib..waadmsum_extr (in=in_a) &temp_lib..def_list (in=in_b); by label; if in_a then if in_b then output &temp_lib..waadmsum_extr; else output only_a; else output only_b; run; %let nsummarizations=%get_observation_count(indsn=&temp_lib..waadmsum_extr); %put %unquote(&wab_note) SUMMARIZATION_TO_RUN parameter value %QCMPRES(will limit) processing to &nsummarizations selected summarizations.; %if %get_observation_count(indsn=only_b) ne 0 %then %do; %put %unquote(&wab_error) There are Segmentation labels in the %QCMPRES( SUMMARIZATION_TO_RUN) argument -- &summarization_to_run -- %QCMPRES( that) do not have any metadata in &smmysrce..; %let retcode=1; %goto ERREXIT; %end; %if &nsummarizations eq 0 %then %do; %put %unquote(&wab_error) No summarization will be processed.; %let retcode=1; %goto ERREXIT; %end; %end; %******************************************************************************; %* make sure that the source data sets -- either Session or Detail -- exist ; %* for requested Summaries ; %******************************************************************************; %* determine whether Session or Detail (or both) are source data sets for the ; %* non-ETL summaries ; %******************************************************************************; proc sort data=&temp_lib..waadmsum_extr (keep=source_table) out =&temp_lib..srce_dsets nodupkeys; by source_table; run; data _null_; set &temp_lib..srce_dsets end=eof; length srce_dsets $ 50; retain srce_dsets; srce_dsets=strip(srce_dsets) || ' ' || strip(upcase(source_table)); if eof then call symput('wab_srce_dsets',strip(srce_dsets)); run; %******************************************************************************; %* inventory the Session data sets (in the DATED library) and the Detail data ; %* sets (in the WADETAIL library) ; %******************************************************************************; proc sql; 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(strip(memname))) order by date descending; 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(strip(memname))) order by date descending; quit; %******************************************************************************; %* based on the &DATE_RANGE argument, copy the appropriate SESSION and/or ; %* DETAIL data sets to TEMP_LIB ; %* ; %* NOTE: in order for the Summary Engine to function, the Session and Detail ; %* data sets that it uses need to be in TEMP_LIB and need to have the ; %* same range of dates. This means that for non-ETL summaries, the ; %* dates whose data are summarized might be different than the dates the ; %* users thought they were requesting when they specified ALL or LAST ; %* for the DATE_RANGE parameter. For example, if the DATED library has ; %* SESSION_yyyymmdd data sets for 01APR2005 thru 20APR2005 but the ; %* WADETAIL library has DETAIL_yyyymmdd data sets for 14APR2005 thru ; %* 20APR2005, the WASUMTST macro will only use the dates that they have ; %* in common (14APR2005 thru 20APR2005) even though there are more dates ; %* available in the DATED library. ; %* This type of date range selection does not occur if the non-ETL ; %* summaries all have a single type of source data -- Session or Detail. ; %******************************************************************************; %* DATE_RANGE eq ALL -- use all available DATED and WADETAIL data sets ; %******************************************************************************; %if %upcase(&date_range) eq ALL %then %do; %if %index(&wab_srce_dsets,DETAIL) %then %do; proc sort data=&temp_lib..detail_library_contents; by date; run; %if %index(&wab_srce_dsets,SESSION) %then %do; proc sort data=&temp_lib..dated_library_contents; by date; run; data &temp_lib..both &temp_lib..only_a &temp_lib..only_b; merge &temp_lib..dated_library_contents (in=in_a keep=date) &temp_lib..detail_library_contents (in=in_b keep=date); by date; if in_a then if in_b then output &temp_lib..both; else output &temp_lib..only_a; else output &temp_lib..only_b; run; %if %get_observation_count(indsn=&temp_lib..both) eq 0 %then %do; %put %unquote(&wab_error) The DATED and WADETAIL libraries %QCMPRES( do) not have any common dates. The current set of %QCMPRES( non ETL) summaries cannot be run. Please re submit %QCMPRES( WASUMTST) with the DATASRCE parameter set to %QCMPRES(DETAIL or) SESSION.; %goto ERREXIT; %end; %else %do; %let etl_date_list=&temp_lib..both; %if %get_observation_count(indsn=&temp_lib..only_a) ne 0 or %get_observation_count(indsn=&temp_lib..only_b) ne 0 %then %do; %put %unquote(&wab_note) The DATED and WADETAIL %QCMPRES( libraries) do not have ALL the same dates. ALL %QCMPRES( their) common dates will be used instead.; %end; %end; %end; %else %do; %let etl_date_list=&temp_lib..detail_library_contents; %end; %end; %else %do; %let etl_date_list=&temp_lib..dated_library_contents; %end; %end; %******************************************************************************; %* DATE_RANGE eq LAST -- use latest available DATED and WADETAIL data sets ; %******************************************************************************; %else %if %upcase(&date_range) eq LAST %then %do; %if %index(&wab_srce_dsets,DETAIL) %then %do; %if %index(&wab_srce_dsets,SESSION) %then %do; data &temp_lib..both &temp_lib..only_a &temp_lib..only_b; merge &temp_lib..dated_library_contents (in=in_a keep=date) &temp_lib..detail_library_contents (in=in_b keep=date); by descending date; if in_a then if in_b then output &temp_lib..both; else output &temp_lib..only_a; else output &temp_lib..only_b; run; %if %get_observation_count(indsn=&temp_lib..both) eq 0 %then %do; %put %unquote(&wab_error) The DATED and WADETAIL libraries %QCMPRES( do) not have any common dates. The current set of %QCMPRES( non ETL) summaries cannot be run. Please re submit %QCMPRES( WASUMTST) with the DATASRCE parameter set to %QCMPRES(DETAIL or) SESSION.; %goto ERREXIT; %end; %else %do; data &temp_lib..both_last_date; set &temp_lib..both; output; stop; run; %let etl_date_list=&temp_lib..both_last_date; %if %get_observation_count(indsn=&temp_lib..only_a) ne 0 or %get_observation_count(indsn=&temp_lib..only_b) ne 0 %then %do; %put %unquote(&wab_note) The DATED and WADETAIL %QCMPRES( libraries) do not have the same LAST date. Their %QCMPRES( latest) common date will be used instead.; %end; %end; %end; %else %do; data &temp_lib..detail_last_date; set &temp_lib..detail_library_contents; output; stop; run; %let etl_date_list=&temp_lib..detail_last_date; %end; %end; %else %do; data &temp_lib..dated_last_date; set &temp_lib..dated_library_contents; output; stop; run; %let etl_date_list=&temp_lib..dated_last_date; %end; %end; %******************************************************************************; %* DATE_RANGE eq ETL -- use the SESSION_yyyymmdd and DETAIL_yyyymmdd data sets ; %* that are already in WORKLIB ; %******************************************************************************; %else %if %upcase(&date_range) eq ETL %then %do; proc sql; create table &temp_lib..detail_library_contents as select memname, input(scan(memname,2,"_"),YYMMDD10.) as date from dictionary.tables where upcase(libname) eq upcase("&TEMP_LIB") and prxmatch('/^DETAIL_\d{8}$/', upcase(strip(memname))) order by date descending; create table &temp_lib..dated_library_contents as select memname, input(scan(memname,2,"_"),YYMMDD10.) as date from dictionary.tables where upcase(libname) eq upcase("&TEMP_LIB") and prxmatch('/^SESSION_\d{8}$/', upcase(strip(memname))) order by date descending; quit; %if %index(&wab_srce_dsets,DETAIL) %then %do; %if %index(&wab_srce_dsets,SESSION) %then %do; data &temp_lib..both &temp_lib..only_a &temp_lib..only_b; merge &temp_lib..dated_library_contents (in=in_a keep=date) &temp_lib..detail_library_contents (in=in_b keep=date); by descending date; if in_a then if in_b then output &temp_lib..both; else output &temp_lib..only_a; else output &temp_lib..only_b; run; %if %get_observation_count(indsn=&temp_lib..both) eq 0 %then %do; %put %unquote(&wab_error) The WORKLIB library does %QCMPRES( not) have any common dates for DETAIL_yyyymmdd and %QCMPRES( SESSION_yyyymmdd) data sets. The current set of %QCMPRES( non ETL) summaries cannot be run. Please re submit %QCMPRES( WASUMTST) with the DATASRCE parameter set to %QCMPRES(DETAIL or) SESSION.; %goto ERREXIT; %end; %else %do; %let etl_date_list=&temp_lib..both; %if %get_observation_count(indsn=&temp_lib..only_a) ne 0 or %get_observation_count(indsn=&temp_lib..only_b) ne 0 %then %do; %put %unquote(&wab_note) The DETAIL_yyyymmdd and %QCMPRES( SESSION_yyyymmdd) data sets in the WORKLIB library %QCMPRES( are) not for the same set of dates. Only their %QCMPRES( common) dates will be used instead.; %end; %end; %end; %else %do; %let etl_date_list=&temp_lib..detail_library_contents; %end; %end; %else %do; %let etl_date_list=&temp_lib..dated_library_contents; %end; %end; %******************************************************************************; %* create macro variables for all the available user-requested dates ; %******************************************************************************; data _null_; set &etl_date_list end=eof; call symput('_wab_date_' || strip(put(_n_,6.)) ,trim(left(put(date,yymmddn8.)))); if eof then call symput('_wab_num_etl_dates_',strip(put(_n_,6.))); run; %******************************************************************************; %* copy DETAIL_yyyymmdd and SESSION_yyyymmdd data sets to &TEMP_LIB (not ; %* necessary when using the data sets that are already in &TEMP_LIB from ; %* a just-finished execution of %WAETL) ; %******************************************************************************; %if %upcase(&date_range) ne ETL %then %do; %***************************************************************************; %* copy the DETAIL_yyyymmdd data sets from WADETAIL to TEMP_LIB for all the ; %* user-requested dates (if there are DETAIL-based non-ETL summaries) ; %***************************************************************************; %if %index(&wab_srce_dsets,DETAIL) %then %do; proc copy out=&temp_lib in=wadetail; select %do i=1 %to &_wab_num_etl_dates_; detail_&&_wab_date_&i %end;; quit; %end; %***************************************************************************; %* copy the SESSION_yyyymmdd data sets from DATED to TEMP_LIB for all the ; %* user-requested dates (if there are SESSION-based non-ETL summaries) ; %***************************************************************************; %if %index(&wab_srce_dsets,SESSION) %then %do; proc copy out=&temp_lib in=dated; select %do i=1 %to &_wab_num_etl_dates_; session_&&_wab_date_&i %end;; quit; %end; %end; %******************************************************************************; %* Convert the non-ETL Summary metadata into the WASUMENG format ; %******************************************************************************; %waadmsum(meta_in=&temp_lib..waadmsum_extr ,meta_out=&wab_metadsn ,retcode=wab_rc ); %******************************************************************************; %* Use the Summary Engine to create PROC Summary steps ; %******************************************************************************; %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 PROC Summary step 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; %******************************************************************************; %* Process the summarization results. ; %******************************************************************************; %* RUN_TYPE=TEST ; %* Copy each summarized &TEMP_LIB data set to the SUMMARY library. Overwrite ; %* the corresponding entries in the SUMMARY library, if they exist. Make ; %* sure that the _TYPE_ variable is dropped from the output. ; %******************************************************************************; %if %upcase(&run_type) eq TEST %then %do; data _null_; set &temp_lib.._wab_meta_data_ end=eof; call symput('smmydset_' || strip(put(_n_,6.)) ,strip(scan(output_table,2,'.')) ); if eof then call symput('smmydset_cnt',strip(put(_n_,6.))); run; %do i=1 %to &smmydset_cnt; data summary.&&smmydset_&i; set &temp_lib..&&smmydset_&i; drop _type_; run; %end; %end; %******************************************************************************; %* RUN_TYPE=PROD ; %* Append each summarized &TEMP_LIB data set to its counterpart in the SUMMARY ; %* library, then re-summarize and write results to SUMMARY. ; %******************************************************************************; %else %do; %***************************************************************************; %* 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 ; %* partitioned current ETL. These dates 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 ; %* correspondingdata 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; %end; %******************************************************************************; %* For production runs: ; %* Update the WEBMART_STATUS table ; %******************************************************************************; %if %upcase(&run_type) eq PROD %then %do; %wastatus(swamart=&swamart ,waetl_sw=Y ,webmart=1 ,decsupp=0 ,retcode=wab_rc ); %end; %goto EXIT; %ERREXIT:; %put %unquote(&wab_error) WASUMTST exiting due to error.; %EXIT:; dm 'af catalog=SASHELP.WAGUI.WATRM.SCL'; %mend wasumtst;