%******************************************************************************; %* Copyright (C) 2004 by SAS Institute Inc., Cary, NC 27512-8000 ; %* ; %* Name: Wautils.sas ; %* ; %* Purpose: Bundle the Web Analytics Utility macros: ; %* ; %* No_Notes ; %* Maybe_Notes ; %* makenulldata ; %* wametatr ; %* watstdsn ; %* Current_OS ; %* make_directory ; %* remove_directory ; %* get_observation_count ; %* locktest ; %* wametdef ; %* wasasdir ; %* walists ; %* cvt_fmts ; %* Create_Session ; %* Create_Detail ; %* Config_Parameters ; %* Create_Funnel ; %* Remove_Formats ; %* ; %* Parameters: ; %* ; %* ; %******************************************************************************; %macro wautils; %******************************************************************************; %* Name: No_Notes ; %* Purpose: Turn off options notes based on wbdebug flag ; %* Usage: %No_Notes ; %******************************************************************************; %macro No_Notes; options nonotes; %mend No_Notes; %******************************************************************************; %* Name: Maybe_Notes ; %* Purpose: Turn on notes only if mode is not Quiet ; %* Usage: %Maybe_Notes ; %******************************************************************************; %macro Maybe_Notes; options notes; %mend Maybe_Notes; /*-----------------------------------------------------------------------------*/ /* MAKENULLDATA */ /* */ /* HISTORY: 09/01/04 sassyw Initial coding */ /* */ /*-----------------------------------------------------------------------------*/ /* DESCRIPTION: This macro is used to generate a template data set. */ /* The data set will have no records only the header info. */ /* This macro was written to centralize the attributes of */ /* the data sets which it generates. */ /* */ /* PARAMETERS: DSTYPE The type of data set to generate. Also could */ /* be stated what process needs the null data */ /* set generated. Valid values are SCORECARD and */ /* DASHBOARD */ /* DSLIB The library to create the data set in */ /* DSNAME The name to use for the dataset */ /* REPLACE Flag indicating if an existing version of */ /* the data set should be replaced or not */ /* (Default=YES) */ /*-----------------------------------------------------------------------------*/ %macro makenulldata(dstype= ,dslib= ,dsname= ,replace=YES); /* normalize all parameters to upper case */ %let dstype=%upcase(&dstype); %let dslib=%upcase(&dslib); %let dsname=%upcase(&dsname); %let replace=%upcase(&replace); %let valid_types=SCORECARD DASHBOARD SEGMENTATION; %let valid_replace=YES NO; /* verify that the libref is defined */ %if (%sysfunc(libref(&dslib))) %then %do; %PUT %UNQUOTE(&wab_error) Library &dslib specified in DSLIB parameter is not defined; %LET retcode=1; %GOTO ERREXIT; %end; /* verify that the dstype is one of SCORECARD DASHBOARD SEGMENTATION */ %if (%sysfunc(index(&valid_types,&dstype)))=0 %then %do; %PUT %UNQUOTE(&wab_error) Type &dstype specified in DSTYPE parameter is not valid; %PUT %UNQUOTE(&wab_error) Valid values are SCORECARD, DASHBOARD or SEGMENTATION; %LET retcode=1; %GOTO ERREXIT; %end; /* verify that the replace parameter value is valid */ %if (%sysfunc(index(&valid_replace,&replace)))=0 %then %do; %PUT %UNQUOTE(&wab_error) Valid values for the REPLACE parameter are &valid_replace; %LET retcode=1; %GOTO ERREXIT; %end; /* build the full libname.dsname name of the data set to be created */ %let fullname=&dslib..&dsname; /* delete if the dataset exist and the replace parameter value is YES */ %IF %SYSFUNC(EXIST(&fullname,DATA)) and (&replace=NO) %THEN %DO; %PUT %UNQUOTE(&wab_error) The data set &fullname already exist; %PUT %UNQUOTE(&wab_error) The REPLACE parameter is set to &replace; %LET retcode=1; %GOTO ERREXIT; %END; %ELSE %DO; %IF %SYSFUNC(EXIST(&fullname,DATA))%THEN %DO; proc datasets lib=&dslib nolist; delete &dsname; run; %END; %END; %IF &dstype=SCORECARD %THEN %DO; /* ready to build scorecard table with null record */ data &fullname; attrib scorecard length =$15 report_date length =8 format =DATE7. _LABEL_ length =$40 format =$100. informat=$100. label='LABEL OF FORMER VARIABLE' _0 length =8 _5 length =8 _10 length =8 _15 length =8 _20 length =8 _25 length =8 _30 length =8 _35 length =8 _40 length =8 _45 length =8 _50 length =8 DATE length =8 format =DATE9. informat=YYMMDD10. label='Date' ACTUAL length =8 label='Actual Values' PREDICT length =8 label='Predicted Values' LOWER length =8 label='Lower Confidence Limits' UPPER length =8 label='Upper Confidence Limits' ERROR length =8 label='Prediction Errors' STD length =8 label='Prediction Standard Errors' business_dir length =$8 format =$8. informat=$8. performance length =8 business_goal length =8 _WEIGHT_ length =8 format =nlnum12.2 importance length =8 _name_ length =$25; run; %END; %IF &dstype=DASHBOARD %THEN %DO; /* ready to build dashboard table with null record */ data &fullname; length slope 8 slope_stderr 8 intercept 8 std_trend 8 var_name $64 report_date 8 dashboard $25 label $64 actual 8 min 8 max 8 mean 8 std 8 category $45 business_dir $5 pvalue 8 performance $15 ; format report_date date9. std_trend nlnum15.2 ; run; %END; %IF &dstype=SEGMENTATION %THEN %DO; /* ready to build autoseg */ data &fullname; length id $25.; format segment_rule_label $2000. predicted_0 predicted_1 weighted_response nlnum8.2 all_visitors nlnum12. pc_visits nlnum8.2 exp_resp_all nlnum12. new_visitors nlnum12. pc_visits_new nlnum8.2 exp_resp_new nlnum12. maxdate_visitors nlnum12. pc_visits_maxdate nlnum8.2 exp_resp_maxdate nlnum12. ; label all_visitors='All Visitors' new_visitors='New Visitors' maxdate_visitors='Most Current Visitors' pc_visits='% All Visitors' pc_visits_new='% New Visitors' pc_visits_maxdate='% Most Current Visitors' exp_resp_all='All Predicted Responders' exp_resp_new='New Predicted Responders' exp_resp_maxdate='Most Current Predicted Responders'; run; %END; %ERREXIT: %mend makenulldata; %******************************************************************************; %* Name: wametatr ; %* Purpose: Assign standard attributes to WA metadata variables. ; %* NOTE: there are two blocks of code -- one for the CONFIG library ; %* data sets, and a 2nd for the DOCUMENT library data sets ; %* Usage: %wametatr(srce_dsn=wasumeng ; %* ,retcode=wab_rc ; %* ) ; %* Change History ; %* Date Description Username Change Code ; %* 11/10/2004 Added label and changed scorecard, cabahl S0275612 ; %* dashboard and segmentation_name to name in WASCRCRD, WADSHBRD ; %* WAAUTOSG attributes statements ; %* 04/06/2006 add new field to WASUMENG frroed S0351214 ; %******************************************************************************; %macro wametatr(srce_dsn= ,retcode= ); %local macname; %let macname=&sysmacroname; %let &retcode=0; %* start of the CONFIG library attributes --------------------------------- ; %if %upcase(&srce_dsn) eq WASUMENG %then %do; attrib id length =$36 format =$36. informat=$36. label='ID for metadata maintenance' input_table length =$41 format =$41. informat=$41. label='LIBNAME and DSN of input data set' summary_stmt_opts length =$1024 format =$1024. informat=$1024. label='PROC SUMMARY statement options' where_stmt length =$4096 format =$4096. informat=$4096. label='Input data set WHERE clause' partition_by_date length =8 format =8. informat=8. label='Indicator: is input/output data date partitioned?' id_vars length =$4096 format =$4096. informat=$4096. label='Comma-delimited list of ID vars' class_vars length =$4096 format =$4096. informat=$4096. label='Comma-delimited list of Class vars' analysis_vars length =$4096 format =$4096. informat=$4096. label='Comma-delimited list of Analysis vars' freq_vars length =$4096 format =$4096. informat=$4096. label='Comma-delimited list of Freq vars' weight_vars length =$4096 format =$4096. informat=$4096. label='Comma-delimited list of Weight vars' output_table length =$41 format =$41. informat=$41. label='LIBNAME and DSN of output data set' summary_outdsn_opts length =$4096 format =$4096. informat=$4096. label='OUTPUT OUT= data set options (except for WHERE)' summary_outdsn_where length =$4096 format =$4096. informat=$4096. label='OUTPUT OUT= WHERE clause' summary_output_opts length =$1024 format =$1024. informat=$1024. label='OUTPUT statement options' need_re_summary length =8 format =8. informat=8. label='0/1 indicator of whether re-summarization is needed' ; %end; %else %if %upcase(&srce_dsn) eq WAINIT or %upcase(&srce_dsn) eq WBINIT %then %do; attrib Name length=$32 label="Webmart Name" Desc length=$200 label="Description" Perm length=$200 label="Webmart Location" Flag length=3 label="Internal use" ; %end; %else %if %upcase(&srce_dsn) eq WADSHINP %then %do; attrib id length =$36 format =$36. informat=$36. label='ID for metadata maintenance' dashboard_id length=$36 format =$36. informat=$36. label='Dashboard ID' time_span length=$25 format =$25. informat=$25. label='Time Span' role length=$25 format =$25. informat=$25. label='Variable Role' _name_ length=$32 format =$32. informat=$32. label='Variable Name' _name_category_ length=$32 format =$32. informat=$32. label='Variable Category' _label_ length=$100 format =$100. informat=$100. label='Variable Label' business_dir length=$8 format =$8. informat=$8. label='Positive Business Direction' link length=$4096 format =$4096. informat=$4096. label='Hyperlink for Customized Drill' dashboard_link length=$36 format =$36. informat=$36. label='Dashboard-to-Dashboard Link' ; %end; %else %if %upcase(&srce_dsn) eq WADSHBRD %then %do; attrib id length =$36 format =$36. informat=$36. label="Dashboard ID" name length=$25 format =$25. informat=$25. label="Dashboard Name" indsn length=$41 format =$41. informat=$41. label="Input Data Set Name" label length=$25 format =$25. informat=$25. label="Dashboard Label" etl_run_sw length=8 format=1. informat=1. label="0/1 switch for creating in WAETL" ; %end; %else %if %upcase(&srce_dsn) eq WASCRINP %then %do; attrib id length =$36 format =$36. informat=$36. label='ID for metadata maintenance' scorecard_id length=$36 format =$36. informat=$36. label="Scorecard ID" role length=$25 format =$25. informat=$25. label="Variable Role" _name_ length=$32 format =$32. informat=$32. label="Variable Name" _label_ length=$100 format =$100. informat=$100. label="Variable Label" business_dir length=$8 format =$8. informat=$8. label="Positive Business Direction" measurement_range length=$40 format =$40. informat=$40. label="Variable Measurement Range" link length=$4096 format =$4096. informat=$4096. label='Hyperlink for Customized Drill' ; %end; %else %if %upcase(&srce_dsn) eq WASCRCRD %then %do; attrib id length=$36 format =$36. informat=$36. label="Scorecard ID" name length=$25 format =$25. informat=$25. label="Scorecard Name" indsn length=$41 format =$41. informat=$41. label="Input Data Set Name" label length=$25 format =$25. informat=$25. label="Scorecard Label" etl_run_sw length=8 format=1. informat=1. label="0/1 switch for creating in WAETL" ; %end; %else %if %upcase(&srce_dsn) eq WAAUTINP %then %do; attrib id length =$36 format =$36. informat=$36. label='ID for metadata maintenance' Segmentation_ID length=$36 format =$36. informat=$36. label="Segmentation ID" role length=$25 format =$25. informat=$25. label="Variable Role" level length=$25 format=$25. informat=$25. label="Variable Measurement Level" _name_ length=$32 format =$32. informat=$32. label="Variable Name" _label_ length=$100 format =$100. informat=$100. label="Variable Label" ; %end; %else %if %upcase(&srce_dsn) eq WAAUTOSG %then %do; attrib id length =$36 format =$36. informat=$36. label="Segmentation ID" name length=$25 format =$25. informat=$25. label="Segmentation Name" indsn length=$41 format=$41. informat=$41. label="Input Data Set Name" num_segments_to_create length =8 label='Number of segments to create' data_prep_include_code length=$2000 label='Data preparation code' label length=$25 format =$25. informat=$25. label="Segmentation Label" etl_run_sw length=8 format=1. informat=1. label="0/1 switch for creating in WAETL" ; %end; %else %if %upcase(&srce_dsn) eq WACONFIG %then %do; attrib id length =$36 format =$36. informat=$36. label='ID for metadata maintenance' value length=$1024 format=$1024. informat=$1024. label="Value" description length=$1024 format=$1024. informat=$1024. label="Description" parameter_name length=$32 format =$32. informat=$32. label="Parameter Name" ; %end; %else %if %upcase(&srce_dsn) eq WADTSORT %then %do; attrib Dow_id length =$2 format =$2. informat=$2. label='Day of Week ID' Dow_Name length=$12 label ="Day of Week Name" Dow_Sort length =$2 label="Day of Week Sort Value" ; %end; %else %if %upcase(&srce_dsn) eq WARESRCE %then %do; attrib id length =$64 format =$64. informat=$64. label='ID -- name of feature' value length=$1024 format =$1024. informat=$1024. label="Value/Setting of feature" description length=$256 format=$256. informat=$256. label="Description of feature" ; %end; %else %if %upcase(&srce_dsn) eq WATBLDSC %then %do; attrib id length =$64 format =$64. informat=$64. label='ID -- name of feature' value length=$1024 format =$1024. informat=$1024. label="Value/Setting of feature" description length=$256 format=$256. informat=$256. label="Description of feature" ; %end; %else %if %upcase(&srce_dsn) eq WAADMSUM %then %do; attrib id length =$36 format =$36. informat=$36. label='ID for metadata maintenance' label length=$256 format =$256. informat=$256. label="Label to Identify Summary Type" source_table length=$32 format =$32. informat=$32. label="Name of Source Table" proc_summary_options length=$1024 format =$1024. informat=$1024. label="PROC SUMMARY options" input_where length=$1024 format =$1024. informat=$1024. label="WHERE clause for filtering input data set" summary_level_list length=$30 format=$30. informat=$30. label="Comma-delimited list of summary levels for ID vars" class_var_list length=$4096 format =$4096. informat=$4096. label="Comma-delimited list of CLASS vars" id_var_list length=$4096 format =$4096. informat=$4096. label="Comma-delimited list of ID vars" id_var_summary_level_list length=$30 format=$30. informat=$30. label="Comma-delimited list of summary levels for ID vars" analysis_var_list length=$4096 format =$4096. informat=$4096. label="Comma-delimited list of Analysis vars" output_dsn_options_0 length=$4096 format =$4096. informat=$4096. label="Data set options for the re-summarization OUT= data set" output_dsn_options_1 length=$4096 format =$4096. informat=$4096. label="Data set options for the initial summarization OUT= data set" output_dsn_where_stmt length=$4096 format =$4096. informat=$4096. label="Data set WHERE statement for the OUT= data set" output_options length=$1024 format =$1024. informat=$1024. label="PROC SUMMARY keyword options" results_table length=$26 format =$26. informat=$26. label="Name of Table with Summarized Results" summary_level_list length=$30 format=$30. informat=$30. label="Comma-delimited list of summary levels" etl_run_sw length=8 format=1. informat=1. label="0/1 switch for creating Summaries in WAETL" ; %end; %else %if %upcase(&srce_dsn) eq WAENGVAR %then %do; attrib id length =$36 format =$36. informat=$36. label='ID for metadata maintenance' libname length=$8 format =$8. informat=$8. label="Library Name" memname length=$32 format =$32. informat=$32. label="Member/DataSet Name" name length=$32 format =$32. informat=$32. label="Column Name" label length=$256 format =$256. informat=$256. label="Column Label" ; %end; %else %if %upcase(&srce_dsn) eq WAFUNNEL %then %do; attrib id length =$36 format =$36. informat=$36. label='ID for metadata maintenance' label length=$128 format =$128. informat=$128. label="Funnel Name" url length=$255 format =$255. informat=$255. label="Comma-delimited list of Web Page/URL IDs" adjacent length=$255 format =$255. informat=$255. label="Comma-delimited list of Adjacent indicators" IsStatic length=8 format =1. informat=1. label="0/1 switch for creating during ETL" ; %end; %else %if %upcase(&srce_dsn) eq WAPATH %then %do; attrib id length =$36 format =$36. informat=$36. label='ID for metadata maintenance' label length=$128 format =$128. informat=$128. label="Path Name" start_page length=$255 format =$255. informat=$255. label="Comma-delimited list of IDs for REQUESTED_FILE Start Pages" end_page length=$255 format =$255. informat=$255. label="Comma-delimited list of IDs for REQUESTED_FILE End Pages" topn length=$5 format =$5. informat=$5. label="Maximum number of rows that will be in the results table" max_sequence_length length=$5 format =$5. informat=$5. label="Maximum number of requested files in a path" min_sequence_length length=$5 format =$5. informat=$5. label="Minimum number of requested files in a path" ; %end; %* end of the CONFIG library attributes ----------------------------------- ; %* start of the DOCUMENT library attributes ------------------------------- ; %else %if %upcase(&srce_dsn) eq ASSIGNMENT %then %do; attrib Id length =$36 Datasource length =$36 Libname length =$36 Path length =$255 ; %end; %else %if %upcase(&srce_dsn) eq DATASOURCE %then %do; attrib Id length =$36 Label length =$128 Documents length =$36 DataHost length =$128 DataPort length =8 DataUser length =$128 DataPassword length =$128 ProcessHost length =$128 ProcessPort length =8 ProcessUser length =$128 ProcessPassword length =$128 Driver length =$128 Prefix length =$128 Repository length =$128 ; %end; %else %if %upcase(&srce_dsn) eq DEFINITION %then %do; attrib Id length =$36 Label length =$128 Description length =$255 DocGroup length =$36 Helpfile length =$255 DefaultGraph length =$36 SortField length =$36 SortOrder length =8 Delimiter length =$1 Sortnum length =8 IsPublic length =8 URL length =$1024 ; %end; %else %if %upcase(&srce_dsn) eq DOCGROUP %then %do; attrib Id length =$36 Label length =$128 Description length =$255 Documents length =$36 Sortnum length =8 IsPublic length =8 ; %end; %else %if %upcase(&srce_dsn) eq DOCUMENTS %then %do; attrib Id length =$36 Label length =$128 Description length =$255 PrimeDatasource length =$36 GraphStyle length =$36 GraphScheme length =$36 GraphWidth length =8 GraphHeight length =8 ; %end; %else %if %upcase(&srce_dsn) eq ENTRY %then %do; attrib Id length =$36 Label length =$128 Description length =$255 Parent length =$36 EntryTag length =$36 Definition length =$36 Datasource length =$36 Db length =$128 DbTable length =$128 Program length =$36 IsAll length =8 ; %end; %else %if %upcase(&srce_dsn) eq ENTRYFILTER %then %do; attrib Id length =$36 Label length =$128 Entry length =$36 DbColumn length =$36 Operator length =$10 FilterValue length =$255 ; %end; %else %if %upcase(&srce_dsn) eq FIELD %then %do; attrib Id length =$36 Label length =$36 Entry length =$36 DbColumn length =$36 Tag length =$36 Link length =$255 Sortnum length =8 SortOrder length =8 NumRows length =8 IsDisplayOrdinal length =8 IsClass length =8 IsHidden length =8 IsPercentage length =8 IsPercentageClass length =8 Formula length =$255 ; %end; %else %if %upcase(&srce_dsn) eq GRAPH %then %do; attrib Id length =$36 Label length =$36 Description length =$255 Definition length =$36 GraphType length =$36 Style length =$36 Scheme length =$36 Width length =8 Height length =8 ; %end; %else %if %upcase(&srce_dsn) eq GRAPHVARIABLE %then %do; attrib Id length =$36 Label length =$128 Graph length =$36 Field length =$36 Axis length =8 ; %end; %else %if %upcase(&srce_dsn) eq INPUTVALUE %then %do; attrib Id length =$36 Datasource length =$36 Name length =$128 InputValue length =$128 SourceDb length =$36 SourceTable length =$36 ; %end; %else %if %upcase(&srce_dsn) eq PARM %then %do; attrib Id length =$36 Program length =$36 Name length =$128 ParmValue length =$128 ; %end; %else %if %upcase(&srce_dsn) eq PROGRAM %then %do; attrib Id length =$36 Label length =$128 Datasource length =$36 Code length =$128 Description length =$255 ; %end; %* end of the DOCUMENT library attributes --------------------------------- ; %* start of the SUMMARY library attributes -------------------------------- ; %***************************************************************************; %* these are NOT all the fields in PATH analysis data sets, just the ones ; %* that need to have common attributes ; %***************************************************************************; %else %if %upcase(&srce_dsn) eq PATHS %then %do; attrib Count length=8 format=NLNUM12. label ="Visits" Support length=8 format=NLNUM6.2 label="% Visits" Conf length=8 format=NLNUM6.2 label="% Dropoff" pc_root length=8 format=NLNUM6.2 label="% Root" ; %end; %* end of the SUMMARY library attributes ---------------------------------- ; %else %do; %put %unquote(&wab_error) %upcase(&srce_dsn) does not %QCMPRES(have standard) Web Analytics Metadata Variable Attributes.; %let &retcode=1; %end; %mend wametatr; %******************************************************************************; %* Name: watstdsn ; %* Purpose: Test whether a SAS data set has the correct variables and ; %* attributes. ; %* Usage: %watstdsn(test_dsn=config.wasumeng ; %* ,tmpl_dsn=wasumeng ; %* ,retcode=wab_rc ; %* ) ; %******************************************************************************; %macro watstdsn(test_dsn= ,tmpl_dsn= ,retcode= ); %local macname; %let macname=&sysmacroname; %let &retcode=0; %***************************************************************************; %* Check whether &TEST_DSN exists ; %***************************************************************************; %if %sysfunc(exist(&test_dsn))=0 %then %do; %put %unquote(&wab_error) The &test_dsn data set does not exist.; %let &retcode=1; %goto ERREXIT; %end; %***************************************************************************; %* Create empty data set with std set of variables (& attributes) ; %***************************************************************************; data _wab_template; %wametatr(srce_dsn=&tmpl_dsn ,retcode=wab_rc ); stop; run; %if &wab_rc=1 %then %do; %* %WAMETATR failed ; %goto ERREXIT; %end; %***************************************************************************; %* Identify the LIBNAME/MEMNAME for &TEST_DSN ; %***************************************************************************; data _null_; length libname memname $32; if not index("&test_dsn",'.') then do; libname='WORK'; memname=upcase("&test_dsn"); end; else do; libname=upcase(scan("&test_dsn",1,'.')); memname=upcase(scan("&test_dsn",2,'.')); end; call symput('test_lib',strip(libname)); call symput('test_mem',strip(memname)); run; %***************************************************************************; %* retrieve info about vars in template and &TEST_DSN ; %***************************************************************************; proc contents data=work._wab_template noprint out =compare (keep=libname memname name npos varnum type length label format informat); run; proc contents data=&test_lib..&test_mem noprint out =base (keep=libname memname name npos varnum type length label format informat); run; %***************************************************************************; %* compare the retrieved information ; %***************************************************************************; proc compare base=base (drop=libname memname) compare=compare (drop=libname memname) out=_wab_comp_results outdif noprint; by name; run; %if %get_observation_count(indsn=_wab_comp_results)=0 %then %do; %put %unquote(&wab_error) %upcase(&test_dsn) set does not exist.; %let &retcode=1; %end; data _null_; set _wab_comp_results end=eof; error_idx+(sum(abs(length),abs(npos),abs(varnum))); if eof then do; if error_idx ne 0 then error_idx=1; call symput("&retcode",strip(put(error_idx,1.))); end; run; %if &wab_rc=1 %then %do; %put %unquote(&wab_error) %upcase(&test_dsn) set does not %QCMPRES(have the) standard variables/attributes.; %end; %ERREXIT:; %mend watstdsn; %******************************************************************************; %* Copyright (C) 2000 by SAS Institute Inc., Cary, NC 27512-8000 ; %* ; %* Name: Current_OS ; %* ; %* Purpose: Determine current operating system ; %* ; %* Output: global macro variables: ; %* operating_system -- valid values: MVS, OSYS or WIN ; %* separator -- valid values: /, \, or ( ; %* ; %* Usage: %Current_OS; ; %* Notes: Determines the current operating system based on the ; %* values of the global system macro variable SYSSCP ; %* ; %* ; %******************************************************************************; %macro Current_OS; %global operating_system separator make_dir remove_dir temp_dir find_options; options nonotes; data _null_; select; when (upcase("&sysscp") in ("SUN 64","HP IPF","AIX 64")) do; call symput("operating_system","OSYS") ; call symput("separator","/") ; call symput("make_dir","mkdir -p"); call symput("remove_dir","rm -Rf"); call symput("delete","/bin/rm"); call symput("delete_all","/bin/rm"); call symput("temp_dir","/tmp"); call symput("find_options", "! -type d -follow -exec /bin/ls -Ls {} \;"); end ; %* Includes ; %* Windows NT (WIN_NT) ; %* Windows NT Server (WIN_NTSV); %* Windows 2000 Professional (WIN_PRO) ; %* Windows 2000 Server (WIN_SRV) ; %* Windows 2000 Advanced Server (WIN_ASRV); %* Windows 2000 DC Server (WIN_DSRV); %* Windows 95 (WIN_94) ; %* Windows 98 (WIN_98) ; %* Windows Millennium (WIN_ME) ; when (upcase("&sysscp") in ("WIN","DNTHOST")) do ; call symput("operating_system","WIN") ; call symput("separator",'\') ; call symput("make_dir","md"); call symput("remove_dir", "rd /s /q"); call symput("delete","erase"); call symput("delete_all","erase /Q"); %* Assign Temporary directory based on environment ; %* variable TEMP ; filename tmp '!TEMP'; rc=fileref("tmp"); if (rc=0) then do; fname=pathname("tmp"); if fname eq "" then do; fname = "C:\TEMP"; end; end; else do; fname = "C:\TEMP"; end; call symput("temp_dir", trim(fname)); filename tmp clear; end ; otherwise do; call symput('_wab_rc_', '1'); end; end ; run; %if &_wab_rc_ eq 1 %then %do; %put %unquote(&wab_error) An error has occurred. Unknown operating system.; %put %unquote(&wab_error) Value of sysscpl is: &sysscpl..; %put %unquote(&wab_error) Value of sysscp is : &sysscp..; %end; %Maybe_Notes; %mend Current_OS; %******************************************************************************; %* Name: Make_Directory ; %* Purpose: Turn off options notes based on wbdebug flag ; %* Usage: %Make_Directory ; %******************************************************************************; %macro make_directory(dirname= ,retcode=); %local macname; %let macname=&sysmacroname; %let &retcode=0; %* Initialize return code ; %if not %sysfunc(fileexist(&dirname)) %then %do ; %put %unquote(&wab_note) Creating directory &dirname; %No_Notes; %execcmd(&make_dir "&dirname"); %if not %sysfunc(fileexist(&dirname)) %then %do ; %put %unquote(&wab_error) Cannot create directory: &dirname; %put %unquote(&wab_error) Check path and operating system permissions; %let &retcode=1; %end; %maybe_notes; %end; %mend make_directory; %******************************************************************************; %* Name: Remove_Directory ; %* Purpose: Remove a directory (and all its sub-directories) ; %* Usage: %Remove_Directory ; %******************************************************************************; %macro remove_directory(rempath= ,retcode=); %local macname; %let macname=&sysmacroname; %let &retcode=0; %* Initialize return code ; %if %sysfunc(fileexist(&rempath)) %then %do ; %put %unquote(&wab_note) Removing directory &rempath.; data _null_; length cmd_str $200; select; when (upcase("&sysscp") in ("SUN 64","HP IPF","AIX 64")) do; cmd_str='rm ' || "&rempath" || ' -Rf'; end ; when (upcase("&sysscp") in ("WIN","DNTHOST")) do ; cmd_str='rd ' || "&rempath" || ' /s /q'; end; end; call system(cmd_str); run; %if %sysfunc(fileexist(&rempath)) %then %do ; %put %unquote(&wab_error) Cannot remove directory: &rempath.; %put %unquote(&wab_error) Check path and operating system permissions; %let &retcode=1; %end; %end; %mend remove_directory; %******************************************************************************; %* ; %* Copyright (c) 2004 by SAS Institute Inc., Cary, NC 27512-8000 ; %* ; %* Type: Macro ; %* ; %* Program: get_observation_count.sas (Web Analytics) ; %* ; %* Purpose: To get the number of observations in a data set. ; %* ; %* Dependencies: ; %* The following macro variables need to be defined and assigned values ; %* in the invoking environment: ; %* &WAB_NOTE: the text that identifies WA macro notification messages. ; %* &WAB_ERROR: the text that identifies WA macro errors. ; %* ; %* Parameters: ; %* INDSN: (REQUIRED) The input data set whose observations are to be ; %* retrieved. ; %* RETCODE: mechanism for passing Return Codes (obsolete) ; %* ; %* Output: ; %* NOBS: local macro variable -- the number of obs in INDSN. ; %* ; %******************************************************************************; %macro get_observation_count(indsn=,retcode=); %local macname; %let macname=&sysmacroname; %local nobs; %let nobs=.; %******************************************************************************; %* open the data set of interest. ; %******************************************************************************; %let indsnid=%sysfunc(open(&indsn)); %******************************************************************************; %* if the open is successful then use ATTRN to get the number of observations. ; %* be sure to close INDSN. ; %******************************************************************************; %if &indsnid %then %do; %put %unquote(&wab_note) Successfully opened &indsn; %let nobs=%sysfunc(attrn(&indsnid,nlobs)); %let rc=%sysfunc(close(&indsnid)); %end; %else %do; %put %unquote(&wab_error) unable to open &indsn - %sysfunc(sysmsg()); %end; %******************************************************************************; %* because the following line is the only text that is not part of a macro ; %* statement, NOBS will be effectively returned to the invoking environment ; %* as though the GET_OBSERVATION_COUNT macro were a function. however, any ; %* other non-macro statement within the GET_OBSERVATION_COUNT macro ; %* (including an extraneous semicolon!) will also be part of this exchange. ; %* be especially careful when updating this macro to add only macro ; %* statements or you will distrupt its current functionality. ; %******************************************************************************; &nobs %mend get_observation_count; /*----------------------Web Analytics-----------------------------------------*/ /* LOCKTEST */ /* Test whether a given data set is locked. */ /* */ /*----------------------------------------------------------------------------*/ /* Supported By: Frank Roediger, FRROED */ /* */ /*----------------------------------------------------------------------------*/ /* History: */ /* Date Description Username Change Code */ /* 20040420 Program Created frroed NA */ /* */ /*----------------------------------------------------------------------------*/ /* Dependencies: */ /* None */ /* */ /*----------------------------------------------------------------------------*/ /* Parameters: */ /* DSET: the name of the data set to be tested */ /* LOCK_SW: the mechanism for indicating whether the specified data set is */ /* locked. */ /* 0 - data set exists and is not locked */ /* 1 - data set exists but is locked */ /* . - data set does not exist */ /* */ /*----------------------------------------------------------------------------*/ /* Input: */ /* (dset= */ /* ); */ /* */ /*----------------------------------------------------------------------------*/ /* Output: */ /* */ /*----------------------------------------------------------------------------*/ /* Copyright (c) 2004 SAS Institute Inc. All Rights Reserved */ /*----------------------------------------------------------------------------*/ %macro locktest(dset=); %global lock_sw; %let lock_sw=0; %***************************************************************************; %* make sure that the DSET parameter has both libname and memname ; %* (assume libname=WORK if there is no embedded period in &DSET). ; %***************************************************************************; %if not %index(&dset,.) %then %do; %let dset=work.&dset; %end; %let open_rc=%sysfunc(open(&dset)); %if &open_rc %then %do; %let close_rc=%sysfunc(close(&open_rc)); lock &dset query; %if &syslckrc eq -630099 %then %do; %* &DSET exists and is not locked; %let lock_sw=0; %end; %else %do; %* &DSET exists and is locked; %let lock_sw=1; %end; %end; %else %do; %* &DSET does not exist; %let lock_sw=.; %end; %mend locktest; /*----------------------Web Analytics-----------------------------------------*/ /* WAMETDEF */ /* Define and create the Web Analytics metadata data sets */ /* */ /*----------------------------------------------------------------------------*/ /* Supported By: Frank Roediger, FRROED */ /* */ /*----------------------------------------------------------------------------*/ /* History: */ /* Date Description Username Change Code */ /* 20040831 Program Created frroed NA */ /* 20041007 Added DEF_LIB parameter frroed C001 */ /* 20041110 Added label to WAAUTOSG, cabahl S0275612 */ /* WADSHBRD and WASCRCRD renamed scorecard, dashboard and */ /* segmentation_name to name */ /* */ /*----------------------------------------------------------------------------*/ /* Dependencies: */ /* %WAMETATR */ /* */ /*----------------------------------------------------------------------------*/ /* Parameters: */ /* DEF_DSN: the name of the data set to be defined/created */ /* DEF_LIB: the LIBNAME for &DEF_DSN */ /* RETCODE: the mechanism for indicating whether the macro ran */ /* successfully */ /* */ /*----------------------------------------------------------------------------*/ /* Input: */ /* (def_dsn= */ /* ,def_lib=config */ /* ,retcode= */ /* ); */ /* */ /*----------------------------------------------------------------------------*/ /* Output: */ /* */ /*----------------------------------------------------------------------------*/ /* Copyright (c) 2004 SAS Institute Inc. All Rights Reserved */ /*----------------------------------------------------------------------------*/ %macro wametdef(def_dsn= ,def_lib=config ,retcode= ); %local macname; %let macname=&sysmacroname; %let &retcode=0; %******************************************************************************; %* MODULE: Create WASUMENG ; %* SUPPORT:frroed ; %******************************************************************************; %if %upcase(&def_dsn) eq WASUMENG %then %do; %***************************************************************************; %* NOTE: this block was removed when WAADMSUM replaced WASUMENG ; %***************************************************************************; %end; %******************************************************************************; %* MODULE: Create WASCRINP ; %* SUPPORT:sassyw ; %******************************************************************************; %else %if %upcase(&def_dsn) eq WASCRINP %then %do; data &def_lib..wascrinp; %wametatr(srce_dsn=wascrinp ,retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Attributes cannot be assigned %QCMPRES( for) the variables in the &def_dsn data set.; stop; run; proc datasets library=&def_lib nolist; delete &def_dsn; quit; %goto ERREXIT; %end; id=id; %* avoid the "Variable id is uninitialized" log notice ; scorecard_id ='01'; role ='input'; _name_ ='err_501_count'; _label_ ='501 Count: Not Implemented'; business_dir ='DOWN'; measurement_range='POSITIVE NUMBER'; link =' '; output; scorecard_id ='01'; role ='input'; _name_ ='file_count'; _label_ ='Hit Count'; business_dir ='UP'; measurement_range='POSITIVE NUMBER'; link =' '; output; scorecard_id ='01'; role ='input'; _name_ ='page_count'; _label_ ='Page Count'; business_dir ='UP'; measurement_range='POSITIVE NUMBER'; link =' '; output; scorecard_id ='01'; role ='target'; _name_ ='session_count'; _label_ ='Visit Count'; business_dir ='UP'; measurement_range='POSITIVE NUMBER'; link =' '; output; scorecard_id ='01'; role ='input'; _name_ ='duration'; _label_ ='Duration in Seconds'; business_dir ='UP'; measurement_range='POSITIVE NUMBER'; link =' '; output; scorecard_id ='01'; role ='input'; _name_ ='total_bytes_sent'; _label_ ='Total Bytes Sent'; business_dir ='UP'; measurement_range='POSITIVE NUMBER'; link =' '; output; run; data &def_lib..wascrinp; set &def_lib..wascrinp; seq+1; id=strip(put(seq,6.)); drop seq; run; proc datasets library=&def_lib nolist; modify wascrinp; index create id / nomiss unique; quit; %end; %******************************************************************************; %* MODULE: Create WASCRCRD ; %* SUPPORT:sassyw ; %******************************************************************************; %else %if %upcase(&def_dsn) eq WASCRCRD %then %do; data &def_lib..wascrcrd; %wametatr(srce_dsn=wascrcrd ,retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Attributes cannot be assigned %QCMPRES( for) the variables in the &def_dsn data set.; stop; run; proc datasets library=&def_lib nolist; delete &def_dsn; quit; %goto ERREXIT; %end; id ='01'; name ='Site Metrics'; indsn ='summary.daily_total_day'; label ='Site Metrics'; etl_run_sw=1; output; run; proc datasets library=&def_lib nolist; modify wascrcrd; index create id / nomiss unique; quit; %end; %******************************************************************************; %* MODULE: Create WADSHINP ; %* SUPPORT:sassyw ; %******************************************************************************; %else %if %upcase(&def_dsn) eq WADSHINP %then %do; data &def_lib..wadshinp; %wametatr(srce_dsn=wadshinp ,retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Attributes cannot be assigned %QCMPRES( for) the variables in the &def_dsn data set.; stop; run; proc datasets library=&def_lib nolist; delete &def_dsn; quit; %goto ERREXIT; %end; id=id; %* avoid the "Variable id is uninitialized" log notice ; dashboard_id ='01'; time_span ='day'; role ='metric'; _name_ ='err_302_count'; _name_category_='Site Health'; _label_ ='302 Count: Found'; business_dir ='DOWN'; link =' '; dashboard_link=' '; output; dashboard_id ='01'; time_span ='day'; role ='metric'; _name_ ='err_304_count'; _name_category_='Site Health'; _label_ ='304 Count: Not Modified'; business_dir ='DOWN'; link =' '; dashboard_link=' '; output; dashboard_id ='01'; time_span ='day'; role ='metric'; _name_ ='err_400_count'; _name_category_='Site Health'; _label_ ='400 Count: Bad Request'; business_dir ='DOWN'; link =' '; dashboard_link=' '; output; dashboard_id ='01'; time_span ='day'; role ='metric'; _name_ ='err_401_count'; _name_category_='Site Health'; _label_ ='401 Count: Unauthorized'; business_dir ='DOWN'; link =' '; dashboard_link=' '; output; dashboard_id ='01'; time_span ='day'; role ='metric'; _name_ ='err_403_count'; _name_category_='Site Health'; _label_ ='403 Count: Forbidden'; business_dir ='DOWN'; link =' '; dashboard_link=' '; output; dashboard_id ='01'; time_span ='day'; role ='metric'; _name_ ='err_404_count'; _name_category_='Site Health'; _label_ ='404 Count: Not Found'; business_dir ='DOWN'; link =' '; dashboard_link=' '; output; dashboard_id ='01'; time_span ='day'; role ='metric'; _name_ ='err_405_count'; _name_category_='Site Health'; _label_ ='405 Count: Method Not Allowed'; business_dir ='DOWN'; link =' '; dashboard_link=' '; output; dashboard_id ='01'; time_span ='day'; role ='metric'; _name_ ='err_408_count'; _name_category_='Site Health'; _label_ ='408 Count: Request Time-out'; business_dir ='DOWN'; link =' '; dashboard_link=' '; output; dashboard_id ='01'; time_span ='day'; role ='metric'; _name_ ='err_500_count'; _name_category_='Site Health'; _label_ ='500 Count: Internal Server Error'; business_dir ='DOWN'; link =' '; dashboard_link=' '; output; dashboard_id ='01'; time_span ='day'; role ='metric'; _name_ ='err_501_count'; _name_category_='Site Health'; _label_ ='501 Count: Not Implemented'; business_dir ='DOWN'; link =' '; dashboard_link=' '; output; dashboard_id ='01'; time_span ='day'; role ='metric'; _name_ ='file_count'; _name_category_='Traffic'; _label_ ='Hit Count'; business_dir ='UP'; link =' '; dashboard_link=' '; output; dashboard_id ='01'; time_span ='day'; role ='metric'; _name_ ='One_Hit_session_count'; _name_category_='Traffic'; _label_ ='One Hit Visits'; business_dir ='DOWN'; link =' '; dashboard_link=' '; output; dashboard_id ='01'; time_span ='day'; role ='metric'; _name_ ='page_count'; _name_category_='Traffic'; _label_ ='Page Count'; business_dir ='UP'; link =' '; dashboard_link=' '; output; dashboard_id ='01'; time_span ='day'; role ='metric'; _name_ ='session_count'; _name_category_='Traffic'; _label_ ='Visit Count'; business_dir ='UP'; link =' '; dashboard_link=' '; output; dashboard_id ='01'; time_span ='day'; role ='metric'; _name_ ='duration'; _name_category_='Traffic'; _label_ ='Duration in Seconds'; business_dir ='UP'; link =' '; dashboard_link=' '; output; dashboard_id ='01'; time_span ='day'; role ='metric'; _name_ ='page_view_2to4_ind'; _name_category_='Traffic'; _label_ ='2-4 Page Visit Count'; business_dir ='UP'; link =' '; dashboard_link=' '; output; dashboard_id ='01'; time_span ='day'; role ='metric'; _name_ ='page_view_le1_ind'; _name_category_='Traffic'; _label_ ='0-1 Page Visit Count'; business_dir ='DOWN'; link =' '; dashboard_link=' '; output; dashboard_id ='01'; time_span ='day'; role ='metric'; _name_ ='page_view_ge5_ind'; _name_category_='Traffic'; _label_ ='>= 5 Page Visit Count'; business_dir ='UP'; link =' '; dashboard_link=' '; output; dashboard_id ='01'; time_span ='day'; role ='metric'; _name_ ='total_bytes_sent'; _name_category_='Traffic'; _label_ ='Total Bytes Sent'; business_dir ='UP'; link =' '; dashboard_link=' '; output; run; data &def_lib..wadshinp; set &def_lib..wadshinp; seq+1; id=strip(put(seq,6.)); drop seq; run; proc datasets library=&def_lib nolist; modify wadshinp; index create id / nomiss unique; quit; %end; %******************************************************************************; %* MODULE: Create WADSHBRD ; %* SUPPORT:sassyw ; %******************************************************************************; %else %if %upcase(&def_dsn) eq WADSHBRD %then %do; data &def_lib..wadshbrd; %wametatr(srce_dsn=wadshbrd ,retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Attributes cannot be assigned %QCMPRES( for) the variables in the &def_dsn data set.; stop; run; proc datasets library=&def_lib nolist; delete &def_dsn; quit; %goto ERREXIT; %end; id ='01'; name ='Site Metrics'; indsn ='summary.daily_total_day'; label ='Site Metrics'; etl_run_sw=1; output; run; proc datasets library=&def_lib nolist; modify wadshbrd; index create id / nomiss unique; quit; %end; %******************************************************************************; %* MODULE: Create WADTSORT ; %* SUPPORT:frroed ; %* NOTE: the libref for WADTSORT is always SUMMARY so that there will not be ; %* any issue about authorization/access when creating this data set ; %* (depending upon the way that SWA is set up and how user permissions ; %* are assigned, writing WADTSORT to the CONFIG library might fail) ; %******************************************************************************; %else %if %upcase(&def_dsn) eq WADTSORT %then %do; data summary.wadtsort; %wametatr(srce_dsn=wadtsort ,retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Attributes cannot be assigned %QCMPRES( for) the variables in the &def_dsn data set.; stop; run; proc datasets library=&def_lib nolist; delete &def_dsn; quit; %goto ERREXIT; %end; do date=today() to (today()+6); Dow_ID=strip(put(weekday(date),2.)); start_of_week=intnx("week&wab_week_shift",date,0); weekday_sow =weekday(start_of_week); dow_sort_value=1 + (weekday(date)-weekday_sow); if dow_sort_value eq 0 then dow_sort_value=7; dow_sort=strip(put(dow_sort_value,2.)); Dow_Name=strip(put(date,downame.)); output; end; keep dow_id dow_name dow_sort; run; proc sort data=summary.wadtsort; by Dow_Sort; run; %end; %******************************************************************************; %* MODULE: Create WACONFIG ; %* SUPPORT:saskxs ; %******************************************************************************; %else %if %upcase(&def_dsn) eq WACONFIG %then %do; data &def_lib..waconfig; %wametatr(srce_dsn=waconfig ,retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Attributes cannot be assigned %QCMPRES( for) the variables in the &def_dsn data set.; stop; run; proc datasets library=&def_lib nolist; delete &def_dsn; quit; %goto ERREXIT; %end; id=id; %* avoid the "Variable id is uninitialized" log notice ; Parameter_Name='wab_r1_support'; Value='5'; Description='The minimum count of total sessions for path for 1 day.'; output; Parameter_Name='wab_r7_support'; Value='10'; Description='The minimum count of total sessions for path for 7 day.'; output; Parameter_Name='wab_r30_support'; Value='20'; Description='The minimum count of total sessions for path for 30 days.'; output; Parameter_Name='wab_itemset_size'; Value='7'; Description='The maximum number of URLs/Pages for a path.'; output; Parameter_Name='wab_metadsn'; Value='&temp_lib..wasumeng'; Description='Summary Engine WASUMENG metadata which drives the creation ' || 'of the proc summary code to build the descriptive ' || 'summaries in the warehouse.'; output; Parameter_Name='wab_admsum'; Value='config.waadmsum'; Description='Summary Engine Administration WAADMSUM metadata which ' || 'is the source for the WASUMENG metadata.'; output; Parameter_Name='wab_days_in_history'; Value='185'; Description= 'The number of days available in daily summaries'; output; Parameter_Name='wab_weeks_in_history'; Value='52'; Description= 'The number of weeks available in weekly summaries'; output; Parameter_Name='wab_months_in_history'; Value='36'; Description= 'The number of months available in monthly summaries'; output; Parameter_Name='wab_quarters_in_history'; Value='12'; Description= 'The number of quarters available in quarterly summaries'; output; Parameter_Name='wab_years_in_history'; Value='3'; Description= 'The number of years available in yearly summaries'; output; Parameter_Name='wab_num_detail_data_sets'; Value='7'; Description= 'The number of separate date partitioned detail' ||' data sets to keep.'; output; Parameter_Name='wab_num_session_data_sets'; Value='30'; Description= 'The number of separate date partitioned session' ||' data sets to keep.'; output; Parameter_Name='wab_visitor_days_in_history'; Value='90'; Description= 'The number of days of history that is retained' ||' in SUMMARY.VISITOR_DAY.'; output; Parameter_Name='wab_pathing_days_in_history'; Value='30'; Description= 'The number of days of history that is retained' ||' in SUMMARY.PATHING.'; output; Parameter_Name='wab_autoseg_train_samp_pct'; Value='0.6'; Description= 'The proportion of total vistor ids' ||' used to build the automatic segmentation' ||' model.'; output; Parameter_Name='wab_autoseg_valid_samp_pct'; Value='0.4'; Description= 'The proportion of total vistor ids' ||' used to identify/validate the automatic segmentation' ||' model.'; output; Parameter_Name='wab_autoseg_num_segments'; Value='4'; Description= 'The number of segments generated by the automatic' ||' segmentation model.'; output; Parameter_Name='wab_autoseg_alpha'; Value='0.20'; Description= 'For automatic segmentation, alpha specifies a threshold' ||' p-value for the significance level of a candidate splitting' ||' rule. This only applicable for targets which have interval' ||' measurement level.'; output; Parameter_Name='wab_autoseg_minworth'; Value='0.0'; Description= 'For automatic segmentation, worth specifies a threshold' ||' value for the logworth of a candidate splitting rule' ||' This only applicable for targets which have nominal' ||' or ordinal measurement level.'; output; Parameter_Name='wab_autoseg_maxdepth'; Value='10'; Description= 'For automatic segmentation, depth is the' ||' number of splitting rules needed to define the node.' ||' The root node has depth zero. The children of the root' ||' node have depth one, and so on.'; output; Parameter_Name='wab_autoseg_leafsize'; Value=' '; Description= 'For automatic segmentation, leaf size is the' ||' smallest number of training observations a new branch may have.'; output; Parameter_Name='wab_init_by_sess_vars'; Value='status_code,requested_file'; Description= 'Comma-delimited list of INITIALIZE_BY_SESSION variables.'; output; Parameter_Name='wab_days_in_scorecard'; Value='365'; Description= 'The number of days available for Scorecards.'; output; Parameter_Name='wab_days_in_dashboard'; Value='365'; Description= 'The number of days available for Dashboards.'; output; Parameter_Name='wab_days_in_autoseg'; Value='365'; Description= 'The number of days available for Segmentations.'; output; Parameter_Name='wab_first_sess_vars'; Value=' '; Description= 'Comma-delimited list of SESSION vars with first.session_id' ||' values.'; output; Parameter_Name='wab_last_sess_vars'; Value=' '; Description= 'Comma-delimited list of SESSION vars with last.session_id' ||' values.'; output; Parameter_Name='wab_sum_sess_vars'; Value=' '; Description= 'Comma-delimited list of SESSION vars with sum of all ' ||' session_id values.'; output; Parameter_Name='wab_interactive_support'; Value='2'; Description= 'Minimum number of sessions per day for a path of' ||' requested files to be output in interactive pathing.'; output; Parameter_Name='week_start'; Value='Sunday'; Description= 'The English-language designation of 1st day in week.'; output; Parameter_Name='wab_max_path_pages'; Value='500'; Description='The maximum page count for a visit to be counted ' || 'in PROC Path analyses.'; output; Parameter_Name='wab_normalize_uri_url'; Value='0'; Description='The 0/1 indicator of whether to lowcase/compress ' || 'URI/URL information.'; output; Parameter_Name='wab_top_path_pages'; Value='2'; Description= 'Minimum number of sessions per day for a path of' ||' requested files to be in TOP_x_PATHS_HX data sets.'; output; run; data &def_lib..waconfig; set &def_lib..waconfig; seq+1; id=strip(put(seq,6.)); drop seq; run; proc datasets library=&def_lib nolist; modify waconfig; index create id / nomiss unique; quit; %end; %******************************************************************************; %* MODULE: Create WAAUTINP ; %* SUPPORT:saskxs ; %******************************************************************************; %else %if %upcase(&def_dsn) eq WAAUTINP %then %do; data &def_lib..waautinp; %wametatr(srce_dsn=waautinp ,retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Attributes cannot be assigned %QCMPRES( for) the variables in the &def_dsn data set.; stop; run; proc datasets library=&def_lib nolist; delete &def_dsn; quit; %goto ERREXIT; %end; id=id; %* avoid the "Variable id is uninitialized" log notice ; /* 1st Segment definition ------------------------------------------------ */ Segmentation_ID ='01'; role ='target'; level ='nominal'; _name_ ='repeat_visitor'; _label_ ='Repeat Visitor'; output; Segmentation_ID ='01'; role ='input'; level ='interval'; _name_ ='page_count_anl'; _label_ ='Page Count Analytic Period'; output; Segmentation_ID ='01'; role ='input'; level ='interval'; _name_ ='session_count_anl'; _label_ ='Visit Count Analytic Period'; output; Segmentation_ID ='01'; role ='input'; level ='interval'; _name_ ='duration_anl'; _label_ ='Duration Analytic Period'; output; Segmentation_ID ='01'; role ='new visitor indicator'; level ='nominal'; _name_ ='new_visitor'; _label_ ='New Visitor'; output; Segmentation_ID ='01'; role ='most current visitor'; level ='nominal'; _name_ ='rptdate_visitor'; _label_ ='Most Current Visitor'; output; Segmentation_ID ='01'; role ='visitor id'; level ='nominal'; _name_ ='visitor_id'; _label_ ='Visitor Id'; output; /* 2nd Segment definition ------------------------------------------------ */ Segmentation_ID ='02'; role ='target'; level ='nominal'; _name_ ='repeat_visitor'; _label_ ='Repeat Visitor'; output; Segmentation_ID ='02'; role ='input'; level ='interval'; _name_ ='page_count_avg_anl'; _label_ ='Page Count Analytic Period'; output; Segmentation_ID ='02'; role ='input'; level ='interval'; _name_ ='page_duration_avg_anl'; _label_ ='Avg Page Duration'; output; Segmentation_ID ='02'; role ='input'; level ='interval'; _name_ ='session_duration_avg_anl'; _label_ ='Avg Visit Duration'; output; Segmentation_ID ='02'; role ='new visitor indicator'; level ='nominal'; _name_ ='new_visitor'; _label_ ='New Visitor'; output; Segmentation_ID ='02'; role ='most current visitor'; level ='nominal'; _name_ ='rptdate_visitor'; _label_ ='Most Current Visitor'; output; Segmentation_ID ='02'; role ='visitor id'; level ='nominal'; _name_ ='visitor_id'; _label_ ='Visitor Id'; output; run; data &def_lib..waautinp; set &def_lib..waautinp; seq+1; id=strip(put(seq,6.)); drop seq; run; proc datasets library=&def_lib nolist; modify waautinp; index create id / nomiss unique; quit; %end; %******************************************************************************; %* MODULE: Create WAAUTOSG ; %* SUPPORT:saskxs ; %******************************************************************************; %else %if %upcase(&def_dsn) eq WAAUTOSG %then %do; data &def_lib..waautosg; %wametatr(srce_dsn=waautosg ,retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Attributes cannot be assigned %QCMPRES( for) the variables in the &def_dsn data set.; stop; run; proc datasets library=&def_lib nolist; delete &def_dsn; quit; %goto ERREXIT; %end; id ='01'; name ='Repeat Visitor - Totals'; indsn ='summary.unique_visitor_segmentation'; num_segments_to_create=4; data_prep_include_code='wauvisit.sas'; label ='Repeat Visitor - Totals'; etl_run_sw =1; output; id ='02'; name ='Repeat Visitor - Averages'; indsn ='summary.unique_visitor_segmentation'; num_segments_to_create=4; data_prep_include_code='wauvisit.sas'; label ='Repeat Visitor - Averages'; etl_run_sw =1; output; run; proc datasets library=&def_lib nolist; modify waautosg; index create id / nomiss unique; quit; %end; %******************************************************************************; %* MODULE: Create WARESRCE ; %* SUPPORT:sassyw ; %******************************************************************************; %else %if %upcase(&def_dsn) eq WARESRCE %then %do; data &def_lib..waresrce; %wametatr(srce_dsn=waresrce ,retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Attributes cannot be assigned %QCMPRES( for) the variables in the &def_dsn data set.; stop; run; proc datasets library=&def_lib nolist; delete &def_dsn; quit; %goto ERREXIT; %end; id ='dashboard_up_bg'; value ='#33cc00'; description='Dashboard positive performance background color'; output; id ='dashboard_down_bg'; value ='#ff0033'; description='Dashboard negative performance background color'; output; id ='dashboard_steady_bg'; value ='#0000ff'; description='Dashboard steady performance background color'; output; id ='dashboard_up_fg'; value ='#ffffff'; description='Dashboard positive performance foreground color'; output; id ='dashboard_down_fg'; value ='#ffffff'; description='Dashboard negative performance foreground color'; output; id ='dashboard_steady_fg'; value ='#ffffff'; description='Dashboard steady performance foreground color'; output; id ='dashboard_belowbad_image'; value ='../images/BelowBad.gif'; description='Dashboard trend down - desired trend up image icon'; output; id ='dashboard_belowgood_image'; value ='../images/BelowGood.gif'; description='Dashboard trend down - desired trend down image icon'; output; id ='dashboard_ok_image'; value ='../images/WithinLimits.gif'; description='Dashboard steady performance image icon'; output; id ='dashboard_abovegood_image'; value ='../images/AboveGood.gif'; description='Dashboard trend up - desired trend up image icon'; output; id ='dashboard_abovebad_image'; value ='../images/AboveBad.gif'; description='Dashboard trend up - desired trend down image icon'; output; id ='scorecard_belowbad_image'; value ='../images/BelowBad.gif'; description='Scorecard Down/Negative performance image icon'; output; id ='scorecard_belowgood_image'; value ='../images/BelowGood.gif'; description='Scorecard Down/Positive performance image icon'; output; id ='scorecard_ok_image'; value ='../images/WithinLimits.gif'; description='Scorecard steady performance image icon'; output; id ='scorecard_abovegood_image'; value ='../images/AboveGood.gif'; description='Scorecard Up/Positive performance image icon'; output; id ='scorecard_abovebad_image'; value ='../images/AboveBad.gif'; description='Scorecard Up/Negative performance image icon'; output; id ='row_threshold'; value ='5000'; description='# of Obs at which user is prompted to continue a report'; output; id ='funnel_sess_cutoff'; value ='3'; Description= 'The value of SESSION_HIT_COUNT for PAGE_FREQUENCIES obs' ||' that qualifies a URL for the interactive funnel pull-down.'; output; id ='label_wrap'; value ='40'; Description= 'The maximum label length to put into a table cell before' ||' wrapping. If set to 0, no wrapping is done.'; output; id ='node_bg'; value ='#c0c0c0'; description='The color to use for the node background in diagrams'; output; run; proc sort data=&def_lib..waresrce; by id; run; proc datasets library=&def_lib nolist; modify waresrce; index create id / nomiss unique; quit; %end; %******************************************************************************; %* MODULE: Create WATBLDSC ; %* SUPPORT:frroed ; %******************************************************************************; %else %if %upcase(&def_dsn) eq WATBLDSC %then %do; data &def_lib..watbldsc; %wametatr(srce_dsn=watbldsc ,retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Attributes cannot be assigned %QCMPRES( for) the variables in the &def_dsn data set.; stop; run; proc datasets library=&def_lib nolist; delete &def_dsn; quit; %goto ERREXIT; %end; id ='waconfig'; value ='Web Analytics Configuration'; description='Data that define and control Web Analytics settings and thresholds'; output; id ='waresrce'; value ='User Interface Settings'; description='Data that define and control the UI settings'; output; run; proc sort data=&def_lib..watbldsc; by id; run; proc datasets library=&def_lib nolist; modify watbldsc; index create id / nomiss unique; quit; %end; %******************************************************************************; %* MODULE: Create WAADMSUM ; %* SUPPORT:frroed ; %******************************************************************************; %else %if %upcase(&def_dsn) eq WAADMSUM %then %do; data waadmsum; %wametatr(srce_dsn=waadmsum ,retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Attributes cannot be assigned %QCMPRES( for) the variables in the &def_dsn data set.; stop; run; proc datasets library=&def_lib nolist; delete &def_dsn; quit; %goto ERREXIT; %end; id=id; %* avoid the "Variable id is uninitialized" log notice ; label ='Browser'; source_table ='session'; proc_summary_options ='sum,missing'; input_where =''; summary_level_list ='day,week,month,qtr,year'; class_var_list ='date,browser'; id_var_list =''; id_var_summary_level_list=''; analysis_var_list ='page_count,session_count'; output_dsn_options_0 =''; output_dsn_options_1 =''; output_dsn_where_stmt =''; output_options ='sum='; results_table ='browser'; etl_run_sw =1; output; label ='Browser Version'; source_table ='session'; proc_summary_options ='sum,missing'; input_where =''; summary_level_list ='day,week,month,qtr,year'; class_var_list ='date,browser,browser_version'; id_var_list =''; id_var_summary_level_list=''; analysis_var_list ='page_count,session_count'; output_dsn_options_0 =''; output_dsn_options_1 =''; output_dsn_where_stmt =''; output_options ='sum='; results_table ='browser_version'; etl_run_sw =1; output; label ='Daily Total'; source_table ='session'; proc_summary_options ='sum,missing'; input_where =''; summary_level_list ='day,week,month,qtr,year'; class_var_list ='date'; id_var_list ='dow_id'; id_var_summary_level_list='day'; analysis_var_list ='err_302_count,err_304_count,err_400_count,' || 'err_401_count,err_403_count,err_404_count,' || 'err_405_count,err_408_count,err_500_count,' || 'err_501_count,duration,file_count,page_count,' || 'one_hit_session_count,total_bytes_sent,' || 'session_count,page_view_le1_ind,' || 'page_view_2to4_ind,page_view_ge5_ind'; output_dsn_options_0 =''; output_dsn_options_1 =''; output_dsn_where_stmt =''; output_options ='sum='; results_table ='daily_total'; etl_run_sw =1; output; label ='Hourly Metrics'; source_table ='detail'; proc_summary_options ='sum,missing'; input_where =''; summary_level_list ='day,week,month,qtr,year'; class_var_list ='date,hour'; id_var_list =''; id_var_summary_level_list=''; analysis_var_list ='entry_point,file_count,page_count,bytes_sent'; output_dsn_options_0 =''; output_dsn_options_1 ='rename=(entry_point=session_count)'; output_dsn_where_stmt =''; output_options ='sum='; results_table ='hourly_metrics'; etl_run_sw =1; output; label ='Hourly Status'; source_table ='detail'; proc_summary_options ='sum,missing'; input_where =''; summary_level_list ='day,week,month,qtr,year'; class_var_list ='date,hour,status_code'; id_var_list =''; id_var_summary_level_list=''; analysis_var_list ='status_code_by_hour,file_count,page_count'; output_dsn_options_0 =''; output_dsn_options_1 ='rename=(status_code_by_hour=session_count)'; output_dsn_where_stmt =''; output_options ='sum='; results_table ='hourly_status'; etl_run_sw =1; output; label ='Page'; source_table ='detail'; proc_summary_options ='sum,missing'; input_where =''; summary_level_list ='day,week,month,qtr,year'; class_var_list ='date,requested_file'; id_var_list =''; id_var_summary_level_list=''; analysis_var_list ='init_by_sess_b,file_count,page_count,' || 'client_error_count,server_error_count,' || 'entry_page_count,exit_page_count,' || 'err_302_count,err_304_count,err_400_count,' || 'err_401_count,err_403_count,err_404_count,' || 'err_405_count,err_408_count,err_500_count,' || 'err_501_count '; output_dsn_options_0 =''; output_dsn_options_1 ='rename=(init_by_sess_b=session_count)'; output_dsn_where_stmt =''; output_options ='sum='; results_table ='page'; etl_run_sw =1; output; label ='Page Status'; source_table ='detail'; proc_summary_options ='sum,missing'; input_where =''; summary_level_list ='day,week,month,qtr,year'; class_var_list ='date,status_code,requested_file'; id_var_list =''; id_var_summary_level_list=''; analysis_var_list ='reqfile_statcde,file_count,page_count'; output_dsn_options_0 =''; output_dsn_options_1 ='rename=(reqfile_statcde=session_count)'; output_dsn_where_stmt =''; output_options ='sum='; results_table ='page_status'; etl_run_sw =1; output; label ='Platform'; source_table ='session'; proc_summary_options ='sum,missing'; input_where =''; summary_level_list ='day,week,month,qtr,year'; class_var_list ='date,platform'; id_var_list =''; id_var_summary_level_list=''; analysis_var_list ='page_count,session_count'; output_dsn_options_0 =''; output_dsn_options_1 =''; output_dsn_where_stmt =''; output_options ='sum='; results_table ='platform'; etl_run_sw =1; output; label ='Referrer Domain'; source_table ='session'; proc_summary_options ='sum,missing'; input_where =''; summary_level_list ='day,week,month,qtr,year'; class_var_list ='date,referrer_domain'; id_var_list =''; id_var_summary_level_list=''; analysis_var_list ='page_count,session_count'; output_dsn_options_0 =''; output_dsn_options_1 =''; output_dsn_where_stmt ='where=(referrer_domain ne " ")'; output_options ='sum='; results_table ='referrer_domain'; etl_run_sw =1; output; label ='Referrer Entry Point'; source_table ='session'; proc_summary_options ='sum,missing'; input_where =''; summary_level_list ='day,week,month,qtr,year'; class_var_list ='date,referrer_domain,first_requested_file'; id_var_list =''; id_var_summary_level_list=''; analysis_var_list ='session_count'; output_dsn_options_0 =''; output_dsn_options_1 =''; output_dsn_where_stmt ='where=(referrer_domain ne " ")'; output_options ='sum='; results_table ='referrer_entry_point'; etl_run_sw =1; output; label ='Referrer Page Status'; source_table ='detail'; proc_summary_options ='sum,missing'; input_where =''; summary_level_list ='day,week,month,qtr,year'; class_var_list ='date,status_code,referrer,requested_file'; id_var_list =''; id_var_summary_level_list=''; analysis_var_list ='reqfile_statcde,file_count,page_count'; output_dsn_options_0 =''; output_dsn_options_1 ='rename=(reqfile_statcde=session_count)'; output_dsn_where_stmt =''; output_options ='sum='; results_table ='referrer_page_status'; etl_run_sw =1; output; label ='Referrer Search Term'; source_table ='session'; proc_summary_options ='sum'; input_where =''; summary_level_list ='day,week,month,qtr,year'; class_var_list ='date,referrer_domain,search_term'; id_var_list =''; id_var_summary_level_list=''; analysis_var_list ='session_count'; output_dsn_options_0 =''; output_dsn_options_1 =''; output_dsn_where_stmt =''; output_options ='sum='; results_table ='referrer_search_term'; etl_run_sw =1; output; label ='Search Term'; source_table ='session'; proc_summary_options ='sum'; input_where =''; summary_level_list ='day,week,month,qtr,year'; class_var_list ='date,search_term'; id_var_list =''; id_var_summary_level_list=''; analysis_var_list ='session_count'; output_dsn_options_0 =''; output_dsn_options_1 =''; output_dsn_where_stmt =''; output_options ='sum='; results_table ='search_term'; etl_run_sw =1; output; label ='Status'; source_table ='detail'; proc_summary_options ='sum,missing'; input_where =''; summary_level_list ='day,week,month,qtr,year'; class_var_list ='date,status_code'; id_var_list =''; id_var_summary_level_list=''; analysis_var_list ='init_by_sess_a,file_count,page_count'; output_dsn_options_0 =''; output_dsn_options_1 ='rename=(init_by_sess_a=session_count)'; output_dsn_where_stmt =''; output_options ='sum='; results_table ='status'; etl_run_sw =1; output; label ='Visitor'; source_table ='session'; proc_summary_options ='missing'; input_where =''; summary_level_list ='day,week,month,qtr,year'; class_var_list ='visitor_id,date'; id_var_list =''; id_var_summary_level_list=''; analysis_var_list ='beg_datetime,end_datetime,page_count,' || 'session_count,duration,page_view_le1_ind,' || 'page_view_2to4_ind,page_view_ge5_ind'; output_dsn_options_0 ='keep=visitor_id' || ' date min_beg max_end page_count' || ' session_count duration page_view_le1_ind' || ' page_view_2to4_ind page_view_ge5_ind _type_' || ' drop=beg_datetime end_datetime' || ' rename=(min_beg=beg_datetime max_end=' || 'end_datetime)'; output_dsn_options_1 ='keep=visitor_id date min_beg max_end page_count' || ' session_count duration page_view_le1_ind' || ' page_view_2to4_ind page_view_ge5_ind _type_' || ' drop=beg_datetime end_datetime' || ' rename=(min_beg=beg_datetime' || ' max_end=end_datetime)'; output_dsn_where_stmt =''; output_options ='sum= min(beg_datetime)=min_beg max(end_datetime)' || '=max_end'; results_table ='visitor'; etl_run_sw =1; output; run; data &def_lib..waadmsum; set waadmsum; seq+1; id=strip(put(seq,6.)); drop seq; run; proc sort data=&def_lib..waadmsum; by id; run; proc datasets library=&def_lib nolist; modify waadmsum; index create id / nomiss unique; quit; %end; %******************************************************************************; %* MODULE: Create WAENGVAR ; %* SUPPORT:frroed ; %******************************************************************************; %else %if %upcase(&def_dsn) eq WAENGVAR %then %do; proc sql; create table mdat_det as select libname, memname, name, label from dictionary.columns where libname eq upcase("&det_lib") and index(memname,'DETAIL_'); quit; proc sort data=mdat_det out =uniq_det nodupkeys; by name; run; proc sql; create table mdat_ses as select libname, memname, name, label from dictionary.columns where libname eq upcase("&ses_lib") and index(memname,'SESSION_'); quit; proc sort data=mdat_ses out =uniq_ses nodupkeys; by name; run; data waengvar; %wametatr(srce_dsn=waengvar ,retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Attributes cannot be assigned %QCMPRES( for) the variables in the &def_dsn data set.; stop; run; proc datasets library=&def_lib nolist; delete &def_dsn; quit; %goto ERREXIT; %end; set uniq_ses (in=in_a) uniq_det (in=in_b); id=id; if in_a then do; libname='DATED'; memname='SESSION_yyyymmdd'; end; else do; libname='DETAIL'; memname='DETAIL_yyyymmdd'; end; run; data &def_lib..waengvar; set waengvar; seq+1; id=put(seq,6.); drop seq; run; proc sort data=&def_lib..waengvar; by id; run; proc datasets library=&def_lib nolist; modify waengvar; index create id / nomiss unique; quit; %end; %******************************************************************************; %* MODULE: Create WAFUNNEL ; %* SUPPORT:frroed ; %******************************************************************************; %else %if %upcase(&def_dsn) eq WAFUNNEL %then %do; data wafunnel; %wametatr(srce_dsn=wafunnel ,retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Attributes cannot be assigned %QCMPRES( for) the variables in the &def_dsn data set.; stop; run; proc datasets library=&def_lib nolist; delete &def_dsn; quit; %goto ERREXIT; %end; stop; run; data &def_lib..wafunnel; set wafunnel; seq+1; id=put(seq,6.); drop seq; run; proc sort data=&def_lib..wafunnel; by id; run; proc datasets library=&def_lib nolist; modify wafunnel; index create id / nomiss unique; quit; %end; %******************************************************************************; %* MODULE: Create WAPATH ; %* SUPPORT:frroed ; %******************************************************************************; %else %if %upcase(&def_dsn) eq WAPATH %then %do; data wapath; %wametatr(srce_dsn=wapath ,retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Attributes cannot be assigned %QCMPRES( for) the variables in the &def_dsn data set.; stop; run; proc datasets library=&def_lib nolist; delete &def_dsn; quit; %goto ERREXIT; %end; stop; run; data &def_lib..wapath; set wapath; seq+1; id=put(seq,6.); drop seq; run; proc sort data=&def_lib..wapath; by id; run; proc datasets library=&def_lib nolist; modify wapath; index create id / nomiss unique; quit; %end; %******************************************************************************; %* MODULE: Create WAVISMET ; %* SUPPORT:frroed ; %* NOTE: WAVISMET, unlike the other data sets that can be created by the ; %* WAMETDEF macro, is only created in WORK. It is not created in CONFIG ; %* and it cannot be customized via the Administrator UI. ; %* NOTE: with the re-design of the Summary Engine for S0351214, the value ; %* for PARTITION_BY_DATE was changed from 0 to 1 so that the generated ; %* code would be for Initial Summarizations, not for Re-Summarizations. ; %* With S0351214, the Re-Summarization code is no longer PROC Summary. ; %******************************************************************************; %else %if %upcase(&def_dsn) eq WAVISMET %then %do; data wavismet; %wametatr(srce_dsn=wasumeng ,retcode=wab_rc ); %if &wab_rc ne 0 %then %do; %put %unquote(&wab_error) Attributes cannot be assigned %QCMPRES( for) the variables in the &def_dsn data set.; stop; run; proc datasets library=work nolist; delete &def_dsn; quit; %goto ERREXIT; %end; id=id; %* avoid the "Variable id is uninitialized" log notice ; input_table='&temp_lib..visitor_day'; summary_stmt_opts='sum missing chartype'; where_stmt=' '; partition_by_date=1; id_vars=' '; class_vars='date'; analysis_vars='unique_visitor,repeat_visitor'; freq_vars=' '; weight_vars=' '; output_table='&temp_lib..daily_visitor_day'; summary_outdsn_opts=' '; summary_outdsn_where=' '; summary_output_opts='sum= '; output; input_table='&temp_lib..visitor_week'; summary_stmt_opts='sum missing chartype'; where_stmt=' '; partition_by_date=1; id_vars=' '; class_vars='date'; analysis_vars='unique_visitor,repeat_visitor'; freq_vars=' '; weight_vars=' '; output_table='&temp_lib..daily_visitor_week'; summary_outdsn_opts=' '; summary_outdsn_where=' '; summary_output_opts='sum= '; output; input_table='&temp_lib..visitor_month'; summary_stmt_opts='sum missing chartype'; where_stmt=' '; partition_by_date=1; id_vars=' '; class_vars='date'; analysis_vars='unique_visitor,repeat_visitor'; freq_vars=' '; weight_vars=' '; output_table='&temp_lib..daily_visitor_month'; summary_outdsn_opts=' '; summary_outdsn_where=' '; summary_output_opts='sum= '; output; input_table='&temp_lib..visitor_qtr'; summary_stmt_opts='sum missing chartype'; where_stmt=' '; partition_by_date=1; id_vars=' '; class_vars='date'; analysis_vars='unique_visitor,repeat_visitor'; freq_vars=' '; weight_vars=' '; output_table='&temp_lib..daily_visitor_qtr'; summary_outdsn_opts=' '; summary_outdsn_where=' '; summary_output_opts='sum= '; output; input_table='&temp_lib..visitor_year'; summary_stmt_opts='sum missing chartype'; where_stmt=' '; partition_by_date=1; id_vars=' '; class_vars='date'; analysis_vars='unique_visitor,repeat_visitor'; freq_vars=' '; weight_vars=' '; output_table='&temp_lib..daily_visitor_year'; summary_outdsn_opts=' '; summary_outdsn_where=' '; summary_output_opts='sum= '; output; run; %end; %else %do; %put %unquote(&wab_error) %upcase(&def_dsn) is not %QCMPRES(a standard) Web Analytics Metadata Data Set.; %let &retcode=1; %goto ERREXIT; %end; %goto EXIT; %ERREXIT:; %put %unquote(&wab_error) WAMETDEF exiting due to error.; %EXIT:; %mend wametdef; /*----------------------Web Analytics-----------------------------------------*/ /* WASASDIR */ /* Create the standard set of members in the \sas sub-directory */ /* NOTE: each module is created only if it does not exist. No extant */ /* modules will be replaced. */ /* */ /*----------------------------------------------------------------------------*/ /* Supported By: Frank Roediger, FRROED */ /* */ /*----------------------------------------------------------------------------*/ /* History: */ /* Date Description Username Change Code */ /* 20041223 Program Created frroed NA */ /* */ /*----------------------------------------------------------------------------*/ /* Dependencies: */ /* The following macro variables must be defined in the invoking */ /* environment: */ /* -- &SWAMART */ /* -- &SEPARATOR */ /* The path identified by the SWAMART macro variable must have a \sas */ /* sub-directory */ /* */ /*----------------------------------------------------------------------------*/ /* Parameters: */ /* RETCODE: the mechanism for indicating whether the macro ran */ /* successfully */ /* */ /*----------------------------------------------------------------------------*/ /* Input: */ /* (retcode= */ /* ); */ /* */ /*----------------------------------------------------------------------------*/ /* Output: */ /* */ /*----------------------------------------------------------------------------*/ /* Copyright (c) 2004 SAS Institute Inc. All Rights Reserved */ /*----------------------------------------------------------------------------*/ %macro wasasdir(retcode= ); %***************************************************************************; %* DAILY.SAS ; %***************************************************************************; %let wasaspgm=daily.sas; %if not %sysfunc(fileexist(&swamart.&separator.sas&separator.&wasaspgm)) %then %do; data _null_; file "&swamart.&separator.sas&separator.&wasaspgm"; put '/** ' / ' * Use this program to process weblog data into the SAS Web Analytics warehouse. ' / ' * ' / ' * IMPORTANT: You must supply the location of the weblogs to process. ' / ' * Either ' / ' * A. Specify the location in the WEBLOGS macro variable below by replacing ' / ' * with the location of your weblogs. WEBLOGS can ' / ' * be the full path either for a single weblog or for a directory that ' / ' * contains multiple weblogs. ' / ' * Or ' / ' * B. Launch the e-Data ETL administrative GUI from an interactive SAS session' / ' * using the "edataetl" command and specify the location of your weblog(s) ' / ' * in the appropriate field. ' / ' * If you use method B. then you can remove the "weblog_path=&weblogs" parameter ' / ' * from the %edataetl call below. If you leave the parameter in the edataetl ' / ' * call, it will take precedence over the Weblog location information you have ' / ' * entered via the e-Data ETL Administrative GUI. ' / ' */ ' // '%let wbrc=0; ' // '%MACRO SWA_ETL; ' // '*** TODO: specify the location of your weblogs; ' / '%let WEBLOGS=; ' // '%let webmart=' "&swamart" ';' // '/* Extract raw weblog data into staging datasets. */ ' / '%edataetl( data_store = &webmart' "&separator." 'e-data-etl, ' / ' weblog_path = &weblogs, ' / ' program = extract ' / ' ); ' // '/* Load web data into final detail dataset. */ ' / '%edataetl( data_store = &webmart' "&separator." 'e-data-etl, ' / ' program = load ' / ' ); ' // '/* Load web detail data into warehouse. */ ' / '%if &wbrc = 0 %then %do; ' // ' %waetl( swamart = &webmart, ' / ' program = warehouse ' / ' ); ' // '%end; ' // '%MEND SWA_ETL; ' / '%SWA_ETL; ' ; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_warning) &wasaspgm %QCMPRES( for) &swamart could not be created in the SAS %QCMPRES( subdirectory) for &swamart.; %let &retcode=1; %end; %end; %***************************************************************************; %* SETUP_LIBS.SAS ; %***************************************************************************; %let wasaspgm=setup_libs.sas; %if not %sysfunc(fileexist(&swamart.&separator.sas&separator.&wasaspgm)) %then %do; data _null_; file "&swamart.&separator.sas&separator.&wasaspgm"; put '/** ' / ' * This is a convience program that can be included in an interactive ' / ' * SAS session or batch sas program. It will define all of the appropriate' / ' * libraries for your Web mart. ' / ' */ ' // 'options '@ ; if upcase("&sysscp") in ("WIN","DNTHOST") then put 'xsync noxwait ' @; put 'nofmterr compress=yes; ' // '%let webmart=' "&swamart" ';' // 'libname detail "&webmart' "&separator.e-data-etl&separator." 'detail";' / 'libname wadetail "&webmart' "&separator.data&separator." 'detail";' / 'libname dated "&webmart' "&separator.data&separator." 'dated";' / 'libname document "&webmart' "&separator.data&separator." 'documents";' / 'libname summary "&webmart' "&separator.data&separator." 'summary";' / 'libname config "&webmart' "&separator.config" '";' ; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) &wasaspgm for %QCMPRES( &swamart) could not be created in the SAS %QCMPRES( subdirectory) for &swamart.; %let &retcode=1; %end; %end; %***************************************************************************; %* STP_WADESIGN.SAS ; %***************************************************************************; %let wasaspgm=stp_wadesign.sas; %if not %sysfunc(fileexist(&swamart.&separator.sas&separator.&wasaspgm)) %then %do; data _null_; file "&swamart.&separator.sas&separator.&wasaspgm"; put '%global output_dataset;'; put '*ProcessBody;'; put 'options compress=yes;'; put '%wadesign;'; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) &wasaspgm for %QCMPRES( &swamart) could not be created in the SAS %QCMPRES( subdirectory) for &swamart.; %let &retcode=1; %end; %end; %***************************************************************************; %* STP_WAFUNNEL.SAS ; %***************************************************************************; %let wasaspgm=stp_wafunnel.sas; %if not %sysfunc(fileexist(&swamart.&separator.sas&separator.&wasaspgm)) %then %do; data _null_; file "&swamart.&separator.sas&separator.&wasaspgm"; put '%global output_dataset;'; put '*ProcessBody;'; put 'options compress=yes;'; put '%wafunnel;'; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) &wasaspgm for %QCMPRES( &swamart) could not be created in the SAS %QCMPRES( subdirectory) for &swamart.; %let &retcode=1; %end; %end; %***************************************************************************; %* STP_WAPATH.SAS ; %***************************************************************************; %let wasaspgm=stp_wapath.sas; %if not %sysfunc(fileexist(&swamart.&separator.sas&separator.&wasaspgm)) %then %do; data _null_; file "&swamart.&separator.sas&separator.&wasaspgm"; put '%global output_dataset;'; put '*ProcessBody;'; put 'options compress=yes;'; put '%wapath;'; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) &wasaspgm for %QCMPRES( &swamart) could not be created in the SAS %QCMPRES( subdirectory) for &swamart.; %let &retcode=1; %end; %end; %***************************************************************************; %* STP_WASTATUS.SAS ; %***************************************************************************; %let wasaspgm=stp_wastatus.sas; %if not %sysfunc(fileexist(&swamart.&separator.sas&separator.&wasaspgm)) %then %do; data _null_; file "&swamart.&separator.sas&separator.&wasaspgm"; put '*ProcessBody;'; put 'options compress=yes;'; put '%let wab_rc=0;'; put '%wastatus(swamart=&swamart'; put ' ,waetl_sw=N'; put ' ,stp_sw=yes'; put ' ,retcode=wab_rc'; put ' );'; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) &wasaspgm for %QCMPRES( &swamart) could not be created in the SAS %QCMPRES( subdirectory) for &swamart.; %let &retcode=1; %end; %end; %***************************************************************************; %* WAUVISIT.SAS ; %***************************************************************************; %let wasaspgm=wauvisit.sas; %if not %sysfunc(fileexist(&swamart.&separator.sas&separator.&wasaspgm)) %then %do; data _null_; file "&swamart.&separator.sas&separator.&wasaspgm"; put '/**********************************************************************************' / '* This macro creates the input data set for the default segmentation analyses.' / '**********************************************************************************/' / ' %wauvisit;' ; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) &wasaspgm for %QCMPRES( &swamart) could not be created in the SAS %QCMPRES( subdirectory) for &swamart.; %let &retcode=1; %end; %end; %***************************************************************************; %* STP_TEMPLATE.SAS ; %***************************************************************************; %let wasaspgm=stp_template.sas; %if not %sysfunc(fileexist(&swamart.&separator.sas&separator.&wasaspgm)) %then %do; data _null_; file "&swamart.&separator.sas&separator.&wasaspgm"; put '/*----------------------------------------------------------------------------*/ ' / '/* Store Process Template */ ' / '/* */ ' / '/* */ ' / '/* Dependencies: */ ' / '/* The following macros need to be available: */ ' / '/* -- none -- */ ' / '/* */ ' / '/* The following macro variables need to be defined and assigned values */ ' / '/* by the User Interface/Middleware */ ' / '/* &START: a comma-delimited list of the YYYYMMDD values that are the */ ' / '/* dates for the analysis. */ ' / '/* &END: a comma-delimited list of the YYYYMMDD values that are the */ ' / '/* dates for the analysis. */ ' / '/* &LIBREFS: a comma-delimited list of the SAS libnames that need to */ ' / '/* be defined by the process. */ ' / '/* &LIBPATHS: a comma-delimited list of the locations for the */ ' / '/* corresponding libnames in the LIBREFS list. */ ' / '/* &INFO: true or false, if true then need to create a template */ ' / '/* version of the table */ ' / '/* Add additional parameters here - example, page or search term */ ' / '/* */ ' / '/* Save Location: need to save completed program in &swamart/sas */ ' / '/*----------------------------------------------------------------------------*/ ' / ' ' / '/* change name of macro */ ' / '%macro STP_TEMPLATE; ' / ' ' / ' %global output_dataset; ' / ' options mtrace mprint; ' / ' /*-------------------------------------------------------------------------------* ' / ' * DO NOT CHANGE THE FOLLOWING SECTION - REQUIRED SECTION I ' / ' *--------------------------------------------------------------------------------*/' / ' %global output_dataset; ' / ' options mtrace mprint symbolgen; ' / ' ' / ' %* parse the LIBREFS and LIBPATHS strings and assign necessary librefs ; ' / ' data _null_; ' / ' length tmprefs $ 1024 ' / ' tmppaths $ 4096; ' / ' ' / ' tmprefs="&librefs"; ' / ' tmppaths="&libpaths"; ' / ' ' / ' %* make sure that both lists have the same number of elements ; ' / ' num_ref_commas=countc(tmprefs,","); ' / ' num_path_commas=countc(tmppaths,","); ' / ' ' / ' if num_ref_commas eq num_path_commas then do; ' / ' do i=1 to (1+num_ref_commas); ' / ' libname=scan(tmprefs,i,","); ' / ' libpath=strip(scan(tmppaths,i,",")); ' / ' rc = filename("fileref",libpath); ' / ' did = dopen("fileref"); ' / ' if did then do; ' / ' rc=dclose(did); ' / ' rc = libname(strip(libname), libpath); ' / ' call symput("_wab_lib_error_","0"); ' / ' end; ' / ' else do; ' / ' call symput("_wab_lib_error_","2"); ' / ' call symput("_wab_lib_msg_" ' / ' ,"The physical location "||strip(libpath) ' / ' ||" either does not exist or cannot be " ' / ' ||"opened. The library "||strip(libname) ' / ' ||" must be respecified with a valid location."); ' / ' end; ' / ' end; ' / ' end; ' / ' else do; ' / ' call symput("_wab_lib_error_","3"); ' / ' call symput("_wab_lib_msg_","You have different numbers of " ' / ' ||"library references than physical paths." ' / ' ||"Please respecify your libraries."); ' / ' end; ' / ' ' / ' run; ' / ' ' / ' options noquotelenmax; /* avoid warning message about unbalanced quotes */ ' / ' data _null_; ' / ' start=compress("&start"); ' / ' if start in ("","$start") then call symput("startchk","N"); ' / ' else call symput("startchk","Y"); ' / ' run; ' / ' ' / ' %if &startchk = Y %then %do; ' / ' data _null_; ' / ' start="&start"; ' / ' end="&end"; ' / ' start_date=input(start,yymmdd10.); ' / ' end_date=input(end,yymmdd10.); ' / ' ' / ' i = 0; ' / ' do date = start_date to end_date; ' / ' i=i+1; ' / ' call symput(compress("date"||put(i,3.)),compress(put(date,yymmdd10.),"- ")); ' / ' if date = end_date then call symput("d",compress(put(i,best.))); ' / ' end; ' / ' run; ' / ' %end; ' / ' ' / ' /*-----------------------------------------------------------------* ' / ' * The stored process handler with SWA will first read a ' / ' * "template" of a STP data set and then actually create the data. ' / ' * When the STP is used to create a report only ' / ' *-----------------------------------------------------------------*/ ' / ' data _null_; ' / ' if strip(lowcase(compress("&info")))="true" then ' / ' call symput("info","TRUE"); ' / ' else ' / ' call symput("info","FALSE"); ' / ' run; ' / ' ' / ' /*-------------------------------------------------------------------------------* ' / ' * END REQUIRED SECTION - I ' / ' *--------------------------------------------------------------------------------*/' / ' ' / ' /*-------------------------------------------------------------------------------* ' / ' * CREATE TABLE TEMPLATE: REQUIRED SECTION - II ' / ' * Need to create a 0 observation data set that contains all of the variables in ' / ' * the final data set. Need to include formatting and labels - these will be ' / ' * used when a report is created from a STP. ' / ' * !!! Any place XXXXXX occurs this means a STP specific name is required !!!!! ' / ' *--------------------------------------------------------------------------------*/' / ' %if &info = TRUE %then %do; ' / ' ' / ' data _null_; ' / ' %* create a unique id for the dsn that will contain the funnel template ; ' / ' call symput("_XXXXXX_data_uid_" ' / ' ,compress( ' / ' translate( strip(put(datetime(),15.)) ' / ' || "_" ' / ' || strip(put(ranuni(-1),13.11)) ' / ' ," " ' / ' ,"." ' / ' ) ' / ' ) ' / ' ); ' / ' run; ' / ' ' / ' /*-------------------------------------------------------------------* ' / ' * ZZZZZZ - - prefix appropriate for the data set between 1 and 6 ' / ' * characters the unique id will take up the rest of the 32 chars ' / ' *--------------------------------------------------------------------*/ ' / ' data stp.ZZZZZZ_&_XXXXXX_data_uid_; ' / ' format /* Fill in with columns created in STP */ ' / ' ; ' / ' label /* Fill in column labels */ ' / ' ; ' / ' run; ' / ' ' / ' %let outdsn=stp.ZZZZZZ_&_XXXXXX_data_uid_; ' / ' ' / ' %goto done; ' / ' %end; ' / ' /*-------------------------------------------------------------------------------* ' / ' * END TABLE TEMPLATE: REQUIRED SECTION - II ' / ' *--------------------------------------------------------------------------------*/' / ' ' / ' /*-------------------------------------------------------------------------------* ' / ' * CREATE TABLE: REQUIRED SECTION - III ' / ' * !!! Any place XXXXXX or ZZZZZZ occurs a STP-specific name is required !!!!! ' / ' *--------------------------------------------------------------------------------*/' / ' data _null_; ' / ' %* create a unique id for the dsn that will contain the funnel template ; ' / ' %* replace XXXXXXX with text appropriate for the STP ; ' / ' call symput("_XXXXXX_data_uid_" ' / ' ,compress( ' / ' translate( strip(put(datetime(),15.)) ' / ' || "_" ' / ' || strip(put(ranuni(-1),13.11)) ' / ' ," " ' / ' ,"." ' / ' ) ' / ' ) ' / ' ); ' / ' run; ' / ' ' / ' /*------------------------------------------------------------------* ' / ' * Put custom code here - all data sets created MUST be stored in ' / ' * WORK. Data sets should be named as follows ' / ' * ZZZZZZ - prefix appropriate for the data set beween 1 and 6 ' / ' * characters the unique id will take up the rest of the 32 chars ' / ' * Data set names MUST have unique identifier or multiple users can ' / ' * tromp on each other. ' / ' *------------------------------------------------------------------*/ ' / ' ' / ' /*------------------------------------------------------------------* ' / ' * Start data check logic: Put logic here to determine if data exists ' / ' * for dates selected if not then need to create output data set with ' / ' * zero observations. This section is required because the calendar ' / ' * will allow selection of dates with no data ' / ' *------------------------------------------------------------------*/ ' / ' ' / ' /*------------------------------------------------------------------* ' / ' * End data check logic: ' / ' *------------------------------------------------------------------*/ ' / ' ' / ' /*------------------------------------------------------------------* ' / ' * Start report logic ' / ' *------------------------------------------------------------------*/ ' / ' ' / ' /*------------------------------------------------------------------* ' / ' * End report logic ' / ' *------------------------------------------------------------------*/ ' / ' ' / ' /*------------------------------------------------------------------* ' / ' * Final data set creation - make sure that all variables have ' / ' * appropriate formats and labels. ' / ' *------------------------------------------------------------------*/ ' / ' data stp.ZZZZZZ_&_XXXXXX_data_uid_; ' / ' format /* Fill in with columns created in STP */ ' / ' ; ' / ' set work.ZZZZZZ_&_XXXXXX_data_uid_ ' / ' ; ' / ' label /* Fill in column labels */ ' / ' ; ' / ' run; ' / ' ' / ' %let outdsn=stp.ZZZZZZ_&_XXXXXX_data_uid_; ' / ' ' / ' /*------------------------------------------------------------------* ' / ' * End Final data set creation ' / ' *------------------------------------------------------------------*/ ' / ' ' / ' /*------------------------------------------------------------------* ' / ' * Start data set clean-up ' / ' *------------------------------------------------------------------*/ ' / ' %DONE:; ' / ' %if %upcase(&info) ne TRUE %then %do; ' / ' ' / ' proc datasets library=work nolist; ' / ' delete ' / ' ' / ' /* Need to clean up work data sets */ ' / ' ' / ' ; ' / ' quit; ' / ' ' / ' proc datasets library=STP nolist; ' / ' delete ' / ' ' / ' ' / ' /* Need to clean up STP data set */ ' / ' ' / ' ' / ' ; ' / ' quit; ' / ' ' / ' /*------------------------------------------------------------------* ' / ' * End data set clean-up ' / ' *------------------------------------------------------------------*/ ' / ' %end; ' / ' ' / ' ' / ' %put *****OUTPUT PARAMETERS*****; ' / ' %put stp:output_dataset=&outdsn; ' / ' %let output_dataset=&outdsn; ' / ' ' / '%mend; ' / '/* Put macro call here and uncomment. ' / '% ' / '*/ ' ; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) &wasaspgm for %QCMPRES( &swamart) could not be created in the SAS %QCMPRES( subdirectory) for &swamart.; %let &retcode=1; %end; %end; %mend wasasdir; /*----------------------Web Analytics-----------------------------------------*/ /* WALISTS */ /* Define and create the Web Analytics UI Selection List data sets */ /* */ /*----------------------------------------------------------------------------*/ /* Supported By: Frank Roediger, FRROED */ /* */ /*----------------------------------------------------------------------------*/ /* History: */ /* Date Description Username Change Code */ /* 20050111 Program Created frroed NA */ /* */ /*----------------------------------------------------------------------------*/ /* Dependencies: */ /* -- none */ /* */ /*----------------------------------------------------------------------------*/ /* Parameters: */ /* RETCODE: the mechanism for indicating whether the macro ran */ /* successfully */ /* */ /*----------------------------------------------------------------------------*/ /* Input: */ /* (retcode= */ /* ); */ /* */ /*----------------------------------------------------------------------------*/ /* Output: */ /* */ /*----------------------------------------------------------------------------*/ /* Copyright (c) 2005 SAS Institute Inc. All Rights Reserved */ /*----------------------------------------------------------------------------*/ %macro walists(retcode= ); %***************************************************************************; %* use the _YEAR data sets to get the most comprehensive list ; %***************************************************************************; %* BROWSER ; %***************************************************************************; proc summary data=summary.browser_year chartype n; class browser; output out=&temp_lib..list_browser (where=(_type_ eq '1')); run; proc sort data=&temp_lib..list_browser (keep=browser) out =summary.list_browser; by browser; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) PROC SORT for the updated %QCMPRES( SUMMARY.LIST_BROWSER) data set failed.; %goto ERREXIT; %end; %***************************************************************************; %* PLATFORM ; %***************************************************************************; proc summary data=summary.platform_year chartype n; class platform; output out=&temp_lib..list_platform (where=(_type_ eq '1')); run; proc sort data=&temp_lib..list_platform (keep=platform) out =summary.list_platform; by platform; run; %if &syserr gt 4 %then %do; %put %unquote(&wab_error) PROC SORT for the updated %QCMPRES( SUMMARY.LIST_PLATFORM) data set failed.; %goto ERREXIT; %end; %goto EXIT; %ERREXIT: %let &retcode=1; %put %unquote(&wab_error) WALISTS exiting due to error.; %EXIT: %put %unquote(&wab_note) WALISTS completed.; %mend walists; /*----------------------Web Analytics-----------------------------------------*/ /* CVT_FMTS */ /* Convert COMMAw.d and w.d formats in SUMMARY data sets to NLNUMw.d formats */ /* */ /*----------------------------------------------------------------------------*/ /* Supported By: Frank Roediger, FRROED */ /* */ /*----------------------------------------------------------------------------*/ /* History: */ /* Date Description Username Change Code */ /* 20050125 Program Created frroed NA */ /* */ /*----------------------------------------------------------------------------*/ /* Dependencies: */ /* -- none */ /* */ /*----------------------------------------------------------------------------*/ /* Parameters: */ /* LIBR: the library for the data set with variables whose formats are to */ /* be converted */ /* DSET: the &LIBR data set with variables whose formats are to be */ /* converted */ /* FMTS: the FORMAT statement that converts the old formats */ /* */ /*----------------------------------------------------------------------------*/ /* Input: */ /* (libr=summary */ /* ,dset= */ /* ,fmts= */ /* ); */ /* */ /*----------------------------------------------------------------------------*/ /* Output: */ /* &LIBR..&dset is updated in-place with &FMTS replacing existing formats */ /* */ /*----------------------------------------------------------------------------*/ /* Copyright (c) 2005 SAS Institute Inc. All Rights Reserved */ /*----------------------------------------------------------------------------*/ %macro cvt_fmts(libr=summary ,dset= ,fmts= ); proc datasets library=&libr nolist; modify &dset; &fmts;; quit; %mend cvt_fmts; /*----------------------Web Analytics-----------------------------------------*/ /* Create_Session */ /* Create the SWA Session data set. */ /* */ /*----------------------------------------------------------------------------*/ /* Supported By: Frank Roediger, FRROED */ /* */ /*----------------------------------------------------------------------------*/ /* History: */ /* Date Description Username Change Code */ /* 20050428 Program Created frroed NA */ /* 20060328 S0351214 frroed */ /* */ /*----------------------------------------------------------------------------*/ /* Dependencies: */ /* The following data sets need to be available: */ /* none */ /* */ /* The following macros need to be available: */ /* none */ /* */ /* The following macro variables need to be defined and assigned values */ /* in the invoking environment: */ /* &temp_lib */ /* */ /*----------------------------------------------------------------------------*/ /* Parameters: */ /* none */ /* */ /*----------------------------------------------------------------------------*/ /* Input: */ /* none */ /* */ /*----------------------------------------------------------------------------*/ /* Output: */ /* */ /* */ /*----------------------------------------------------------------------------*/ /* Copyright (c) 2005 SAS Institute Inc. All Rights Reserved */ /*----------------------------------------------------------------------------*/ %macro Create_Session(session_sffx= ,session_iter= ,retcode= ); %let &retcode=0; %***************************************************************************; %* create SESS_VARS to inventory the SESSION_ID-related special variables ; %* and the FSVn (First Session Variable), LSVn (Last Session Variable), ; %* and SSVn (Summary Session Variable) macro variables. ; %***************************************************************************; data sess_vars; length varname name var_cat $ 32 varlist $ 4096; varlist="&wab_first_sess_vars"; varseq=0; do while (varlist gt ' '); varseq+1; varname=scan(varlist,1,','); call symput('fsv'||strip(put(varseq,2.)) ,strip(varname)); varlist=substr(varlist,(2+length(varname))); name=upcase(varname); var_cat='first_sess_vars'; output; end; call symput('fsv_cnt' ,strip(put(varseq,2.))); varlist="&wab_last_sess_vars"; varseq=0; do while (varlist gt ' '); varseq+1; varname=scan(varlist,1,','); call symput('lsv'||strip(put(varseq,2.)) ,strip(varname)); varlist=substr(varlist,(2+length(varname))); name=upcase(varname); var_cat='last_sess_vars'; output; end; call symput('lsv_cnt' ,strip(put(varseq,2.))); varlist="&wab_sum_sess_vars"; varseq=0; do while (varlist gt ' '); varseq+1; varname=scan(varlist,1,','); call symput('ssv'||strip(put(varseq,2.)) ,strip(varname)); varlist=substr(varlist,(2+length(varname))); name=upcase(varname); var_cat='sum_sess_vars'; output; end; call symput('ssv_cnt' ,strip(put(varseq,2.))); keep varname name var_cat; run; %***************************************************************************; %* make sure that there is not any duplication across the 3 types of ; %* SESSION variables ; %***************************************************************************; proc sort data=sess_vars; by varname; run; data uniq mult; set sess_vars; by varname; file log; if first.varname then if last.varname then output uniq; else do; output mult; put 'SESSION variable: ' varname +10 'Variable Category: ' var_cat; end; else do; output mult; put 'SESSION variable: ' varname +10 'Variable Category: ' var_cat; end; run; %if %get_observation_count(indsn=mult) ne 0 %then %do; %put %unquote(&wab_error) There are duplicate SESS_VARS.; %goto ERREXIT; %end; %***************************************************************************; %* make sure that all SESSION variables are in the source data set ; %* (&TEMP_LIB..DETAIL&SESSION_SFFX) ; %***************************************************************************; proc contents data=&temp_lib..detail_&session_sffx noprint out =varlist (keep=type name); run; data varlist; set varlist; name=upcase(name); run; proc sort data=varlist; by name; run; data both only_a only_b; merge varlist (in=in_a) uniq (in=in_b); by name; if in_a then if in_b then output both; else output only_a; else do; file log; put 'SESSION variable: ' varname +10 'Variable Category: ' var_cat; output only_b; end; run; %if %get_observation_count(indsn=only_b) ne 0 %then %do; %put %unquote(&wab_error) There are SESS_VARS that are not %QCMPRES( in) &temp_lib..detail_&session_sffx..; %goto ERREXIT; %end; %***************************************************************************; %* make sure that all SUM_SESS_VARS are numeric ; %***************************************************************************; data non_numeric; set both; if index(upcase(var_cat),'SUM_') and type ne 1; file log; put 'SESSION variable: ' varname +10 'Variable Category: ' var_cat; run; %if %get_observation_count(indsn=non_numeric) ne 0 %then %do; %put %unquote(&wab_error) There are SUM_SESS_VARS that are not numeric.; %goto ERREXIT; %end; data &temp_lib..session_&session_sffx (keep=Client_Id Session_Id First_User_Agent First_Browser First_Browser_Version First_Platform First_Referrer First_Referrer_Domain First_Page_View_Ind Beg_Datetime Date Start_of_Week Start_of_Month Start_of_Quarter Dow_ID Start_of_Year Err_000_Count Err_302_Count Err_304_Count Err_400_Count Err_401_Count Err_403_Count Err_404_Count Err_405_Count Err_408_Count Err_500_Count Err_501_Count End_Datetime Last_Requested_File Last_Page_View_Ind File_Count Page_Count One_Hit_Session_Count Total_Bytes_Sent Duration Page_View_Le1_Ind Page_View_2to4_Ind Page_View_Ge5_Ind Session_Count %do i=1 %to &session_token_var_count; &&session_token_var_&i &&session_string_var_&i %end; %do i=1 %to &ssv_cnt; &&ssv&i %end; %do i=1 %to &fsv_cnt; &&fsv&i %end; %do i=1 %to &lsv_cnt; &&lsv&i %end; rename=(First_User_Agent=User_Agent First_Browser=Browser First_Browser_Version=Browser_Version First_Platform=Platform First_Referrer=Referrer First_Referrer_Domain=Referrer_Domain ) ) %do i=1 %to &session_token_var_count; &temp_lib..&&session_token_var_&i (keep=&&session_token_var_&i &&session_string_var_&i ) %end; ; length First_User_Agent $ 160 First_Browser $ 40 First_Browser_Version $ 8 First_Platform $ 40 First_Requested_File $ 1024 Last_Requested_File $ 1024 Referrer_Query_String_New $ 1024 Referrer_Query_String_Temp $ 1024 Referrer_Query_String $ 1024 First_Referrer $ 1024 First_Referrer_Domain $ 128 Search_Term $ 1024 Beg_Datetime First_Page_View_Ind Last_Page_View_Ind Page_Sequence File_Count Err_000_Count Err_302_Count Err_304_Count Err_400_Count Err_401_Count Err_403_Count Err_404_Count Err_405_Count Err_408_Count Err_500_Count Err_501_Count Session_Count 8; length %do i=1 %to &session_token_var_count; &&session_source_var_&i $ &&session_string_len_&i %end; ; format First_Page_View_Ind Last_Page_View_Ind File_Count Page_Count One_Hit_Session_Count Total_Bytes_Sent Page_View_Le1_Ind Page_View_2to4_Ind Page_View_Ge5_Ind Err_000_Count Err_302_Count Err_304_Count Err_400_Count Err_401_Count Err_403_Count Err_404_Count Err_405_Count Err_408_Count Err_500_Count Err_501_Count Duration Session_Count nlnum15. Status_Code 3.; set &temp_lib..detail_&session_sffx (rename=(Err_000_Count=Err_000 Err_302_Count=Err_302 Err_304_Count=Err_304 Err_400_Count=Err_400 Err_401_Count=Err_401 Err_403_Count=Err_403 Err_404_Count=Err_404 Err_405_Count=Err_405 Err_408_Count=Err_408 Err_500_Count=Err_500 Err_501_Count=Err_501 ) ); array Parm_Names {&&wab_max_nv_pairs_&session_iter} $ 128 _TEMPORARY_; array Parm_Values{&&wab_max_nv_pairs_&session_iter} $ 128 _TEMPORARY_; retain First_User_Agent First_Browser First_Browser_Version First_Platform First_Requested_File First_Referrer First_Referrer_Domain Search_Term " " Beg_Datetime Page_Sequence File_Count Total_Bytes_Sent Err_000_Count Err_302_Count Err_304_Count Err_400_Count Err_401_Count Err_403_Count Err_404_Count Err_405_Count Err_408_Count Err_500_Count Err_501_Count First_Page_View_Ind 0 %do i=1 %to &session_token_var_count; &&session_token_var_&i %end; %do i=1 %to &fsv_cnt; hold_fsv&i %end; ; by Session_Id; format Beg_Datetime End_Datetime datetime.; label Err_000_Count="0 Count: Missing Status Code" Err_302_Count="302 Count: Found" Err_304_Count="304 Count: Not Modified" Err_400_Count="400 Count: Bad Request" Err_401_Count="401 Count: Unauthorized" Err_403_Count="403 Count: Forbidden" Err_404_Count="404 Count: Not Found" Err_405_Count="405 Count: Method Not Allowed" Err_408_Count="408 Count: Request Time-out" Err_500_Count="500 Count: Internal Server Error" Err_501_Count="501 Count: Not Implemented" Total_Bytes_Sent="Total Bytes Sent" Page_Sequence="Page View Sequence" One_Hit_Session_Count="One Hit Session" File_Count="Hit Count" Page_Count="Page Count" Duration="Duration in Seconds" First_Platform="Platform" First_Referrer="Referrer" First_Referrer_Domain="Referrer Domain" First_User_Agent="User Agent" First_Requested_File="First Requested File" First_Page_View_Ind="First Page View Session" Last_Requested_File="Last Requested File" Last_Page_View_Ind="Last Page View Session" End_Datetime="Ending Session Datetime" Beg_Datetime="Beginning Session Datetime" First_Browser="Browser" First_Browser_Version="Browser Version" Page_View_Le1_Ind="0-1 Page Session Count" Page_View_2to4_Ind="2-4 Page Session Count" Page_View_Ge5_Ind="5 or Greater Page Session Count" Session_Count="Session Count" ; %************************************************************************; %* Initialize Count and Indicator Variables. Initialize First Referrer, ; %* First Referrer Domain, First Browser, First Requested File, etc. ; %************************************************************************; if first.Session_Id then do; %do i=1 %to &ssv_cnt; hold_ssv&i=0; %end; %do i=1 %to &fsv_cnt; hold_fsv&i=&&fsv&i; %end; Err_000_Count=0; Err_302_Count=0; Err_304_Count=0; Err_400_Count=0; Err_401_Count=0; Err_403_Count=0; Err_404_Count=0; Err_405_Count=0; Err_408_Count=0; Err_500_Count=0; Err_501_Count=0; Total_Bytes_Sent=0; Page_Sequence=0; Beg_Datetime = Datetime; First_Page_View_Ind=Page_Count; First_Requested_File = Requested_File; First_User_Agent = User_Agent; First_Browser = Browser; First_Browser_Version = Browser_Version; First_Platform = Platform; if Referrer=" " then Referrer="No Referrer"; else First_Referrer = Referrer; if Referrer_Domain=" " then Referrer_Domain="No Referrer Domain"; else First_Referrer_Domain = Referrer_Domain; Referrer_Query_String=scan(Referrer,2,"?"); Search_Term=" "; num_nv_pairs=countc(Referrer_Query_String,'='); Referrer_Query_String_Temp=tranwrd(Referrer_Query_String ,'=&' ,'=%20&' ); Referrer_Query_String_New=strip(prxchange('s/[&=]/ /' ,-1 ,Referrer_Query_String_Temp)); %*********************************************************************; %* Parse Referrer Search Terms. ; %*********************************************************************; if num_nv_pairs > 0 then do; do i=1 to num_nv_pairs; Parm_Names{i}=urldecode(strip(scan(Referrer_Query_String_New ,(2*i)-1," "))); Parm_Values{i}=urldecode(strip(scan(Referrer_Query_String_New ,(2*i) ," "))); if prxmatch('/^(\d)+$/' ,Parm_Values{i}) then continue; if prxmatch('/^(.*txt.*|k[0-9]|search[0-9])$/' ,Parm_Names{i}) or lowcase(Parm_Names{i}) in ("_searchkey", "form_keyword", "ix", "k", "key", "key_word", "keyword", "keywords", "kw", "mt", "name", "p", "q", "qhqn", "qkw", "qry", "qry_str", "qt", "query", "queryterm", "querytext", "rawto", "s", "sc", "search", "search_string", "search_term", "searchfor", "searchstring", "searchwords", "sitesearchquery", "ss", "sstring", "str", "string", "strsrch", "term", "terms", "userquery" ) then Search_Term=strip(Search_Term ||" " ||prxchange('s/[;"]/ /' ,-1 ,prxchange("s/[']/ /" ,-1 ,Parm_Values{i}))); end; end; if Search_Term eq " " then Search_Term="No Search Term"; %*********************************************************************; %* Normalize the _SOURCE variables ; %*********************************************************************; %if &wab_normalize_uri_url eq 1 %then %do; %* create the _SOURCE field using the normalization rules ; %* NORMALIZE COMPRESSes all blanks and LOWCASEs the long string ; %do i=1 %to &session_token_var_count; &&session_source_var_&i=compress(lowcase(&&session_string_var_&i)); %end; %end; %else %do; %* not NORMALIZE COMPBLs blanks and LOWCASEs the long string ; %do i=1 %to &session_token_var_count; &&session_source_var_&i=compbl(lowcase(&&session_string_var_&i)); %end; %end; %*********************************************************************; %* assign _TOKEN unique identifiers for long CLASS strings ; %* NOTE: We use the _SOURCE field to minimize assigning multiple ; %* tokens for the same field. For example, "ABC DE", "aBc De",; %* "AbC dE", and "abc de" will all have the same _TOKEN. ; %*********************************************************************; %do i=1 %to &session_token_var_count; &&session_token_var_&i=put(md5(strip(&&session_source_var_&i)),hex32.); output &temp_lib..&&session_token_var_&i; %end; end; /* if first.Session_Id then do */ %************************************************************************; %* Increment the Error Count Variables based on a requests status code. ; %************************************************************************; select(Status_Code); when( 0) Err_000_Count=Err_000_Count + 1; when(302) Err_302_Count=Err_302_Count + 1; when(304) Err_304_Count=Err_304_Count + 1; when(400) Err_400_Count=Err_400_Count + 1; when(401) Err_401_Count=Err_401_Count + 1; when(403) Err_403_Count=Err_403_Count + 1; when(404) Err_404_Count=Err_404_Count + 1; when(405) Err_405_Count=Err_405_Count + 1; when(408) Err_408_Count=Err_408_Count + 1; when(500) Err_500_Count=Err_500_Count + 1; when(501) Err_501_Count=Err_501_Count + 1; otherwise; end; %************************************************************************; %* Sum the number of bytes sent for each request into a total for each ; %* session. Also increment the page count for each record when the ; %* request is a valid page. ; %************************************************************************; Total_Bytes_Sent=sum(Total_Bytes_Sent,Bytes_Sent); Page_Sequence = Page_Sequence + Page_Count; %do i=1 %to &ssv_cnt; hold_ssv&i+&&ssv&i; %end; %************************************************************************; %* Create the retrieve last request information for each session. ; %************************************************************************; if last.Session_Id then do; %do i=1 %to &ssv_cnt; &&ssv&i=hold_ssv&i; %end; %do i=1 %to &fsv_cnt; &&fsv&i=hold_fsv&i; %end; End_Datetime = Datetime; Last_Requested_File = Requested_File; Last_Page_View_Ind = Page_Count; File_Count = Sequence; One_Hit_Session_Count=(File_Count=1); Page_View_Le1_Ind=(Page_Sequence <= 1); Page_View_2to4_Ind=(2<= Page_Sequence < 5); Page_View_Ge5_Ind=(Page_Sequence >= 5); Page_Count=Page_Sequence; Duration=End_Datetime-Beg_Datetime; Session_Count=1; output &temp_lib..session_&session_sffx; end; run; %***************************************************************************; %* remove duplicates from &TEMP_LIB..&&SESSION_TOKEN_VAR_&I and APPEND ; %* the uniqued result to the corresponding SUMMARY.HASH_ data set and ; %* again remove the duplicates. ; %***************************************************************************; %do i=1 %to &session_token_var_count; %let uniq_outdsn=&temp_lib..&&session_token_var_&i; %let uniq_outdsn=&uniq_outdsn._uniq; proc sort data=&temp_lib..&&session_token_var_&i out =&uniq_outdsn nodupkeys; by &&session_token_var_&i; run; proc append base=summary.hash_&&session_token_var_&i data=&uniq_outdsn; run; proc sort data=summary.hash_&&session_token_var_&i nodupkeys; by &&session_token_var_&i; run; %end; %goto EXIT; %ERREXIT:; %let &retcode=1; %EXIT:; %mend Create_Session; /*----------------------Web Analytics-----------------------------------------*/ /* Create_Detail */ /* Create the SWA Detail data set. */ /* */ /*----------------------------------------------------------------------------*/ /* Supported By: Frank Roediger, FRROED */ /* */ /*----------------------------------------------------------------------------*/ /* History: */ /* Date Description Username Change Code */ /* 20050426 Program Created frroed NA */ /* 20060307 S0347405 frroed */ /* 20060328 S0351214 frroed */ /* */ /*----------------------------------------------------------------------------*/ /* Dependencies: */ /* The following data sets need to be available: */ /* none */ /* */ /* The following macros need to be available: */ /* none */ /* */ /* The following macro variables need to be defined and assigned values */ /* in the invoking environment: */ /* &temp_lib */ /* */ /*----------------------------------------------------------------------------*/ /* Parameters: */ /* &DETAIL_SFFX -- the yyyymmdd string that identifies the date whose */ /* detail data set is being created */ /* &DETAIL_ITER -- the number of the yyyymmdd in the range of dates */ /* whose data is being processed by the current ETL */ /* */ /*----------------------------------------------------------------------------*/ /* Input: */ /* */ /* */ /*----------------------------------------------------------------------------*/ /* Output: */ /* */ /* */ /*----------------------------------------------------------------------------*/ /* Copyright (c) 2005 SAS Institute Inc. All Rights Reserved */ /*----------------------------------------------------------------------------*/ %macro Create_Detail(detail_sffx= ,detail_iter= ); %***************************************************************************; %* Create the STATCODE format that provides an index for each STATUS_CODE ; %* in the date-partitioned detail data. ; %* Create the $REQFILE format that provides an index for each ; %* REQUESTED_FILE in the date-partitioned detail data. ; %***************************************************************************; proc summary data=&temp_lib..detail_&detail_sffx chartype missing n; class status_code requested_file; output out=&temp_lib..all_status_codes (where=(_type_='10') keep=status_code _freq_ _type_) ; output out=&temp_lib..all_requested_files (where=(_type_='01') keep=requested_file _freq_ _type_) ; run; data &temp_lib..all_status_codes_fmt; set &temp_lib..all_status_codes; length label $ 7; fmtname="statcode"; label =strip(put(_n_,7.)); rename status_code=start; keep fmtname status_code label; run; proc format cntlin=&temp_lib..all_status_codes_fmt; run; data &temp_lib..all_requested_files_fmt; set &temp_lib..all_requested_files; length label $ 7; fmtname="$reqfile"; label =strip(put(_n_,7.)); rename requested_file=start; keep fmtname requested_file label; run; proc format cntlin=&temp_lib..all_requested_files_fmt; run; %***************************************************************************; %* Assign macro variables for ARRAYs in the next DATA Step: ; %* REQ_FILE_CNT: the number of REQUESTED_FILEs in the source data set ; %* STAT_CDE_CNT: the number of STATUS_CODEs in the source data set ; %* FLAG_CNT: the number of REQUESTED_FILEs times the number of ; %* STATUS_CODEs (the total number of elements in an array ; %* of flags that track STATUS_CODE by REQUESTED_FILE ; %* within SESSION_ID) ; %***************************************************************************; %let req_file_cnt=%get_observation_count (indsn=&temp_lib..all_requested_files_fmt); %let stat_cde_cnt=%get_observation_count (indsn=&temp_lib..all_status_codes_fmt); %let flag_cnt=%eval(&req_file_cnt*&stat_cde_cnt); %***************************************************************************; %* Make sure that the Partitioned ETL has the additional variables it needs ; %* for the standard analyses. ; %* NOTE: because Web sites may have an extremely large number of ; %* REQUESTED_FILE/STATUS_CODE combinations, the assignment of the ; %* REQFILE_STATCDE indicator is done in a separate set of steps to ; %* avoid the situation where this DATA Step gets bogged down by an ; %* unwieldy ARRAY ; %***************************************************************************; data &temp_lib..detail_&detail_sffx (drop=weekday sess_id_245 reqf_id stat_idx next_sess_id i) &temp_lib..reqfile_statcde (keep=rec_id session_id status_code reqf_id) %do i=1 %to &detail_token_var_count; &temp_lib..&&detail_token_var_&i (keep=&&detail_token_var_&i &&detail_string_var_&i ) %end; ; length %do i=1 %to &detail_token_var_count; &&detail_source_var_&i $ &&detail_string_len_&i %end; ; attrib session_id length=8 format=z12.; format Initial_Reference_by_Session Bytes_Sent File_Count Page_Count Entry_Point Redirect_Count Client_Error_Count Server_Error_Count Entry_Page_Count Exit_Page_Count Err_000_Count Err_302_Count Err_304_Count Err_400_Count Err_401_Count Err_403_Count Err_404_Count Err_405_Count Err_408_Count Err_500_Count Err_501_Count nlnum15. Start_of_Week Start_of_Month Start_of_Quarter Start_of_Year date9. Dow_ID $2.; set &temp_lib..detail_&detail_sffx (rename=(session_id=sess_id_245)) end=eof; by sess_id_245 hour; retain Sequence 0 next_sess_id &next_sess_id ; %* define an array of STATUS_CODE indicator variables ; array statcdes {1:&stat_cde_cnt} _temporary_; Entry_Page_Count=0; rec_id=_n_; if first.sess_id_245 then do; Sequence=0; Entry_Page_Count=1; end; session_id=next_sess_id; %************************************************************************; %* to count each STATUS_CODE only once per hour for STATUS_CODE_BY_HOUR, ; %* initialize all indicator variables to 0 -- not encountered -- at ; %* the head of each hour ; %************************************************************************; if first.hour then do; do i=1 to &stat_cde_cnt; statcdes{i}=0; end; end; Sequence=Sequence+1; Exit_Page_Count=0; Redirect_Count=(300<=status_code<=399); Client_Error_Count=(400<=status_code<=499); Server_Error_Count=(500<=status_code); Err_000_Count=(status_code=0); Err_302_Count=(status_code=302); Err_304_Count=(status_code=304); Err_400_Count=(status_code=400); Err_401_Count=(status_code=401); Err_403_Count=(status_code=403); Err_404_Count=(status_code=404); Err_405_Count=(status_code=405); Err_408_Count=(status_code=408); Err_500_Count=(status_code=500); Err_501_Count=(status_code=501); %************************************************************************; %* assign REQF_ID before normalizing REQUESTED_FILE ; %************************************************************************; reqf_id=input(put(requested_file,$reqfile.),7.); if compbl(Referrer)=" " then Referrer="No Referrer"; if compbl(Referrer_Domain)=" " then Referrer_Domain="No Referrer Domain"; browser=left(trim(lowcase(browser))); browser_version=left(trim(lowcase(browser_version))); platform=left(trim(lowcase(platform))); %************************************************************************; %* Normalize URI/URL variables. ; %************************************************************************; %if &wab_normalize_uri_url eq 1 %then %do; %* create the _SOURCE field using the normalization rules ; %* NORMALIZE COMPRESSes all blanks and LOWCASEs the long string ; %do i=1 %to &detail_token_var_count; &&detail_source_var_&i=compress(lowcase(&&detail_string_var_&i)); %end; requested_file=left(trim(compress(lowcase(requested_file)))); referrer=left(trim(compress(lowcase(referrer)))); referrer_domain=left(trim(compress(lowcase(referrer_domain)))); %end; %else %do; %* not NORMALIZE COMPBLs blanks and LOWCASEs the long string ; %do i=1 %to &detail_token_var_count; &&detail_source_var_&i=compbl(lowcase(&&detail_string_var_&i)); %end; %end; if browser_version eq ' ' then browser_version='NA'; %************************************************************************; %* assign _TOKEN unique identifiers for long CLASS strings ; %* NOTE: We use the _SOURCE field to minimize assigning multiple tokens ; %* for the same field. For example, "ABC DE", "aBc De", ; %* "AbC dE", and "abc de" will all have the same _TOKEN. ; %************************************************************************; %do i=1 %to &detail_token_var_count; &&detail_token_var_&i=put(md5(strip(&&detail_source_var_&i)),hex32.); %end; %************************************************************************; %* Create date variables for summaries. ; %************************************************************************; start_of_week =intnx("week&wab_week_shift",date,0); start_of_month =intnx('month',date,0); start_of_quarter=intnx('qtr',date,0); start_of_year =intnx('year',date,0); weekday=weekday(date); dow_id=strip(put(weekday,2.)); %************************************************************************; %* identify the current STATUS_CODEs location in the array of indicator ; %* variables and set STATUS_CODE_BY_HOUR to 1 if this is its first ; %* occurance during the current hour ; %************************************************************************; stat_idx=input(put(status_code,statcode.),3.); if statcdes{stat_idx} eq 0 then do; statcdes{stat_idx}=1; status_code_by_hour=1; end; else status_code_by_hour=0; if last.sess_id_245 then do; Exit_Page_Count=1; next_sess_id+1; end; %************************************************************************; %* initialize REQFILE_STATCDE to 0 -- it will be re-assigned to 1 for ; %* selected obs in a later DATA Step. ; %************************************************************************; reqfile_statcde=0; output &temp_lib..detail_&detail_sffx; %************************************************************************; %* write out an obs for tokenized field ; %************************************************************************; %do i=1 %to &detail_token_var_count; output &temp_lib..&&detail_token_var_&i; %end; %************************************************************************; %* write out an obs for each detail record. this data set will be ; %* reduced in later DATA Steps so that it contains only obs whose ; %* corresponding REQFILE_STATCDE is 1. ; %************************************************************************; output &temp_lib..reqfile_statcde; if eof then do; call symput('next_sess_id',strip(put(next_sess_id,12.))); end; label Page_Count="Page Count" Redirect_Count="Redirect Count" Client_Error_Count="Client Error Count" Server_Error_Count="Server Error Count" Entry_Page_Count="Entry Page Count" Exit_Page_Count="Exit Page Count" Err_000_Count="0 Count: Missing Status Code" Err_302_Count="302 Count: Found" Err_304_Count="304 Count: Not Modified" Err_400_Count="400 Count: Bad Request" Err_401_Count="401 Count: Unauthorized" Err_403_Count="403 Count: Forbidden" Err_404_Count="404 Count: Not Found" Err_405_Count="405 Count: Method Not Allowed" Err_408_Count="408 Count: Request Time-out" Err_500_Count="500 Count: Internal Server Error" Err_501_Count="501 Count: Not Implemented" Initial_Reference_by_Session="Session Count" Start_of_Week="Start of Week" Start_of_Month="Start of Month" Start_of_Quarter="Start of Quarter" Start_of_Year="Start of Year" Dow_ID="Day of Week ID" status_code_by_hour='Indicator for hourly STATUS_CODE use' reqfile_statcde='Indicator for STATUS_CODE by REQUESTED_FILE use within SESSION_ID' referrer_token='Unique identifier for REFERRER' requested_file_token='Unique identifier for REQUESTED_FILE' referrer_domain_token='Unique identifier for REFERRER_DOMAIN' visitor_id_token='Unique identifier for VISITOR_ID' ; %************************************************************************; %* DROP the _SOURCE fields ; %************************************************************************; drop %do i=1 %to &detail_token_var_count; &&detail_source_var_&i %end; ; run; %***************************************************************************; %* remove duplicates from &TEMP_LIB..&&DETAIL_TOKEN_VAR_&I and APPEND the ; %* uniqued result to the corresponding SUMMARY.HASH_ data set and ; %* again remove the duplicates. ; %***************************************************************************; %do i=1 %to &detail_token_var_count; %let uniq_outdsn=&temp_lib..&&detail_token_var_&i; %let uniq_outdsn=&uniq_outdsn._uniq; proc sort data=&temp_lib..&&detail_token_var_&i out =&uniq_outdsn nodupkeys; by &&detail_token_var_&i; run; proc append base=summary.hash_&&detail_token_var_&i data=&uniq_outdsn; run; proc sort data=summary.hash_&&detail_token_var_&i nodupkeys; by &&detail_token_var_&i; run; %end; %***************************************************************************; %* keep only 1 obs for each SESSION_ID/REQUESTED_FILE/STATUS_CODE ; %* combination. ; %***************************************************************************; proc sort data=&temp_lib..reqfile_statcde out =&temp_lib..reqfile_statcde_uniq (keep=rec_id) nodupkeys; by session_id reqf_id status_code; run; %***************************************************************************; %* m/m the REQFILE_STATCDE_UNIQ data set with the ; %* &temp_lib..detail_&detail_sffx data set to assign the proper value ; %* to the REQFILE_STATCDE field ; %***************************************************************************; proc sort data=&temp_lib..reqfile_statcde_uniq; by rec_id; run; data &temp_lib..detail_&detail_sffx; merge &temp_lib..detail_&detail_sffx (in=in_a) &temp_lib..reqfile_statcde_uniq (in=in_b); by rec_id; if in_b then reqfile_statcde=1; drop rec_id; run; %***************************************************************************; %* update SUMMARY.NEXT_SESS_ID to provide continuity for SESSION_ID ; %* assignments across ETL runs. ; %***************************************************************************; data summary.next_sess_id; session_id=&next_sess_id; etl_dttm=datetime(); format etl_dttm datetime19.; run; %***************************************************************************; %* there is a need to establish indicator variables that flag events one ; %* time per SESSION_ID. these indicator variables are processed by the ; %* Summary Engine to count how many sessions have a certain feature. ; %* EXAMPLE: to count how many sessions have status codes of 404 ; %* (page not found), we establish an INIT_BY_SESS_x variable ; %* that is set to 1 the first time a STATUS_CODE of 404 is ; %* encountered in a session, and has a value of 0 all other ; %* times. By running PROC SUMMARY with INIT_BY_SESS_x as ; %* the analysis var and STATUS_CODE as the class var, we can ; %* then count how many sessions have STATUS_CODE of 404. ; %***************************************************************************; %***************************************************************************; %* create a format for each variable in WAB_INIT_BY_SESS_VARS to indicate ; %* whether the variable is numeric or character. this is necessary to ; %* create the right kind of format for each variable. ; %***************************************************************************; %let wab_ibsv_blank_delimit=%upcase (%sysfunc (translate (%quote(&wab_init_by_sess_vars),' ',',') )); proc sql; create table &temp_lib..wab_ibsv_var_type as select upcase(name) as start, type as label, '$vartype' as fmtname from dictionary.columns where libname eq upcase("&temp_lib") and memname eq upcase("detail_&detail_sffx") and index("&wab_ibsv_blank_delimit",upcase(strip(name))); quit; proc format cntlin=&temp_lib..wab_ibsv_var_type; run; %***************************************************************************; %* create macro vars for each variable in WAB_INIT_BY_SESS_VARS: ; %* a) v_name - the SAS var name ; %* b) v_prfx - the format prefix ($ or nullstring) ; %* c) v_sffx - the format suffix (a letter: a to z) ; %* d) v_fnam - the name of the format that creates an id value for a ; %* corresponding value of V_NAME ; %* e) v_fmt - v_fnam with the terminal period appended ; %* f) v_type - the _type_ mask for PROC SUMMARY ; %* each variable in WAB_INIT_BY_SESS_VARS has its own numeric suffix that ; %* identifies the macro var as belonging to it. ; %* also, create the IBSV_CNT macro var with the # of WAB_INIT_BY_SESS_VARS ; %***************************************************************************; data _null_; length v_type wab_init_by_sess_vars $ 1024 v_sffx v_prfx $ 1 v_fnam $ 8 v_fmt $ 9 v_name $ 32; wab_init_by_sess_vars="&wab_init_by_sess_vars"; i=0; do until (wab_init_by_sess_vars eq ' '); i+1; v_name=scan(wab_init_by_sess_vars,1,','); v_sffx=byte(96+i); if put(upcase(v_name),$vartype.) eq 'char' then v_prfx='$'; else v_prfx=' '; v_fnam=strip(strip(v_prfx) || 'v_fmt' || strip(v_sffx) ); v_fmt =strip(v_fnam) || '.'; call symput('v_name'||strip(put(i,3.)) ,strip(v_name) ); call symput('v_prfx'||strip(put(i,3.)) ,strip(v_prfx) ); call symput('v_sffx'||strip(put(i,3.)) ,strip(v_sffx) ); call symput('v_fnam'||strip(put(i,3.)) ,strip(v_fnam) ); call symput('v_fmt'||strip(put(i,3.)) ,strip(v_fmt) ); wab_init_by_sess_vars=substr(wab_init_by_sess_vars,(2+length(v_name))); end; call symput('ibsv_cnt',strip(put(i,3.))); v_type=repeat('0',(i-1)); do j=1 to i; substr(v_type,j,1)='1'; call symput('v_type'||strip(put(j,3.)) ,strip(v_type) ); substr(v_type,j,1)='0'; end; run; %***************************************************************************; %* execute a single PROC SUMMARY step to create an OUTPUT data set for each ; %* variable in the WAB_INIT_BY_SESS_VARS list. ; %***************************************************************************; proc summary data=&temp_lib..detail_&detail_sffx sum missing chartype; class %sysfunc(translate(%quote(&wab_init_by_sess_vars),' ',',')); types %sysfunc(translate(%quote(&wab_init_by_sess_vars),' ',',')); var ; %do i=1 %to &ibsv_cnt; output out=&temp_lib..&&v_name&i( where=(_type_="&&v_type&i") keep= &&v_name&i _type_) ; %end; run; %***************************************************************************; %* for each variable in the WAB_INIT_BY_SESS_VARS list, create a format to ; %* assign an ID for each of its values. this id will be used to set a ; %* binary indicator in an array in a subsequent DATA step. ; %***************************************************************************; %do i=1 %to &ibsv_cnt; data &temp_lib..cntlin; set &temp_lib..&&v_name&i end=eof; fmtname="&&v_fnam&i"; start =&&v_name&i; label =strip(put(_n_,7.)); if eof then call symput("v_cnt&i",strip(put(_n_,7.))); keep fmtname start label; run; proc format cntlin=&temp_lib..cntlin; run; %end; %***************************************************************************; %* These macro variables make it convenient to use ranges to identify ; %* variable lists. ; %***************************************************************************; data _null_; do i=1 to &ibsv_cnt; call symput('xx'||strip(put(i,3.)) ,'1' ); end; run; %***************************************************************************; %* Create the initialize-by-session variables. ; %* NOTE: this requires a second pass of the data set because the earlier ; %* pass has the responsibility of normalizing several character ; %* variables, any of which could be the source for initialize-by- ; %* session indicators. ; %***************************************************************************; data &temp_lib..detail_&detail_sffx; %* make sure that each INIT_BY_SESS_VAR has the standard format ; format %do i=1 %to &ibsv_cnt; init_by_sess_&&v_sffx&i %end; nlnum15.; %* define one array for each INIT_BY_SESS_VAR ; %do i=1 %to &ibsv_cnt; array var_&&v_sffx&i {1:&&v_cnt&i} var_&&v_sffx&i&&xx&i-var_&&v_sffx&i&&v_cnt&i; %end; set &temp_lib..detail_&detail_sffx end=eof; by session_id; retain %do i=1 %to &ibsv_cnt; var_&&v_sffx&i&&xx&i-var_&&v_sffx&i&&v_cnt&i %end; ; %* determine the max for REFERRER_QUERY_STRING name-value pairs ; %* (REFERRER_QUERY_STRING is later defined as 'scan(Referrer,2,"?")' ; retain max_nv_pairs 1; if countc(scan(Referrer,2,"?"),'=') gt max_nv_pairs then max_nv_pairs=countc(scan(Referrer,2,"?"),'='); if first.session_id then do; %* initialize each INIT_BY_SESS_VAR array ; %do i=1 %to &ibsv_cnt; do i=1 to &&v_cnt&i; var_&&v_sffx&i{i}=0; end; %end; end; %* for each INIT_BY_SESS_VAR... ; %do i=1 %to &ibsv_cnt; %* locate the position of the value in the variables array ; idx_&&v_sffx&i=input(put(&&v_name&i,&&v_fmt&i..),7.); %* if it has not been encountered in the sess., turn on indicator ; if not var_&&v_sffx&i{idx_&&v_sffx&i} then do; var_&&v_sffx&i{idx_&&v_sffx&i}=1; init_by_sess_&&v_sffx&i=1; end; %* if it has already been encountered, turn off indicator ; else init_by_sess_&&v_sffx&i=0; %end; if eof then call symput('wab_max_nv_pairs_'||"&detail_iter" ,strip(put(max_nv_pairs,6.)) ); drop i max_nv_pairs %do i=1 %to &ibsv_cnt; var_&&v_sffx&i&&xx&i-var_&&v_sffx&i&&v_cnt&i idx_&&v_sffx&i %end; ; label %do i=1 %to &ibsv_cnt; init_by_sess_&&v_sffx&i="INIT_BY_SESSION for &&v_name&i" %end; ; run; %***************************************************************************; %* Separate out any Tally/Spider records into a separate _TALLY data set. ; %***************************************************************************; data &temp_lib..detail_&detail_sffx &temp_lib..detail_tally_&detail_sffx; set &temp_lib..detail_&detail_sffx; if (file_count gt 1 and upcase(requested_file) eq '(NONPAGE)' and upcase(referrer) eq '(TALLY)' ) or (upcase(requested_file) eq '(SPIDER)' and upcase(referrer) eq '(TALLY)' ) then output &temp_lib..detail_tally_&detail_sffx; else output &temp_lib..detail_&detail_sffx; run; %mend Create_Detail; /*----------------------Web Analytics-----------------------------------------*/ /* Config_Parameters */ /* Transform the WACONFIG parameters into macro variables. */ /* */ /*----------------------------------------------------------------------------*/ /* Supported By: Frank Roediger, FRROED */ /* */ /*----------------------------------------------------------------------------*/ /* History: */ /* Date Description Username Change Code */ /* 20050426 Program Created frroed NA */ /* */ /*----------------------------------------------------------------------------*/ /* Dependencies: */ /* The following data sets need to be available: */ /* CONFIG.WACONFIG */ /* */ /* The following macros need to be available: */ /* %locktest */ /* %get_observation_count */ /* */ /* The following macro variables need to be defined and assigned values */ /* in the invoking environment: */ /* &wab_note */ /* &wab_warning */ /* &wab_error */ /* */ /*----------------------------------------------------------------------------*/ /* Parameters: */ /* none */ /* */ /*----------------------------------------------------------------------------*/ /* Input: */ /* none */ /* */ /*----------------------------------------------------------------------------*/ /* Output: */ /* WACONFIG configuration parameters expressed as macro variables */ /* */ /*----------------------------------------------------------------------------*/ /* Copyright (c) 2005 SAS Institute Inc. All Rights Reserved */ /*----------------------------------------------------------------------------*/ %macro config_parameters(retcode= ); %local macname; %let macname=&sysmacroname; %global dated_msg; %let &retcode=0; %***************************************************************************; %* Initialize macro parameters from web analytics config data set. ; %***************************************************************************; %if %sysfunc(exist(config.waconfig)) ne 0 %then %do; %locktest(dset=config.waconfig); %if &lock_sw eq 0 %then %do; %if %get_observation_count(indsn=config.waconfig) gt 0 %then %do; %******************************************************************; %* make sure that all the config parm macro vars are GLOBAL ; %******************************************************************; data _null_; set config.waconfig end=eof; length gblmvars $3200; retain gblmvars ' '; %***************************************************************; %* WEEK_START is the only parm whose PARAMETER_NAME does not ; %* become its macro variable name. make the translation. ; %***************************************************************; if upcase(parameter_name) eq 'WEEK_START' then parameter_name='wab_week_shift'; gblmvars=strip(gblmvars) || ' ' || strip(parameter_name); if eof then call symput('gblmvars',strip(gblmvars)); run; %global &gblmvars; %let bad_week_start=1; data _null_; set config.waconfig end=eof; retain wab_pathing_days_in_history wab_num_session_data_sets wab_interactive_support wab_top_path_pages ; length week_shift $ 2; if upcase(parameter_name) eq 'WEEK_START' then do; call symput('bad_week_start','0'); if upcase(value) eq 'SUNDAY' then week_shift='.1'; else if upcase(value) eq 'MONDAY' then week_shift='.2'; else if upcase(value) eq 'TUESDAY' then week_shift='.3'; else if upcase(value) eq 'WEDNESDAY' then week_shift='.4'; else if upcase(value) eq 'THURSDAY' then week_shift='.5'; else if upcase(value) eq 'FRIDAY' then week_shift='.6'; else if upcase(value) eq 'SATURDAY' then week_shift='.7'; else do; call symput('bad_week_start',strip(value)); stop; end; call symput('wab_week_shift',strip(week_shift)); end; %* make sure that there are no embedded spaces in comma- ; %* delimited lists ; else if upcase(parameter_name) in ('WAB_INIT_BY_SESS_VARS' ,'WAB_FIRST_SESS_VARS' ,'WAB_LAST_SESS_VARS' ,'WAB_SUM_SESS_VARS' ) then do; call symput(parameter_name,compress(value)); end; else do; call symput(parameter_name,strip(value)); end; if upcase(parameter_name) eq 'WAB_PATHING_DAYS_IN_HISTORY' then wab_pathing_days_in_history=value; else if upcase(parameter_name) eq 'WAB_NUM_SESSION_DATA_SETS' then wab_num_session_data_sets=value; else if upcase(parameter_name) eq 'WAB_INTERACTIVE_SUPPORT' then wab_interactive_support=value; else if upcase(parameter_name) eq 'WAB_TOP_PATH_PAGES' then wab_top_path_pages=value; if eof then do; %************************************************************; %* when WAB_INTERACTIVE_SUPPORT and WAB_TOP_PATH_PAGES are ; %* not the same, the PROC Path results accumulated by ; %* processing the SUMMARY.PATHING_yyyymmdd data sets ; %* will be different. ; %* this code makes sure that the smaller value is used if ; %* WAB_INTERACTIVE_SUPPORT and WAB_TOP_PATH_PAGES are ; %* not the same. ; %************************************************************; if wab_interactive_support ne wab_top_path_pages then do; call symput('path_msg' ,"&wab_warning WAB_TOP_PATH_PAGES -- " || strip(wab_top_path_pages) || ' -- is not the same as WAB_INTERACTIVE_' || 'SUPPORT -- ' || strip(wab_interactive_support) || '. The smaller value is being used in ' || 'parameters.' ); if wab_interactive_support gt wab_top_path_pages then wab_interactive_support=wab_top_path_pages; else wab_top_path_pages=wab_interactive_support; call symput('wab_interactive_support' ,strip(wab_interactive_support) ); call symput('wab_top_path_pages' ,strip(wab_top_path_pages) ); end; else call symput('path_msg',' '); %************************************************************; %* when WAB_NUM_SESSION_DATA_SETS is less than ; %* WAB_PATHING_DAYS_IN_HISTORY, the drill-down report ; %* for the Interactive Funnel will fail. as the ; %* arguments are assigned for the WACONFIG-based macro ; %* parameters, this condition is detected and expressed ; %* in the DATED_MSG macro (blank if the condition does ; %* not exist). also, the WACONFIG value for ; %* WAB_NUM_SESSION_DATA_SETS is set to the value of ; %* WAB_PATHING_DAYS_IN_HISTORY. ; %* the message is not output until III_A - the aging ; %* section. ; %************************************************************; if wab_num_session_data_sets lt wab_pathing_days_in_history then do; call symput('dated_msg' ,"&wab_warning WACONFIG has a value of " || strip(wab_num_session_data_sets) || ' for the WAB_NUM_SESSION_DATA_SETS' || ' parameter. This value must be >= ' || strip(wab_pathing_days_in_history) || ', the value for the' || ' WAB_PATHING_DAYS_IN_HISTORY parameter' || ' or else the Interactive Funnel drill-' || 'down report will fail. WAETL is using ' || strip(wab_pathing_days_in_history) || ' as the WAB_NUM_SESSION_DATA_SETS' || ' parameter to control the retention of' || ' DATED.SESSION_yyyymmdd data sets.' ); call symput('wab_num_session_data_sets' ,strip(wab_pathing_days_in_history) ); end; else call symput('dated_msg',' '); end; run; %put &path_msg; %if &bad_week_start eq 1 %then %do; %put %unquote(&wab_note) CONFIG.WACONFIG does not have %QCMPRES( an) entry for the WEEK_START parameter. The %QCMPRES( default) entry -- Sunday -- is being used.; %let wab_week_shift=.1; %end; %else %if &bad_week_start ne 0 %then %do; %put %unquote(&wab_error) CONFIG.WACONFIG has an %QCMPRES( invalid) value -- &bad_week_start -- for the %QCMPRES( WEEK_START) parameter. Please assign a standard %QCMPRES( English)-language day-of-week to the WEEK_START parameter.; %let &retcode=1; %end; %end; %end; %end; %mend config_parameters; /*----------------------Web Analytics-----------------------------------------*/ /* Create_Funnel */ /* Create the SWA Funnel reports */ /* */ /*----------------------------------------------------------------------------*/ /* Supported By: Frank Roediger, FRROED */ /* */ /*----------------------------------------------------------------------------*/ /* History: */ /* Date Description Username Change Code */ /* 20050426 Program Created frroed NA */ /* */ /*----------------------------------------------------------------------------*/ /* Dependencies: */ /* The following data sets need to be available: */ /* SUMMARY.PAGE_FREQUENCIES */ /* SUMMARY.PATHING */ /* */ /* The following macros need to be available: */ /* none */ /* */ /* The following macro variables need to be defined and assigned values */ /* in the invoking environment: */ /* &_FUNNEL_DATA_UID_ a unique identifier that is used as a data set */ /* suffix so that WORK data set names are unique */ /* &INFO: a switch variable that indicates whether to create a */ /* place-holding (1 obs with missing values) or a populated */ /* (summarization of actual data) version of &OUTPUT_DATASET */ /* &LEVELONE: the reference to the REQUESTED_FILE that is the first */ /* entry in the funnel. the $IDXTORF format associates the */ /* reference number with the actual REQUESTED_FILE. there */ /* are seven of these references, &LEVELONE - &LEVELSEVEN */ /* &ONETHENTWO: a switch variable that indicates whether the &LEVELONE and */ /* &LEVELTWO pages need to have occurred consecutively in */ /* order to be tallied in the analysis. there are six of */ /* these switches, &ONETHENTWO - &SIXTHENSEVEN. */ /* &DATES: a comma-delimited list of the YYYYMMDD values that are the */ /* dates for the analysis. */ /* &LIBREFS: a comma-delimited list of the SAS libnames that need to */ /* be defined by the process. */ /* &LIBPATHS: a comma-delimited list of the locations for the */ /* corresponding libnames in the LIBREFS list. */ /* */ /*----------------------------------------------------------------------------*/ /* Parameters: */ /* RPT_LIB - the libref where the funnel report is written */ /* */ /*----------------------------------------------------------------------------*/ /* Input: */ /* none */ /* */ /*----------------------------------------------------------------------------*/ /* Output: */ /* Standard SWA Funnel report */ /* */ /*----------------------------------------------------------------------------*/ /* Copyright (c) 2005 SAS Institute Inc. All Rights Reserved */ /*----------------------------------------------------------------------------*/ %macro Create_Funnel(rpt_lib=stp ); options compress=yes nofmterr; %PUT CREATE_FUNNEL MACRO; %* create the $IDXTORF format that identifies REQUESTED_FILEs by their index ; proc format cntlin=summary.page_frequencies; run; %* assign values to macro variables that identify the funnel components ; data _null_; length temp_str $ 3200; %* create FUNNEL_LIST, which will be used in an IN list to subset only the ; %* pathing obs that pertain to the designated funnel elements ; if "&levelone" gt ' ' then temp_str="'" || strip("&levelone") || "'"; if "&leveltwo" gt ' ' then temp_str=strip(temp_str) || ",'" || strip("&leveltwo") || "'"; if "&levelthree" gt ' ' then temp_str=strip(temp_str) || ",'" || strip("&levelthree") || "'"; if "&levelfour" gt ' ' then temp_str=strip(temp_str) || ",'" || strip("&levelfour") || "'"; if "&levelfive" gt ' ' then temp_str=strip(temp_str) || ",'" || strip("&levelfive") || "'"; if "&levelsix" gt ' ' then temp_str=strip(temp_str) || ",'" || strip("&levelsix") || "'"; if "&levelseven" gt ' ' then temp_str=strip(temp_str) || ",'" || strip("&levelseven") || "'"; call symput('funnel_list',strip(temp_str)); %* use the $IDXTORF format to identify the referrer page associated with ; %* each code passed by the UI ; %* NOTE: any embedded single quotes are TRANWRDed into paired single quotes ; temp_str=tranwrd(put(strip("&levelOne"),$idxtorf.),"'","''"); if strip(temp_str) eq strip("&LevelOne") and temp_str gt ' ' then temp_str='NOT AVAILABLE -- ' || strip(temp_str); call symput("level01",strip(temp_str)); temp_str=tranwrd(put(strip("&levelTwo"),$idxtorf.),"'","''"); if strip(temp_str) eq strip("&LevelTwo") and temp_str gt ' ' then temp_str='NOT AVAILABLE -- ' || strip(temp_str); call symput("level02",strip(temp_str)); temp_str=tranwrd(put(strip("&levelThree"),$idxtorf.),"'","''"); if strip(temp_str) eq strip("&LevelThree") and temp_str gt ' ' then temp_str='NOT AVAILABLE -- ' || strip(temp_str); call symput("level03",strip(temp_str)); temp_str=tranwrd(put(strip("&levelFour"),$idxtorf.),"'","''"); if strip(temp_str) eq strip("&LevelFour") and temp_str gt ' ' then temp_str='NOT AVAILABLE -- ' || strip(temp_str); call symput("level04",strip(temp_str)); temp_str=tranwrd(put(strip("&levelFive"),$idxtorf.),"'","''"); if strip(temp_str) eq strip("&LevelFive") and temp_str gt ' ' then temp_str='NOT AVAILABLE -- ' || strip(temp_str); call symput("level05",strip(temp_str)); temp_str=tranwrd(put(strip("&levelSix"),$idxtorf.),"'","''"); if strip(temp_str) eq strip("&LevelSix") and temp_str gt ' ' then temp_str='NOT AVAILABLE -- ' || strip(temp_str); call symput("level06",strip(temp_str)); temp_str=tranwrd(put(strip("&levelSeven"),$idxtorf.),"'","''"); if strip(temp_str) eq strip("&LevelSeven") and temp_str gt ' ' then temp_str='NOT AVAILABLE -- ' || strip(temp_str); call symput("level07",strip(temp_str)); call symput("twocontig",""); call symput("threecontig",""); call symput("fourcontig",""); call symput("fivecontig",""); call symput("sixcontig",""); call symput("sevencontig",""); if left(trim(lowcase(compress("&oneThenTwo"))))="on" then call symput("twocontig","yes"); if left(trim(lowcase(compress("&twoThenThree"))))="on" then call symput("threecontig","yes"); if left(trim(lowcase(compress("&threeThenFour"))))="on" then call symput("fourcontig","yes"); if left(trim(lowcase(compress("&fourThenFive"))))="on" then call symput("fivecontig","yes"); if left(trim(lowcase(compress("&fiveThenSix"))))="on" then call symput("sixcontig","yes"); if left(trim(lowcase(compress("&sixThenSeven"))))="on" then call symput("sevencontig","yes"); call symput('samp','0'); run; *% create metadata using the funnel parameters passed by the UI ; data d&_funnel_data_uid_; length events desc $ 128; format events desc $128.; %if %length(%bquote(&level01)) gt 0 %then %do; desc="&level01"; contiguous=0; events="requested_file='&level01'"; output; %end; %if %length(%bquote(&level02)) gt 0 %then %do; events="requested_file='&level02'"; desc="&level02"; contiguous=0; %if %length(%cmpres(&TwoContig)) gt 2 %then %do; contiguous=1; %end; output; %end; %if %length(%bquote(&level03)) gt 0 %then %do; events="requested_file='&level03'"; desc="&level03"; contiguous=0; %if %length(%cmpres(&ThreeContig)) gt 2 %then %do; contiguous=1; %end; output; %end; %if %length(%bquote(&level04)) gt 0 %then %do; events="requested_file='&level04'"; desc="&level04"; contiguous=0; %if %length(%cmpres(&FourContig)) gt 2 %then %do; contiguous=1; %end; output; %end; %if %length(%bquote(&level05)) gt 0 %then %do; events="requested_file='&level05'"; desc="&level05"; contiguous=0; %if %length(%cmpres(&FiveContig)) gt 2 %then %do; contiguous=1; %end; output; %end; %if %length(%bquote(&level06)) gt 0 %then %do; events="requested_file='&level06'"; desc="&level06"; contiguous=0; %if %length(%cmpres(&SixContig)) gt 2 %then %do; contiguous=1; %end; output; %end; %if %length(%bquote(&level07)) gt 0 %then %do; events="requested_file='&level07'"; desc="&level07"; contiguous=0; %if %length(%cmpres(&SevenContig)) gt 2 %then %do; contiguous=1; %end; output; %end; run; %* If any ADJACENT checkbox has been checked in the UI, make sure that the ; %* &CONTIG macro variable is set to TRUE. ; %* NOTE: the CONTIGUOUS field is set to 1 if ADJACENT has been checked and is ; %* set to 0 if it has not. any non-zero sum indicates that one of the ; %* ADJACENT checkboxes has been checked. ; proc summary data=D&_funnel_data_uid_ chartype noprint; var contiguous; output out=contig_&_funnel_data_uid_ sum=contig_ttl; run; data _null_; set contig_&_funnel_data_uid_; if contig_ttl then contig='TRUE'; else contig='F'; call symput("contig",trim(left(contig))); run; %* create macro variables with the values from the funnel metadata ; data _null_; set d&_funnel_data_uid_ end=eof; length cond_list $50; retain cond_list; if _n_ eq 1 then do; cond_list="'" || strip(put(contiguous,6.)) || "'"; end; else do; cond_list=strip(cond_list) || ",'" || strip(put(contiguous,6.)) || "'"; end; call symput(compress('event'|| put(_n_,3.)) ,trim(left(events))); call symput(compress('desc'|| put(_n_,3.)) ,trim(left(desc))); call symput(compress('condition'|| put(_n_,3.)) ,trim(left(put(contiguous,6.)))); if eof then do; call symput('total_events',trim(left(put(_n_,3.)))); call symput('cond_list',strip(cond_list)); %* determine if all ADJACENT checkboxes have the same status ; %* NOTE: the 1st element occupies the 1st four bytes in the comma- ; %* delimited COND_LIST. it is always zero, so it is not ; %* considered when checking the ADJACENT checkboxes status. ; temp_list=substr(cond_list,5); if index(temp_list,'0') and index(temp_list,'1') then call symput('same_adj','0'); else call symput('same_adj','1'); end; run; options noquotelenmax; /* avoid warning message about unbalanced quotes */ %******************************************************************************; %* convert &DATES (YYYYMMDD) to &SASDATES (SAS date values) and create one ; %* macro variable for each date in &DATES ; %******************************************************************************; %* make sure that the dates are in ascending order -- this will mean that the ; %* extract will not have to be sorted ; %******************************************************************************; data dates_&_funnel_data_uid_; date_str="&dates"; length pathing_date $ 8; do i=1 to (1+countc(date_str,',')); pathing_date=compress(tranwrd(scan(date_str,i,','),'-',' ')); sas_date=input(pathing_date,yymmdd8.); output; end; keep pathing_date sas_date; run; proc sort data=dates_&_funnel_data_uid_; by sas_date; run; data _null_; set dates_&_funnel_data_uid_ end=eof; call symput('extr_date_' || strip(put(_n_,2.)) ,strip(pathing_date) ); if eof then do; call symput('extr_date_cnt' ,strip(put(_n_,2.)) ); end; run; %* --------------------------------------------------------------------------- ; %* this block handles the cases where some (not all) of the Adjacent ; %* checkboxes have been checked. it is a separate block because ; %* modifying the existing code to accommodate these conditions was ; %* not feasible in the available time-frame. ; %* --------------------------------------------------------------------------- ; %if &same_adj eq 0 %then %do; %* create a subset extract from the PATHING_yyyymmdd data sets with just the ; %* designated dates ; data extr_f&_funnel_data_uid_; length requested_file $ 1024; set %do i=1 %to &extr_date_cnt; summary.pathing_&&extr_date_&i %end; ; requested_file=put(reference,$idxtorf.); run; %* create the $FUN_REF format to identify designated funnel elements ; data cntlin_&_funnel_data_uid_; length fmtname $ 8 start $ 12 label $ 1; fmtname='$fun_ref'; label='1'; start="&LevelOne"; if start gt ' ' then output; label='2'; start="&LevelTwo"; if start gt ' ' then output; label='3'; start="&LevelThree"; if start gt ' ' then output; label='4'; start="&LevelFour"; if start gt ' ' then output; label='5'; start="&LevelFive"; if start gt ' ' then output; label='6'; start="&LevelSix"; if start gt ' ' then output; label='7'; start="&LevelSeven"; if start gt ' ' then output; hlo='O'; label='0'; output; run; proc format cntlin=cntlin_&_funnel_data_uid_; run; data in_f&_funnel_data_uid_; set extr_f&_funnel_data_uid_ (keep=session_id date reference sequence ); %***************************************************************************; %* NOTE: the NOTSORTED option is used on the BY statement in case the order ; %* of SESSION_ID assignments is out of sequence (this can happen if ; %* dates are not loaded into the warehouse consecutively). ; %***************************************************************************; by session_id notsorted; length ckbox1 -ckbox&total_events $ 1; array ckbox {1:&total_events} $ ckbox1 -ckbox&total_events; array level {1:&total_events} level1 -level&total_events; array funnel {1:&total_events} funnel1 -funnel&total_events; array hold_fun {1:&total_events} hold_fun1-hold_fun&total_events; retain level1 -level&total_events funnel1 -funnel&total_events hold_fun1-hold_fun&total_events max_hold curr_hold ckbox1-ckbox&total_events ; if _n_ eq 1 then do; do i=1 to &total_events; %* break out adj checkboxed from CHECKBOXES ; ckbox{i}=strip(tranwrd(scan("&cond_list",i,','),"'"," ")); end; end; %* initialize the active funnel storage area ; if first.session_id then do; do i=1 to &total_events; level{i} =0; funnel{i} =0; hold_fun{i}=0; end; max_hold=0; curr_hold=1; end; element_sw=put(reference,$fun_ref.); %* ------------------------------------------------------------------------ ; %* current element is a funnel event ; %* ------------------------------------------------------------------------ ; if element_sw gt '0' then do; curr_pos=input(element_sw,1.); level{curr_pos}=1; if curr_hold eq curr_pos then do; %* register when an in-funnel entry occurs in sequence ; funnel{curr_hold}= 1; curr_hold+1; %* dump when all funnel elements have been encountered ; if curr_hold gt &total_events then do; curr_hold+(-1); link DUMP; end; end; else do; %* dump when out-of-order funnel entry occurs ; link DUMP; %* register when 1st funnel entry has recurred out-of-order ; if curr_pos eq 1 then do; funnel{curr_hold}= 1; curr_hold+1; end; end; end; %* ------------------------------------------------------------------------ ; %* current element is NOT a funnel event ; %* ------------------------------------------------------------------------ ; else do; if ckbox{curr_hold} eq '0' then do; %* ignore non-funnel entry when current funnel element does not need ; %* to be contiguous ; end; else do; %* dump when non-funnel entry occurs where contiguity is required ; link DUMP; end; end; if last.session_id then do; %* move the active funnel storage area to HOLD, if necessary ; link DUMP; %* write out the sessions most complete encounter with the funnel ; do i=1 to &total_events; %* LEVELx is not in HOLD -- it indicates any encounter ; funnel{i} =hold_fun{i}; end; output; end; drop i j ckbox1-ckbox&total_events hold_fun1-hold_fun&total_events max_hold curr_hold element_sw curr_pos reference sequence ; return; DUMP:; %* Dump the active funnel storage area into HOLD ; if curr_hold gt max_hold then do; do j=1 to curr_hold; hold_fun{j}=funnel{j}; end; max_hold=curr_hold; end; %* Re-initialize the active funnel storage area ; do i=1 to &total_events; %* LEVELx is not re-initialized -- it indicates any encounter ; funnel{i} =0; end; curr_hold=1; return; run; %end; %* --------------------------------------------------------------------------- ; %* this block handles the cases where all of the Adjacent checkboxes have ; %* the same status (either checked or non-checked). it is a separate ; %* block because this code was already in place and worked without ; %* modification for these two scenarios but could not be readily ; %* changed to handle the additional scenario of mixed-status Adjacent ; %* checkboxes ; %* --------------------------------------------------------------------------- ; %else %do; %* create a subset extract from &PATH_DSN with just the funnel elements for ; %* the designated dates ; data extr_f&_funnel_data_uid_; length requested_file $ 1024; set %do i=1 %to &extr_date_cnt; summary.pathing_&&extr_date_&i %end; ; if reference in (&funnel_list); requested_file=put(reference,$idxtorf.); run; %* --------------------------------------------------------------------------- ; %* process the subset extract to create variables for the funnel analysis: ; %* LEVELx - a 0/1 indicator of whether the corresponding REQUESTED_ ; %* FILE is in the SESSION_ID ; %* FUNNELx - a 0/1 indicator of whether the corresponding LEVELx and ; %* all previous levels have been detected ; %* LEVELSS_x - contains the latest SEQUENCE of the SESSION_ID where the ; %* corresponding LEVELx has been encountered ; %* the EVENTx macro variables contain comparisons in the form: ; %* REQUESTED_FILE="&LEVEL0x" ; %* the CONDITIONx macro variables contain indicators of whether the ; %* corresponding REQUESTED_FILE needs to be contiguous with the funnels ; %* previous REQUESTED_FILE ; %* --------------------------------------------------------------------------- ; data in_f&_funnel_data_uid_; set extr_f&_funnel_data_uid_ (keep=session_id date requested_file sequence ); %***************************************************************************; %* NOTE: the NOTSORTED option is used on the BY statement in case the order ; %* of SESSION_ID assignments is out of sequence (this can happen if ; %* dates are not loaded into the warehouse consecutively). ; %***************************************************************************; by session_id notsorted; array level {1:&total_events} level1 -level&total_events; array funnel {1:&total_events} funnel1 -funnel&total_events; array levelss {1:&total_events} levelss_1-levelss_&total_events; array hold_lvl {1:&total_events} hold_lvl1-hold_lvl&total_events; array hold_fun {1:&total_events} hold_fun1-hold_fun&total_events; array hold_lss {1:&total_events} hold_lss1-hold_lss&total_events; retain level1 -level&total_events funnel1 -funnel&total_events levelss_1-levelss_&total_events hold_lvl1-hold_lvl&total_events hold_fun1-hold_fun&total_events hold_lss1-hold_lss&total_events max_hold curr_hold ; %* initialize the active funnel storage area ; if first.session_id then do; do i=1 to &total_events; level{i} =0; funnel{i} =0; levelss{i} =.; %* initialize the HOLD area (for funnels with contiguous elements) ; if upcase("&contig") eq 'TRUE' then do; hold_lvl{i}=0; hold_fun{i}=0; hold_lss{i}=.; end; end; max_hold=0; curr_hold=0; end; %* Is the current REQUESTED_FILE the 1st funnel event? ; %* Does the 2nd funnel element need to be contiguous with the 1st? ; %* - Dump the active funnel storage area into HOLD. ; %* - Re-initialize the active funnel storage area. ; %* Set the 1st event total indicator to 1. ; %* Set the 1st funnel indicator to 1. ; %* Set the 1st sequence comparison integer to SEQUENCE -- the request ; %* number in the session where the first event occurs. ; if &event1 then do; if upcase("&contig") eq 'TRUE' then do; if &condition2 then do; %* Dump the active funnel storage area into HOLD ; if curr_hold gt max_hold then do; do j=1 to curr_hold; hold_lvl{j}=1; hold_fun{j}=1; hold_lss{j}=levelss{j}; end; max_hold=curr_hold; end; %* Re-initialize the active funnel storage area ; do i=1 to &total_events; %* LEVELx is not re-initialized -- it indicates any encounter ; funnel{i} =0; levelss{i} =.; curr_hold=0; end; end; end; level1 = 1; funnel1 = 1; levelss_1 = sequence; curr_hold+1; end; %* Is the current REQUESTED_FILE a subsequent (not 1st) funnel event? ; %do curr_lvl = 2 %to &total_events; %let prev_lvl = %eval(&curr_lvl - 1); %* IF the contiguous indicator (&CONDITIONx) is NOT on: ; %* If the current REQUESTED_FILE is a funnel event: ; %* Set the current event total indicator to 1. ; %* Set the current sequence comparison integer to SEQUENCE -- the ; %* request number in the session where the current event occurs ; %* If the previous events FUNNELx indicator is on then: ; %* Set the current funnel indicator to 1. ; if &&condition&curr_lvl ne 1 then do; if &&event&curr_lvl then do; level&curr_lvl = 1; levelss_&curr_lvl=sequence; if funnel&prev_lvl = 1 then funnel&curr_lvl = 1; curr_hold+1; end; end; %* ELSE (the contiguous indicator--&CONDITIONx--is on): ; %* If the current REQUESTED_FILE is a funnel event: ; %* Set IN_SEQ to indicate if the REQUESTED_FILE is in sequence ; %* If the current sequence comparison integer has not been set yet ; %* AND ; %* The IN_SEQ indicator is set: ; %* Set the current event total indicator to 1. ; %* Set the curr sequence comparison integer to SEQUENCE -- the ; %* req number in the session where the current event occurs. ; %* Set the current funnel indicator to 1. ; %* Else: ; %* Set the current funnel indicator to 0 to indicate that the ; %* string of contiguous funnel elements has been broken ; else do; if &&event&curr_lvl then do; level&curr_lvl = 1; %* Set IN_SEQ to indicate if the REQUESTED_FILE is in sequence ; if levelss_&prev_lvl le . then in_seq=0; else if sequence - levelss_&prev_lvl eq 1 then in_seq=1; else in_seq=0; if levelss_&curr_lvl le .z and in_seq then do; levelss_&curr_lvl=sequence; funnel&curr_lvl = 1; curr_hold+1; end; else do; funnel&curr_lvl = 0; end; end; end; %end; %* If the funnel has contiguous elements: ; %* Determine whether the funnel has a break in contiguity ; if upcase("&contig") eq 'TRUE' then do; fun_cnt=0; lss_cnt=0; do i=1 to curr_hold; fun_cnt+funnel{i}; if levelss{i} gt . then lss_cnt+1; end; if fun_cnt ne lss_cnt then do; %* There has been a break in contiguity: ; %* Dump the active funnel storage area into HOLD ; if curr_hold gt max_hold then do; do j=1 to curr_hold; hold_lvl{j}=1; hold_fun{j}=1; hold_lss{j}=levelss{j}; end; max_hold=curr_hold; end; %* Re-initialize the active funnel storage area ; do i=1 to &total_events; %* LEVELx is not re-initialized -- it indicates any encounter ; funnel{i} =0; levelss{i} =.; end; end; end; if last.session_id then do; if upcase("&contig") eq 'TRUE' then do; %* move the active funnel storage area to HOLD, if necessary ; if curr_hold gt max_hold then do; do j=1 to curr_hold; hold_lvl{j}=level{j}; hold_fun{j}=funnel{j}; hold_lss{j}=levelss{j}; end; max_hold=curr_hold; end; %* write out the sessions most complete encounter with the funnel ; do i=1 to &total_events; %* LEVELx is not carried over -- it indicates any encounter ; funnel{i} =hold_fun{i}; levelss{i} =hold_lss{i}; end; end; output; end; drop i j hold_lvl1-hold_lvl&total_events hold_fun1-hold_fun&total_events hold_lss1-hold_lss&total_events max_hold curr_hold fun_cnt lss_cnt in_seq ; run; %end; %* prepare the level-oriented summaries ; proc summary data = in_f&_funnel_data_uid_ sum; var level1-level&total_events; output out=fun_f&_funnel_data_uid_.1 (drop=_type_ _freq_) sum=; run; proc transpose data=fun_f&_funnel_data_uid_.1 out =xpose_f&_funnel_data_uid_.1 (rename=(col1=sessions)); run; %* prepare the funnel-oriented summaries ; proc summary data = in_f&_funnel_data_uid_ sum; var funnel1-funnel&total_events; output out=fun_f&_funnel_data_uid_.2 (drop=_type_ _freq_) sum=; run; proc transpose data=fun_f&_funnel_data_uid_.2 out =xpose_f&_funnel_data_uid_.2 (rename=(col1=sessions2)); run; %* combine the level- and funnel-oriented summaries ; data fun_f&_funnel_data_uid_; merge xpose_f&_funnel_data_uid_.2 xpose_f&_funnel_data_uid_.1; run; data _null_; set fun_f&_funnel_data_uid_ end=eof; call symput(compress("users"|| strip(put(_n_,6.))) ,strip(put(sessions,10.))); if eof then call symput("tot",strip(put(_n_,6.))); run; data fun_f&_funnel_data_uid_.A; set fun_f&_funnel_data_uid_; if &users1 gt 0 then perc_level1 = (sessions2 / &users1) * 100; else perc_level1 = 0; run; data _null_; set fun_f&_funnel_data_uid_.A end=eof; call symput(compress("perc"|| strip(put(_n_,6.))) ,strip(put(perc_level1,6.2))); if eof then call symput("tot2",strip(put(_n_,6.))); run; data fun_f&_funnel_data_uid_.B; merge fun_f&_funnel_data_uid_.A d&_funnel_data_uid_ (keep = desc); run; data &rpt_lib..fun_f&_funnel_data_uid_; length _name_ $10 desc $128 seq sessions sessions2 perc_level1 perc_prev dropoff 8; format sessions sessions2 nlnum8. perc_prev dropoff perc_level1 nlnum6.2; set fun_f&_funnel_data_uid_.B; retain prev_sess2 lvl1_sess2; if perc_level1 le .z then perc_level1 = 0; _name_ = upcase(_name_); %* NOTE: one digit is sufficient because only 7 levels are allowed ; seq=input(substr(reverse(strip(_name_)),1,1),1.); if _n_ eq 1 then do; lvl1_sess2=sessions2; perc_prev=perc_level1; dropoff=0; end; else do; if prev_sess2 eq 0 then do; perc_prev=0; dropoff=0; end; else do; perc_prev=100*(sessions2/prev_sess2); dropoff=100-perc_prev; end; end; prev_sess2=sessions2; %* NOTE: embedded DESC single quotes were TRANWRDed into paired single ; %* quotes earlier in the program. restore them here with TRANWRD. ; desc=tranwrd(desc,"''","'"); label seq = "Level" desc = "Event" sessions = "Total Visits" sessions2 = "Path Visits" perc_level1 = "% of First Level" perc_prev = "% of Previous Level" dropoff = "% of Drop Off" ; keep seq desc sessions sessions2 perc_level1 perc_prev dropoff; run; %if &samp gt 0.5 %then %do; data &rpt_lib..fun_f&_funnel_data_uid_; set &rpt_lib..fun_f&_funnel_data_uid_(drop=sessions sessions2); label perc_level1 = "Estimated % of First Level" perc_prev = "Estimated % of Previous Level" dropoff = "Estimated % of Drop Off"; run; %end; %mend Create_Funnel; /*----------------------Web Analytics-----------------------------------------*/ /* Remove_Formats */ /* Remove WebHound/e-Data-ETL-specific formats */ /* */ /*----------------------------------------------------------------------------*/ /* Supported By: Frank Roediger, FRROED */ /* */ /*----------------------------------------------------------------------------*/ /* History: */ /* Date Description Username Change Code */ /* 20050804 Program Created frroed NA */ /* */ /*----------------------------------------------------------------------------*/ /* Dependencies: */ /* The &LIBNAME library and the &MEMNAME data set need to exist */ /* */ /* The following macros need to be available: */ /* %GET_OBSERVATION_COUNT */ /* */ /* The following macro variables need to be defined and assigned values */ /* in the invoking environment: */ /* &TEMP_LIB: the libref for the standard Web Analytics work space */ /* &WAB_NOTE: a message prefix */ /* &WAB_WARNING: a message prefix */ /* */ /*----------------------------------------------------------------------------*/ /* Parameters: */ /* LIBNAME - the libref that contains &MEMNAME */ /* MEMNAME - the &LIBNAME data set that has WebHound/e-Data-ETL- */ /* specific formats */ /* */ /*----------------------------------------------------------------------------*/ /* Input: */ /* the &LIBNAME.MEMNAME data set with WebHound/e-Data-ETL-specific formats */ /* */ /*----------------------------------------------------------------------------*/ /* Output: */ /* the &LIBNAME.MEMNAME data set without WebHound/e-Data-ETL-specific */ /* formats */ /* */ /*----------------------------------------------------------------------------*/ /* Copyright (c) 2005 SAS Institute Inc. All Rights Reserved */ /*----------------------------------------------------------------------------*/ %macro remove_formats(libname= ,memname= ); %local macname; %let macname=&sysmacroname; %if %sysfunc(exist(&libname..&memname))=0 %then %do; %put %unquote(&wab_warning) The &libname..&memname data set does %QCMPRES( not) exist and cannot be tested for e-Data-ETL-specific formats.; %goto mac_exit; %end; %******************************************************************************; %* identify the columns in LIBNAME.MEMNAME that have WebHound/e-Data-ETL- ; %* specific formats ; %******************************************************************************; proc sql; create table &temp_lib..cols_to_change as select name ,format from dictionary.columns where libname eq upcase("&libname") and memname eq upcase("&memname") and ( index(upcase(format),'$BLNK') or index(upcase(format),'$COUNTRY') or index(upcase(format),'$OTYPE') or index(upcase(format),'$UNKN') or index(upcase(format),'$WHCOMP') or index(upcase(format),'STATUS') ) order by name; quit; %******************************************************************************; %* if there are columns in LIBNAME.MEMNAME that have WebHound/e-Data-ETL- ; %* specific formats, create a list of them ; %******************************************************************************; %if %get_observation_count(indsn=&temp_lib..cols_to_change) gt 0 %then %do; data _null_; set &temp_lib..cols_to_change end=eof; length var_list $4096; retain var_list; var_list=strip(var_list) || ' ' || strip(name); if eof then call symput('wab_var_list',strip(var_list)); run; %***************************************************************************; %* use PROC DATASETS to remove the WebHound/e-Data-ETL-specific formats ; %***************************************************************************; proc datasets library=&libname nolist; modify &memname; format &wab_var_list; quit; %put %unquote(&wab_note) The following fields in &libname..&memname %QCMPRES( no) longer have e-Data-ETL-specific formats:; %put %unquote(&wab_note) &wab_var_list..; %end; %else %do; %put %unquote(&wab_note) There are no fields in &libname..&memname %QCMPRES( that) had e-Data-ETL-specific formats.; %end; %MAC_EXIT:; %mend remove_formats; %mend wautils;