/*-----------------------------------------------------------------------------*/ /* HISTORY: 8/16/04 sassyw S0261783 Fix for running multiple dashboards */ /* */ /*-----------------------------------------------------------------------------*/ /*-----------------------------------------------------------------------------*/ /* CLEAN_UP_TEMP_LIB */ /* */ /* HISTORY: 03/01/04 sassyw Initial coding */ /* */ /*-----------------------------------------------------------------------------*/ /* DESCRIPTION: This is a utility macro of %wadashdp which is used to */ /* clean up the temporary work library to prevent files */ /* that exist from previous runs from impacting the */ /* current run. */ /* */ /* */ /* PARAMETERS: NONE */ /*-----------------------------------------------------------------------------*/ %MACRO clean_up_temp_lib; /* * If temporary data sets exist from previous runs then delete them */ %IF %SYSFUNC(EXIST(&temp_lib..dashboard_metric_history,DATA))^=0 %THEN %DO; proc datasets lib=&temp_lib nolist; delete dashboard_metric_history; run; %END; %IF %SYSFUNC(EXIST(&temp_lib..trends_tmp,DATA))^=0 %THEN %DO; proc datasets lib=&temp_lib nolist; delete trends_tmp; run; %END; %IF %SYSFUNC(EXIST(&temp_lib..merged,DATA))^=0 %THEN %DO; proc datasets lib=&temp_lib nolist; delete merged; run; %END; %IF %SYSFUNC(EXIST(&temp_lib..slope,DATA))^=0 %THEN %DO; proc datasets lib=&temp_lib nolist; delete slope; run; %END; %MEND clean_up_temp_lib; /*-----------------------------------------------------------------------------*/ /* METRIC_STATS */ /* */ /* HISTORY: 03/01/04 sassyw Initial coding */ /* */ /*-----------------------------------------------------------------------------*/ /* DESCRIPTION: This is a utility macro of %wadashdp which is used to */ /* calculate the PROC MEAN descriptive statistics for the */ /* input dataset. */ /* */ /* PARAMETERS: INDSN: The name of the input dataset which proc means */ /* will be run on */ /* VAR_LIST: The list of variables within the input data */ /* set to consider in the proc means run */ /* CURRENT_DATE: The current date for which the dashboard */ /* is being processed */ /*-----------------------------------------------------------------------------*/ %MACRO metric_stats(indsn= ,var_list= ,current_date=); /* * Calculate the descriptive stats for the last 30 days */ data &temp_lib..tmpstats(keep=&var_list date); set &indsn; if date ge (¤t_date-29); run; data &temp_lib..tmpstats(drop=date); set &temp_lib..tmpstats; run; proc means data=&temp_lib..tmpstats noprint; output out=&temp_lib..meanout; run; data &temp_lib..meanout(drop=_TYPE_ _FREQ_); set &temp_lib..meanout; run; proc transpose data=&temp_lib..meanout out=&temp_lib..transpose; run; %***************************************************************************; %* make sure that the TEMP_LIB..TRANSPOSE data set has a column called ; %* _LABEL_ ; %***************************************************************************; proc sql; create table &temp_lib..label_col as select * from dictionary.columns where libname eq upcase("&temp_lib") and memname eq 'TRANSPOSE' and name eq '_LABEL_'; quit; %if %get_observation_count(indsn=&temp_lib..label_col) eq 0 %then %do; data &temp_lib..transpose; set &temp_lib..transpose; _label_=''; run; %end; data &temp_lib..metric_stats; set &temp_lib..transpose(rename=(_name_=var_name _LABEL_=label col1=n col2=min col3=max col4=mean col5=std)); label label='Metric Label' var_name='Metric Variable Name'; run; data &temp_lib..metric_stats(drop=n); set &temp_lib..metric_stats; run; %MEND metric_stats; /*-----------------------------------------------------------------------------*/ /* BUILD_DASHBOARD_FORMATS */ /* */ /* HISTORY: 03/01/04 sassyw Initial coding */ /* */ /*-----------------------------------------------------------------------------*/ /* DESCRIPTION: This is a utility macro of %wadashdp which is used to */ /* create the user defined formats which are later used */ /* to lookup information based on a metric or variable */ /* name. */ /* */ /* PARAMETERS: DASHBOARD: The name of the dashboard that is currently */ /* being processed. */ /*-----------------------------------------------------------------------------*/ %MACRO build_dashboard_formats(dashboard=); /* * Subset the config for the data on the current dashboard * being processed */ data &temp_lib..lookup; set &temp_lib..dashboard_config; if (dashboard="&dashboard") and (lowcase(_name_) ne 'date'); _name_=lowcase(_name_); run; /* * Build the business direction look up table */ %IF %SYSFUNC(EXIST(&temp_lib..busdir_look_up,DATA))^=0 %THEN %DO; proc datasets lib=&temp_lib nolist; delete busdir_look_up; run; %END; data &temp_lib..busdir_look_up; length fmtname $8; set &temp_lib..lookup(rename=(_name_=start business_dir=label)); fmtname='$busdir'; run; data &temp_lib..busdir_look_up(keep=start label fmtname); set &temp_lib..busdir_look_up; label start='Start'; label label='Label'; label fmtname='Format Name'; start=lowcase(start); run; /* * Build the format used for category translation */ proc format cntlin=&temp_lib..busdir_look_up; run; /* * Build the category look up table */ %IF %SYSFUNC(EXIST(&temp_lib..category_look_up,DATA))^=0 %THEN %DO; proc datasets lib=&temp_lib nolist; delete category_look_up; run; %END; data &temp_lib..category_look_up; length fmtname $8; set &temp_lib..lookup(rename=(_name_=start _name_category_=label)); fmtname='$cat'; run; data &temp_lib..category_look_up(keep=start label fmtname); set &temp_lib..category_look_up; label start='Start'; label label='Label'; label fmtname='Format Name'; start=lowcase(start); run; /* * Build the format used for category translation */ proc format cntlin=&temp_lib..category_look_up; run; /* * Build the label look up table */ %IF %SYSFUNC(EXIST(&temp_lib..label_look_up,DATA))^=0 %THEN %DO; proc datasets lib=&temp_lib nolist; delete label_look_up; run; %END; data &temp_lib..label_look_up; length fmtname $8; set &temp_lib..lookup(rename=(_name_=start _label_=label)); fmtname='$label'; run; data &temp_lib..label_look_up(keep=start label fmtname); set &temp_lib..label_look_up; label start='Start'; label label='Label'; label fmtname='Format Name'; start=lowcase(start); run; /* * Build the format used for label translation */ proc format cntlin=&temp_lib..label_look_up; run; %MEND build_dashboard_formats; /*-----------------------------------------------------------------------------*/ /* PROCESS_METRIC */ /* */ /* HISTORY: 03/01/04 sassyw Initial coding */ /* */ /*-----------------------------------------------------------------------------*/ /* DESCRIPTION: This is a utility macro of %wadashdp which is used to */ /* calculate the trend slope for a particular metric */ /* (variable) in the input dataset */ /* */ /* OUTPUT: &temp_lib..dashboard_metric_history */ /* &temp_lib..trend_tmp */ /* */ /* PARAMETERS: INDSN: The name of the input dataset */ /* VARNAME: The name of the variable (metric) for which */ /* calculations will be made */ /* CURRENT_DATE: The current date for which the dashboard */ /* is being processed */ /* INTERVAL: Only valid value for this release is DAY */ /* DASHBOARD: Name of the dashboard currently being */ /* processed */ /*-----------------------------------------------------------------------------*/ %macro process_metric(indsn= ,varname= ,current_date= ,interval=day ,dashboard=); /* * Make a temp copy of the input dataset passed in on INDSN and * rename the input parameter (indsn) to point to the temp copy */ data &temp_lib..process_data; set &indsn; run; %let indsn=&temp_lib..process_data; /* * Report the current macro variable values */ %let days_per_interval=; %let forecast_window=; %let trend_window=; %let plot_window=; /* * Set up default values based on input parameter INTERVAL */ %if &interval=day %then %do; %if &days_per_interval= %then %let days_per_interval=1; %if &forecast_window= %then %let forecast_window=31;/*one month*/ %if &trend_window= %then %let trend_window=7;/*one week*/ %if &plot_window= %then %let plot_window=31;/*one month*/ %let date_label=Date; %end; %else %if &interval=week %then %do; %if &days_per_interval= %then %let days_per_interval=7; %if &forecast_window= %then %let forecast_window=12;/*one quarter*/ %if &trend_window= %then %let trend_window=4;/*one month*/ %if &plot_window= %then %let plot_window=12;/*one quarter*/ %let date_label=Week Beginning; %end; %else %if &interval=month %then %do; %if &days_per_interval= %then %let days_per_interval=30;/* 30 is close enough because this is only used when appending all records not today, but still within the plotting window to a dataset holding the record for today.*/ %if &forecast_window= %then %let forecast_window=12;/*one year*/ %if &trend_window= %then %let trend_window=3;/*one quarter*/ %if &plot_window= %then %let plot_window=12;/*one year*/ %let date_label=Month Beginning; %end; /* * Make sure we keep enough data for plot, trend and forecast */ data _null_; point=1; temp1=max(&plot_window,&trend_window,&forecast_window); call symput('window',temp1); temp1=min(%get_observation_count(indsn=&indsn),&plot_window); call symput('plot_window',temp1); stop; run; %let firstobs=0; %let firstobs=%eval(%get_observation_count(indsn=&indsn)-&window+1); %if &firstobs < 1 %then %let firstobs=1; /* Get the size of the input dataset (INDSN)- This which will be used */ /* for plotting, trending and forecasting. */ %if &window < %get_observation_count(indsn=&indsn) %then %let nobs=&window; %let firstplot=0; %let firstplot=%eval(%get_observation_count(indsn=&indsn)-&plot_window+1); %if &firstplot < 1 %then %let firstplot=1; %let firsttrnd=0; %let firsttrnd=%eval(%get_observation_count(indsn=&indsn)-&trend_window+1); %if &firsttrnd < 1 %then %let firsttrnd=1; %let firstfcst=0; %let firstfcst=%eval(%get_observation_count(indsn=&indsn)-&forecast_window); %if &firstfcst < 1 %then %let firstfcst=1; /* * Run the PROC AUTOREG for the particular variable that was passed in on VARNAME */ proc autoreg data=&indsn(firstobs=&firsttrnd) outest=&temp_lib..outest outcov noprint; model &varname=date/nlag=1 method=ml backstep; output out=&temp_lib..xbeta p=pred pm=xbeta; run; /* * set slope_stderr and std_trend based on calculated slope */ data &temp_lib..trends_tmp(keep=slope std_trend slope_stderr var_name intercept); set &temp_lib..outest(rename=(intercept=intcpt)) end=eof; retain slope slope_stderr intercept; if _type_='COV' and lowcase(_name_)="date" then slope_stderr=_stderr_; if _type_='OLS' then do; slope=date; intercept=intcpt; end; if eof then do; if slope=0 and slope_stderr=0 then std_trend=0; else if slope_stderr ne 0 then std_trend=slope/slope_stderr; else std_trend=slope; var_name="&varname"; output; end; run; /* * add the identifying fields for this dashboard and the current report date */ data &temp_lib..trends_tmp; length dashboard $25; length report_date 8; format report_date date9.; set &temp_lib..trends_tmp; report_date=¤t_date; dashboard="&dashboard"; output; run; /* drop all the extra variables */ data &temp_lib..pretranspose; set &indsn(keep=&varname date); run; /* Transpose enough history for the plot for later */ proc transpose data=&temp_lib..pretranspose(firstobs=&firstplot) out=&temp_lib..dashboard_metric_history; by date; run; /* S0273228 - need to check to see if _label_ was created and if not create */ proc contents data=&temp_lib..dashboard_metric_history noprint out=dshbrd_metric_var_chk; run; data _null_; set dshbrd_metric_var_chk end=eof; retain has_label 0; if strip(lowcase(name))='_label_' then has_label=1; if eof then call symput('wab_dshbrd_metric_has_label',put(has_label,z1.)); run; data &temp_lib..dashboard_metric_history; set &temp_lib..dashboard_metric_history(rename=(_name_=var_name %if &wab_dshbrd_metric_has_label = 1 %then %do; _label_=label %end; col1=actual)); /* create if does not exist */ %if &wab_dshbrd_metric_has_label = 0 %then %do; label = var_name; %end; if strip(label)='' then label=var_name; label label='Metric Label' var_name='Metric Variable Name' actual='Actual'; run; data &temp_lib..xbeta; set &temp_lib..xbeta(rename=(xbeta=trend_line)); label trend_line='Trend Line'; run; data &temp_lib..xbeta; set &temp_lib..xbeta(keep=trend_line date); run; proc sort data=&temp_lib..xbeta; by date; run; proc sort data=&temp_lib..dashboard_metric_history; by date; run; data &temp_lib..dashboard_metric_history; merge &temp_lib..xbeta &temp_lib..dashboard_metric_history; by date; run; /* * add the itentifying fields for this dashboard and the current report date */ data &temp_lib..dashboard_metric_history; length dashboard $25; length report_date 8; format report_date date9.; label report_date='Report Date'; label dashboard='Dashboard Name'; set &temp_lib..dashboard_metric_history; report_date=¤t_date; dashboard="&dashboard"; output; run; /* * Place the metric actual value on the trends_tmp dataset */ data &temp_lib..actual(keep=var_name actual); set &temp_lib..dashboard_metric_history; if date=¤t_date; /*norm*/ var_name=lowcase(var_name); run; data &temp_lib..trends_tmp; merge &temp_lib..trends_tmp &temp_lib..actual; by var_name; run; %mend; %macro makenulldash(message=); /* * See if a null record has already been written for this dashboard * for the current_date. If it has do not write another one. If it * has not then append one to the end of &outlib.dashboard_data */ %let append=; %let dbobs=; %IF %SYSFUNC(EXIST(&outlib..dashboard_data,DATA)) %THEN %DO; data &temp_lib..checkforrec; set &outlib..dashboard_data; if (dashboard eq "&dashboard") and (report_date=¤t_date) then delete ; run; data _null_; set &outlib..dashboard_data; call symput("DBOBS",_n_); run; %END; %ELSE %DO; data &temp_lib..checkforrec; stop; run; %END; %IF %SYSFUNC(EXIST(&outlib..dashboard_data,DATA)) eq 0 %THEN %DO; %LET append=go; %END; %IF (&dbobs ge 0) or (&append=go) %THEN %DO; data &temp_lib..dashboard_temp_data; 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 ; slope=0; slope_stderr=0; intercept=0; std_trend=0; var_name=''; report_date=¤t_date; dashboard="&dashboard"; label="&message"; actual=0; min=0; max=0; mean=0; std=0; category="&message"; business_dir=' '; pvalue=0; performance=' '; run; proc append base=&outlib..dashboard_data data=&temp_lib..dashboard_temp_data; run; %END; %mend; %macro wadashdp(begin_date= ,end_date= ,indsn= ,outlib= ,definition_to_run= ,retcode= ); /*----------------------Web Analytics-----------------------------------------*/ /* Name: */ /* WADASHDP -- Web Analytics DASHboard Daily Process */ /* */ /* Purpose: */ /* Create the Web Analytics Dashboard data set (adapted v6 service code */ /* for use in SWA product). */ /* */ /*----------------------------------------------------------------------------*/ /* Supported By: */ /* Scott Wilkinson (SASSYW) */ /* Kevin Scott (SASKXS) */ /* */ /*----------------------------------------------------------------------------*/ /* History: */ /* Date Description Username Change Code */ /* 20040323 Program Created sassyw NA */ /* 20041025 S0273362: Change dashboard_to_run to definition_to_run */ /* 20041229 S0278295: Add missing day check cabahl C0001 */ /* to 30 contiguous days check, handle missing days */ /* 20060301 S0347345: Input data set for cabahl C0002 */ /* individual dashboards not reset - fixed */ /*----------------------------------------------------------------------------*/ /* Dependencies: */ /* The following macros need to be available: */ /* GET_OBSERVATION_COUNT */ /* LOCKTEST */ /* WATSTDSN */ /* */ /* The following macro variables need to be defined and assigned values */ /* in the invoking environment: */ /* &TEMP_LIB: a libref for the storage location for temporary data sets. */ /* &WAB_ERROR: the text that identifies WA macro error messages. */ /* &WAB_NOTE: the text that identifies WA macro notification messages. */ /* */ /*----------------------------------------------------------------------------*/ /* Parameters: */ /* BEGIN_DATE: (Required) SAS date value for the first date in a */ /* range of 1 or more days to process the dashboard for. */ /* END_DATE: (Optional) SAS date value for the last date in a */ /* range of 1 or more days to process the dashboard for. */ /* If no value set for this parm then the BEGIN_DATE */ /* value will be used to create a one-iteration loop. */ /* INDSN: (Optional) Name of data set containing daily metric */ /* values. If this value is not passed in then the value */ /* will be obtained from the config file. If a value is */ /* passed in then this value will override the value in */ /* the config file. */ /* CONFIG: (Optional: Default=CONFIG.WADSHBRD) Name of the data */ /* set that contains the meta data for how to build the */ /* dashboard. Multiple dashboards may be defined in */ /* this file. If no value is passed for this parameter */ /* then the default config file will be used. */ /* DEFINITION_TO_RUN:(Optional: Default=blank) If a parameter is specified */ /* for this then only that dashboard will be processed. */ /* It must be a name of a dashboard defined in the */ /* dashboard_config data set. */ /* RETCODE: Mechanism for passing Return Codes. */ /* */ /*----------------------------------------------------------------------------*/ /* Input: */ /* (begin_date= */ /* ,end_date= */ /* ,indsn= */ /* ,config= */ /* ,definition_to_run= */ /* ,retcode= */ /* ); */ /* */ /*----------------------------------------------------------------------------*/ /* Output: */ /* SUMMARY.DASHBOARD_DATA */ /* SUMMARY.DASHBOARD_METRIC_HISTORY */ /* */ /*----------------------------------------------------------------------------*/ /* Copyright (c) 2004 SAS Institute Inc. All Rights Reserved */ /*----------------------------------------------------------------------------*/ /* * ADDRESS THIS: This code is for testing only. Remove in production */ %local macname; %let macname=&sysmacroname; /* * Initialize return code; */ %let &retcode=0; /* * Announce start of parameter validation */ %PUT %UNQUOTE(&wab_note) *******************************************************; %PUT %UNQUOTE(&wab_note) Begin parameter validation; %PUT %UNQUOTE(&wab_note) *******************************************************; /*************** O U T P U T P A R M V A L I D A T I O N ****************/ /* * Set the default values if no parameter values specified * If parameters have values then verify that the libref exist */ %IF %QUOTE(&outlib)= %THEN %DO; %LET outlib=summary; %END; %ELSE %DO; /* verify that the library is defined */ %if (%sysfunc(libref(&outlib))) %then %do; %PUT %UNQUOTE(&wab_error) Library &outlib specified in OUTLIB parameter is not defined; %LET retcode=1; %GOTO ERREXIT; %end; %END; /**************** I N D S N P A R M V A L I D A T I O N *****************/ /* * Validate that the value (dataset name) passed into the macro on the INDSN * parameter exist can be opened and that it has data (rows) in it */ %IF %QUOTE(&indsn)^= %THEN %DO; %IF %SYSFUNC(EXIST(&indsn,DATA))=0 %THEN %DO; %PUT %UNQUOTE(&wab_error) Input dataset &indsn defined in macro parameter INDSN does not exist; %PUT %UNQUOTE(&wab_error) No dashboards will be processed; %LET indsn_root= ; %LET retcode=1; %GOTO ERREXIT; %END; %locktest(dset=&indsn); %if &lock_sw ne 0 %then %do; %PUT %UNQUOTE(&wab_error) Input dataset &indsn defined in macro parameter INDSN can not be opened; %PUT %UNQUOTE(&wab_error) No dashboards will be processed; %LET indsn_root= ; %LET retcode=1; %GOTO ERREXIT; %END; %IF %get_observation_count(indsn=&indsn) eq 0 %THEN %DO; %PUT %UNQUOTE(&wab_error) No rows found in input dataset %QCMPRES(&indsn) defined in macro parameter INDSN; %PUT %UNQUOTE(&wab_error) No dashboards will be processed; %LET indsn_root= ; %LET retcode=1; %GOTO ERREXIT; %END; %END; /********************** D A T E V A L I D A T I O N **************/ /* * Date is required parameter. If none passed in then ERROR */ %IF %quote(&begin_date)= %THEN %DO; %PUT %UNQUOTE(&wab_error)A Begin date parameter was not specified on the dashboard_dp macro call; %PUT %UNQUOTE(&wab_error) No dashboards will be processed; %LET retcode=1; %GOTO ERREXIT; %END; /* * If no enddate parameter value passed in then set it to the begin date * to force a 1 iteration loop */ %IF %quote(&end_date)= %THEN %DO; %LET end_date=&begin_date; %END; /* * Make sure end date is greater than begin date */ %IF &end_date lt &begin_date %THEN %DO; %PUT %UNQUOTE(&wab_error) Date range is invalid. End date must be greater than or equal to begin date; %PUT %UNQUOTE(&wab_error) No dashboards will be processed; %LET retcode=1; %GOTO ERREXIT; %END; /* * Use the date value passed in to create the _fmt_begin_date_ * and _fmt_end_date_ variables used in this code */ data _null_; tempdate=strip(put(&begin_date,Date9.)); call symput("_fmt_begin_date_",tempdate); tempdate=strip(put(&end_date,Date9.)); call symput("_fmt_end_date_",tempdate); run; /* * Do we have a valid date value (this only means that the value that * we formatted was numeric not that it is valid as far as what is * in the data warehouse) */ %IF &_fmt_begin_date_=. %THEN %DO; %PUT %UNQUOTE(&wab_error) The Begin date parameter was not a valid date value; %PUT %UNQUOTE(&wab_error) No dashboards will be processed; %LET retcode=1; %GOTO ERREXIT; %END; %IF &_fmt_end_date_=. %THEN %DO; %PUT %UNQUOTE(&wab_error) The End date parameter was not a valid date value; %PUT %UNQUOTE(&wab_error) No dashboards will be processed; %LET retcode=1; %GOTO ERREXIT; %END; /****************** C O N F I G V A L I D A T I O N ************/ /* * Set default value for the config parameter */ %LET config=config.wadshinp; /* * Make sure the config file exist, has rows, and can be opened */ %IF %SYSFUNC(EXIST(&config,DATA))=0 %THEN %DO; %PUT %UNQUOTE(&wab_error) The config file &config does not exist; %PUT %UNQUOTE(&wab_error) No dashboards will be processed; %LET retcode=1; %GOTO ERREXIT; %END; /* * Make sure we can open the config file * (may not need this since we just make a copy of it) */ %locktest(dset=&config); %if &lock_sw ne 0 %then %do; %PUT %UNQUOTE(&wab_error) Can not open config file &config; %PUT %UNQUOTE(&wab_error) No dashboards will be processed; %LET retcode=1; %GOTO ERREXIT; %END; %IF %get_observation_count(indsn=&config) eq 0 %THEN %DO; %PUT %UNQUOTE(&wab_error) No data in config file &config; %PUT %UNQUOTE(&wab_error) No dashboards will be processed; %LET retcode=1; %GOTO ERREXIT; %END; /* ADDRESS THIS: does wab_rc need to be initialized here */ /* %let wab_rc= ; %IF %quote(&config)^= %THEN %DO; %watstdsn(test_dsn=&config ,tmpl_dsn=wadshbrd ,retcode=wab_rc ); %IF &wab_rc=1 %THEN %DO; %PUT Attributes of variables in config &config are invalid; %LET retcode=1; %GOTO ERREXIT; %END; %END; */ /* * Validation PASSED - Announce start of processing */ %PUT %UNQUOTE(&wab_note) *******************************************************; %PUT %UNQUOTE(&wab_note) Parameter validation passed; %PUT %UNQUOTE(&wab_note) *******************************************************; %PUT %UNQUOTE(&wab_note) Beginning execution of macro WADASHDP with parameters:; %PUT %UNQUOTE(&wab_note) BEGIN_DATE =&_fmt_begin_date_; %PUT %UNQUOTE(&wab_note) END_DATE =&_fmt_end_date_; %PUT %UNQUOTE(&wab_note) INDSN =&indsn; %PUT %UNQUOTE(&wab_note) CONFIG =&config; %PUT %UNQUOTE(&wab_note) *******************************************************; %***************************************************************************; %* Join WADSHBRD and WADSHINP to get complete Dashboard metadata ; %***************************************************************************; proc sql; create table &temp_lib..dashboard_config as select l.time_span , l.role , l._name_ , l._name_category_ , l._label_ , l.business_dir , r.name as dashboard , r.indsn , r.etl_run_sw from &config l, config.wadshbrd r where l.dashboard_id eq r.id; quit; /* * Make a copy of the orig config file name to reset to the original dashboard config */ /* S0261783: F I X problem with running multiple dashboard definitions */ %LET config_root=&temp_lib..dashboard_config; /*%LET config_root=&config;*/ /***********************************************************/ /* Code to loop for each dashboard name in the config file */ /***********************************************************/ %***************************************************************************; %* Figure out how many dashboards are defined in the metadata ; %***************************************************************************; proc sort data=&temp_lib..dashboard_config out =&temp_lib..sclist (keep=dashboard etl_run_sw) nodupkey; by dashboard; run; /* * If no observations in this data set then we did not find any * dashboard definitions */ %IF %get_observation_count(indsn=&temp_lib..sclist) eq 0 %THEN %DO; %PUT %UNQUOTE(&wab_error) No dashboard names found in config file &config; %PUT %UNQUOTE(&wab_error) No dashboards will be processed; %LET retcode=1; %GOTO ERREXIT; %END; /* * Count the observations and store the number of dashboards * in macro variable */ %let ndashboards=%get_observation_count(indsn=&temp_lib..sclist); %PUT %UNQUOTE(&wab_note) %QCMPRES(&ndashboards) dashboard definitions found in config file &config; %***************************************************************************; %* Use the argument for the DEFINITION_TO_RUN parameter to control the ; %* selection of which dashboards to run. ; %***************************************************************************; %if %quote(&definition_to_run) eq %str() or %quote(%upcase(&definition_to_run)) eq _ALL_ %then %do; %let ndashboards=%get_observation_count(indsn=&temp_lib..sclist); %put %unquote(&wab_note) DEFINITION_TO_RUN parameter value %QCMPRES(will select) all &ndashboards dashboards.; %if &ndashboards eq 0 %then %do; %put %unquote(&wab_error) No dashboard will be processed.; %let retcode=1; %goto ERREXIT; %end; %end; %else %if %quote(%upcase(&definition_to_run)) eq _ETL_ %then %do; data &temp_lib..sclist; set &temp_lib..sclist; if etl_run_sw; run; %let ndashboards=%get_observation_count(indsn=&temp_lib..sclist); %put %unquote(&wab_note) DEFINITION_TO_RUN parameter value %QCMPRES(will limit) processing to &ndashboards ETL dashboards.; %if &ndashboards eq 0 %then %do; %put %unquote(&wab_error) No dashboard will be processed.; %let retcode=1; %goto ERREXIT; %end; %end; %else %if %quote(%upcase(&definition_to_run)) eq _NON_ETL_ %then %do; data &temp_lib..sclist; set &temp_lib..sclist; if not etl_run_sw; run; %let ndashboards=%get_observation_count(indsn=&temp_lib..sclist); %put %unquote(&wab_note) DEFINITION_TO_RUN parameter value %QCMPRES(will limit) processing to &ndashboards non-ETL dashboards.; %if &ndashboards eq 0 %then %do; %put %unquote(&wab_error) No dashboard will be processed.; %let retcode=1; %goto ERREXIT; %end; %end; %else %do; %************************************************************************; %* identify each of the requested dashboards in DEFINITION_TO_RUN ; %************************************************************************; data &temp_lib..def_list; length defs $ 5000; length dashboard $ 25; defs="&definition_to_run"; do while(defs gt ' '); dashboard=scan(defs,1,','); output; defs=strip(tranwrd(defs,strip(dashboard),' ')); if substr(defs,1,1) eq ',' then defs=strip(substr(defs,2)); end; keep dashboard; run; %************************************************************************; %* m/m DEF_LIST with SCLIST to establish the metadata that is for only ; %* the requested dashboards ; %************************************************************************; proc sort data=&temp_lib..def_list; by dashboard; run; data &temp_lib..sclist only_a only_b; merge &temp_lib..sclist (in=in_a) &temp_lib..def_list (in=in_b); by dashboard; if in_a then if in_b then output &temp_lib..sclist; else output only_a; else output only_b; run; %let ndashboards=%get_observation_count(indsn=&temp_lib..sclist); %put %unquote(&wab_note) DEFINITION_TO_RUN parameter value %QCMPRES(will limit) processing to &ndashboards selected dashboards.; %if %get_observation_count(indsn=only_b) ne 0 %then %do; %put %unquote(&wab_error) There are Dashboard names in the %QCMPRES( DEFINTION_TO_RUN) argument -- &definition_to_run -- %QCMPRES( that) do not have any metadata.; %let retcode=1; %goto ERREXIT; %end; %if &ndashboards eq 0 %then %do; %put %unquote(&wab_error) No dashboard will be processed.; %let retcode=1; %goto ERREXIT; %end; %end; /* * Check to see if the &outlib..dashboard_data &outlib..dashboard_metric_history * file exist but have no observations * This can happen if there was an initial failure in the running of * the dashboard code. If it is there and has no obs then clean it out; */ %if %sysfunc(exist(&outlib..dashboard_data)) ne 0 %then %do; %locktest(dset=&outlib..dashboard_data); %if &lock_sw eq 0 %then %do; %IF %get_observation_count(indsn=&outlib..dashboard_data) eq 0 %THEN %DO; %PUT %UNQUOTE(&wab_note) summary.dashboard exists but has no observations; %PUT %UNQUOTE(&wab_note) Deleting summary.dashboard; proc datasets lib=summary nolist; delete dashboard_data; run; %END; %END; %end; %if %sysfunc(exist(&outlib..dashboard_metric_history)) ne 0 %then %do; %locktest(dset=&outlib..dashboard_metric_history); %if &lock_sw eq 0 %then %do; %IF %get_observation_count(indsn=&outlib..dashboard_metric_history) eq 0 %THEN %DO; %PUT %UNQUOTE(&wab_note) &outlib..dashboard_metric_history exists but has no observations; %PUT %UNQUOTE(&wab_note) Deleting &outlib..dashboard_metric_history; proc datasets lib=summary nolist; delete dashboard_metric_history; run; %END; %END; %end; /* D A T E R A N G E L O O P */ %DO current_date=&begin_date %to &end_date; /* * Clean out the temp library because we will be doing a lot of appending * and we want to make sure we do not append anything that is hanging * around in temp_lib on initial pass */ %clean_up_temp_lib; /* * Build the formatted date value for current_date used for display */ data _null_; /*tempdate=strip(put(¤t_date,yymmddn8.));*/ tempdate=strip(put(¤t_date,Date9.)); call symput("_fmt_current_date_",tempdate); run; /* D A S H B O A R D L I S T L O O P */ %DO sc=1 %to &ndashboards; /* * Reset dashboard input data set to either the specified data set * or blank to be set later. */ %if &indsn ne %str() %then %let dsh_indsn=&indsn; %else %let dsh_indsn=; data _null_; point=≻ set &temp_lib..sclist point=point; call symput('dashboard',trim(dashboard)); stop; run; /* * Clean out the temp library because we will be doing a lot of appending * and we want to make sure we do not append anything that is hanging * around in temp_lib on initial pass */ %clean_up_temp_lib; /* * Call the build_dashboard_formats macro which will result in defining * $cat which maps a metric name to a category =put(_name_,$cat.); * $lable which maps a metric name to its lable =put(_name_,$label.); */ %build_dashboard_formats(dashboard=&dashboard); /* * Get the time span value from the config for this dashboard */ /* Only going to support daily dashboards in this release data _null_; set &config; if dashboard="&dashboard"; if indsn ne '' then do; call symput('time_span',time_span); stop; end; else do; call symput('time_span',' '); end; run; */ /* Only going to support daily dashboards in this release */ %let time_span=day; /* * Address this:Validate time span (day week month qtr year) */ /* code goes here */ /* * If no input data set name (indsn) passed in then * go get it out of the dashboard config file */ %IF %quote(&dsh_indsn)= %THEN %DO; data &temp_lib..checkindsn; set &temp_lib..dashboard_config; if dashboard="&dashboard" and indsn ne ''; run; /* * If no observations meet above condition then we can not determine * the input dataset to use. Give error and skip to next dashboard */ %IF %get_observation_count(indsn=&temp_lib..checkindsn) eq 0 %THEN %DO; %PUT %UNQUOTE(&wab_warning) No input dataset defined in config file &config for &dashboard; %PUT %UNQUOTE(&wab_warning) &dashboard dashboard will not be processed; %LET indsn_root= ; %GOTO FORWARD; %END; /* * If there is a definition in the config then extract it */ data _null_; set &temp_lib..dashboard_config; if dashboard="&dashboard"; if indsn ne '' then do; call symput('dsh_indsn',indsn); stop; end; else do; call symput('dsh_indsn',' '); end; run; %END; /* * Validate input data set value */ /*********** DASHBOARD I N D S N C O N F I G V A L U E V A L I D A T I O N ************/ %IF %quote(&dsh_indsn)= %THEN %DO; %PUT %UNQUOTE(&wab_warning) Input dataset value for &dashboard not found in config file &config; %PUT %UNQUOTE(&wab_warning) &dashboard dashboard will not be processed; %LET indsn_root= ; %GOTO FORWARD; %END; %IF %SYSFUNC(EXIST(&dsh_indsn,DATA))=0 %THEN %DO; %PUT %UNQUOTE(&wab_warning) Input dataset &dsh_indsn defined for &dashboard dashboard in config file &config does not exist; %PUT %UNQUOTE(&wab_warning) &dashboard dashboard will not be processed; %LET indsn_root= ; %GOTO FORWARD; %END; %locktest(dset=&dsh_indsn); %if &lock_sw ne 0 %then %do; %PUT %UNQUOTE(&wab_error) Input dataset &dsh_indsn defined in macro parameter INDSN can not be opened; %PUT %UNQUOTE(&wab_error) No dashboards will be processed; %LET indsn_root= ; %LET retcode=1; %GOTO ERREXIT; %END; %IF %get_observation_count(indsn=&dsh_indsn) eq 0 %THEN %DO; %PUT %UNQUOTE(&wab_warning) No rows found in input dataset %QCMPRES(&dsh_indsn) defined for &dashboard dashboard in config file &config; %PUT %UNQUOTE(&wab_warning) &dashboard dashboard will not be processed; %LET indsn_root= ; %GOTO FORWARD; %END; /* * Make sure the input data set for dashboard is moved into a temporary * work location because we may have to remove records at the end if the * maxdate for the data is beyond the day we are processing a dashboard for */ data &temp_lib..dashboard_input; set &dsh_indsn; run; /* * Keep a copy of the name of the original input data set name */ %let indsn_root=&dsh_indsn; /* * Make sure it can be opened */ %locktest(dset=&temp_lib..dashboard_input); %if &lock_sw ne 0 %then %do; %PUT %UNQUOTE(&wab_warning) Input data set &temp_lib..dashboard_input could not be opened; %PUT %UNQUOTE(&wab_warning) &dashboard dashboard will not be processed; %LET indsn_root= ; %GOTO FORWARD; %END; /* * Validate that there is a date variable in the input dataset */ proc contents data=&dsh_indsn out=&temp_lib..checkdatevar noprint; run; data &temp_lib..checkdatevar; set &temp_lib..checkdatevar; if lowcase(name) in ('date'); if type = 1; run; %IF %get_observation_count(indsn=&temp_lib..checkdatevar) lt 1 %THEN %DO; %PUT %UNQUOTE(&wab_warning) No date variable named DATE found in input dataset &indsn; %PUT %UNQUOTE(&wab_warning) &dashboard dashboard will not be processed; %LET indsn_root= ; %GOTO FORWARD; %END; /* * Validate that there are no missing days in the input data set * for the days previous to the current processing date */ /*01Jun04:Enhance continous date check - can not be missing days */ /* in data previous to the current processing date */ /*29Dec04:Handling missing days with extrapolated data /* proc sort data=&temp_lib..dashboard_input out=&temp_lib..dashboard_input; by date; run; data &temp_lib..continousdate; set &temp_lib..dashboard_input; if (date le ¤t_date); run; continousdate was dashboard_input data &temp_lib..date_missing; set &temp_lib..continousdate; datedif=dif(date); if (datedif ne 1) and (_n_ ne 1) then do; output; end; run; %IF %get_observation_count(indsn=&temp_lib..date_missing) ge 1 %THEN %DO; %PUT %UNQUOTE(&wab_warning) Missing day(s) found in history records used for trending; %PUT %UNQUOTE(&wab_warning) In order to process the dashboard for &_fmt_current_date_ there must be no; %PUT %UNQUOTE(&wab_warning) missing days in &indsn_root previous to &_fmt_current_date_ ; %PUT %UNQUOTE(&wab_warning) Updating &dashboard dashboard with a record indicating this problem; %PUT %UNQUOTE(&wab_warning) When these missing days age out this dashboard will run again; %PUT %UNQUOTE(&wab_warning) The missing date(s) are:; data _null_; set &temp_lib..date_missing; format missing_date Date9.; missing_date=date-1; put missing_date=; run; %MAKENULLDASH(message=MISSING DAYS-NO REGRESSION); %LET indsn_root= ; %GOTO FORWARD; %END; */ /* S0278295 - handle missing days, extrapolation by using the previous week's weekday values for the missing day - J. Brocklebank */ proc sort data=&temp_lib..dashboard_input out=&temp_lib..dashboard_input; by date; run; data date_template; set &temp_lib..dashboard_input(keep=date obs=1 rename=(date=min)); current_date=¤t_date; do date=min to current_date; output; end; run; proc sort data=date_template; by date; run; data &temp_lib..date_missing; merge &temp_lib..dashboard_input(in=a keep=date) date_template(in=b); by date; if b and not a then do; output; end; run; /*----------------------------------------------------------------* * If there are missing dates *----------------------------------------------------------------*/ %if %get_observation_count(indsn=&temp_lib..date_missing) ge 1 %THEN %DO; proc sql; create table &temp_lib..date_missing_replace as select * from &temp_lib..dashboard_input where date in (select intnx('day',date,-7) from &temp_lib..date_missing); quit; data &temp_lib..dashboard_input(drop=replace_date); set &temp_lib..dashboard_input &temp_lib..date_missing_replace(in=b rename=(date=replace_date)); if b then date=intnx('day',replace_date,7); run; proc sort data=&temp_lib..dashboard_input; by date; run; %end; /* * Announce start of each individual dashboard */ %PUT %UNQUOTE(&wab_note) ***************************************************************************; %PUT %UNQUOTE(&wab_note) BEGIN DATA PROCESSING FOR DASHBOARD: &dashboard ; %PUT %UNQUOTE(&wab_note) PROCESSING DATE =&_fmt_current_date_; %PUT %UNQUOTE(&wab_note) INPUT DATA SET =&indsn_root; %PUT %UNQUOTE(&wab_note) CONFIGURATION FILE =&config_root; %PUT %UNQUOTE(&wab_note) ***************************************************************************; /* * Bring the config file into &temp_lib subsetting * so that only the needed info for the dashboard being processed * is in the file */ /* S0261783 F I X: 9.1.3d Bug Fix Old version was subsetting dashboard_config in place which meant that all but the first dashboard definition would be lost. Changed to use the dashboard_config_final data set so that the original definition information is not lost. So in the case where multiple dashboards are defined in the config file they will be processed */ data &temp_lib..dashboard_config_final; set &temp_lib..dashboard_config; if (dashboard="&dashboard") and (lowcase(role) eq "metric") and (lowcase(_name_) ne "date"); run; %IF %get_observation_count(indsn=&temp_lib..dashboard_config_final) eq 0 %THEN %DO; %PUT %UNQUOTE(&wab_warning) No metric variables defined in &config for &dashboard dashboard; %PUT %UNQUOTE(&wab_warning) At least one metric (measurement) variable must be defined; %PUT %UNQUOTE(&wab_warning) Dashboard: &dashboard will not be processed; %LET indsn_root= ; %GOTO FORWARD; %END; /* * Make a copy of the entire config for this dashboard so we * can check later to make sure all the vars in the input dataset * are defined in the config file */ /* S0261783 F I X: 9.1.3d Change This code is not needed because we never use root_config anywhere else in the code */ /* data &temp_lib..root_config; set &temp_lib..dashboard_config; if (lowcase(dashboard)=lowcase("&dashboard")); run; */ /* * Verify that the day to process exist in the input dataset */ data &temp_lib..checkforday; set &temp_lib..dashboard_input; if date eq ¤t_date; run; /* 9.1.3d Bug Fix: error msg did read scorecard instead of dashboard when running against a data set that does not have a row of data for the processing date */ %IF %get_observation_count(indsn=&temp_lib..checkforday) ne 1 %THEN %DO; %PUT %UNQUOTE(&wab_warning) No data found for processing date: &_fmt_current_date_; %PUT %UNQUOTE(&wab_warning) &dashboard dashboard will not be processed; %PUT %UNQUOTE(&wab_warning) Updating &dashboard dashboard with a record indicating this problem; %MAKENULLDASH(message=MISSING DAY IN DATA); %LET indsn_root= ; %GOTO FORWARD; %END; /* * Need to make sure there are no observations in the indsn beyond * the date we are processing. At the bottom of the date-range loop * the original input dataset will replace the temp copy so that further * date processing can take place. */ /* at this point &indsn holds the value &temp_lib..dashboard_input which is a copy of the input dataset passed in or defined in the config */ /* dashboard issue: Not sure this is needed for the dashboard like it was in the sc */ data &temp_lib..dashboard_input; set &temp_lib..dashboard_input; if date le ¤t_date; run; /* NEED TO PROTECT AGAINST PROCESSING WITHOUT ENOUGH DATA. 2 LITTLE DATA GIVES IRRELAVENT MODELS AND MAY CAUSE FAILURE. GENERALLY, WE NEED 2 WEEKS TO MAKE A DECENT MODEL. SINCE WE OMIT THE FIRST 15 DAYS TO AVOID A LINEAR TREND IN THE RESPONSE, WE WILL LOOK FOR 30 DAYS TOTAL BEFORE PROCESSING. */ %IF %get_observation_count(indsn=&temp_lib..dashboard_input) ge 30 %THEN %DO; /* * Clean up summary data based on current processing date * * Remove any records for a date greater than the current processing * date in the accumulated data for the dashboard and metric history. * This is done because if we are processing a date back in time then * that invalidates all data for days after that process date. */ %IF %SYSFUNC(EXIST(&outlib..dashboard_data,DATA)) %THEN %DO; data &temp_lib..recstodelete; set &outlib..dashboard_data; if (report_date ge ¤t_date)and (lowcase(dashboard)=lowcase("&dashboard")); run; %IF %GET_OBSERVATION_COUNT(indsn=&temp_lib..recstodelete) gt 0 %THEN %DO; %PUT %UNQUOTE(&wab_note) ***********************************************************************************; %PUT %UNQUOTE(&wab_note) RECORDS WITH REPORT DATE GREATER THAN CURRENT; %PUT %UNQUOTE(&wab_note) PROCESSING DATE (&_fmt_current_date_) FOUND IN DATA SET:; %PUT %UNQUOTE(&wab_note) &outlib..dashboard_data; %PUT %UNQUOTE(&wab_note) THESE RECORDS WILL BE DELETED BECAUSE THEY ARE NO LONGER VALID; %PUT %UNQUOTE(&wab_note) ************************************************************************************; %END; data &outlib..dashboard_data; set &outlib..dashboard_data; if (report_date ge "¤t_date") and (lowcase(dashboard)=lowcase("&dashboard")) then delete; run; %END; %IF %SYSFUNC(EXIST(&outlib..dashboard_metric_history,DATA))^=0 %THEN %DO; data &temp_lib..recstodelete; set &outlib..dashboard_metric_history; if (report_date ge ¤t_date)and (lowcase(dashboard)=lowcase("&dashboard")); run; %IF %GET_OBSERVATION_COUNT(indsn=&temp_lib..recstodelete) gt 0 %THEN %DO; %PUT %UNQUOTE(&wab_note) ***********************************************************************************; %PUT %UNQUOTE(&wab_note) RECORDS WITH REPORT DATE GREATER THAN CURRENT; %PUT %UNQUOTE(&wab_note) PROCESSING DATE (&_fmt_current_date_) FOUND IN HISTORY DATA SET:; %PUT %UNQUOTE(&wab_note) &outlib..dashboard_metric_history; %PUT %UNQUOTE(&wab_note) THESE RECORDS WILL BE DELETED BECAUSE THEY ARE NO LONGER VALID; %PUT %UNQUOTE(&wab_note) ************************************************************************************; %END; data &outlib..dashboard_metric_history; set &outlib..dashboard_metric_history; if (report_date ge ¤t_date)and (lowcase(dashboard)=lowcase("&dashboard")) then delete; run; %END; /* * Build the list of metrics from the config to loop through */ data &temp_lib..vars_to_process(keep=_name_); set &temp_lib..dashboard_config_final; if (dashboard="&dashboard") and (lowcase(role)='metric'); _name_=lowcase(_name_); run; /* * Filter out any date variable so it is not processed for * dashboard trend and slope */ data &temp_lib..vars_to_process; set &temp_lib..vars_to_process; if lowcase(_name_)='date' then delete; run; %let var_list=; %DO i=1 %to %get_observation_count(indsn=&temp_lib..vars_to_process); data _null_; point=&i; set &temp_lib..vars_to_process point=point; call symput('var',_name_); stop; run; %let var_list=&var_list &var; %let var_list=%lowcase(&var_list); %END; /* * Calculate the min max mean etc for the metrics in input dataset */ %metric_stats(indsn=&temp_lib..dashboard_input ,var_list=&var_list ,current_date=¤t_date); %DO i=1 %to %get_observation_count(indsn=&temp_lib..vars_to_process); data _null_; point=&i; set &temp_lib..vars_to_process point=point; call symput('var',_name_); stop; run; %IF %lowcase(&var) ^=date %THEN %DO; %process_metric(indsn=&temp_lib..dashboard_input ,varname=&var ,current_date=¤t_date ,interval=&time_span ,dashboard=&dashboard); %END; /* * If the &outlib..dashboard_data data set does not exist then build a standard * header so that we can perform a clean proc append */ %IF %SYSFUNC(EXIST(&outlib..dashboard_data,DATA))=0 %THEN %DO; data &outlib..dashboard_data; 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 label $64 category $45 business_dir $5 pvalue 8 performance $15; format report_date date9. std_trend nlnum15.2 ; stop; run; %END; /* * Standardize the output slope dataset to have matching header * to prevent proc append failure */ data &temp_lib..trends_tmp; length slope 8 slope_stderr 8 intercept 8 std_trend 8 var_name $64 report_date 8 dashboard $25; set &temp_lib..trends_tmp; run; /* * address this: need to check to see if this is first append because it will pick * up any pre existing worklib.dashboard_metric_history that happens to be * there from a prev run */ proc append base = &temp_lib..slope data = &temp_lib..trends_tmp; run; /* * If the &outlib..dashboard_metric_history data set does not exist * then build a standard header so that we can perform a clean proc append */ %IF %SYSFUNC(EXIST(&outlib..dashboard_metric_history,DATA))=0 %THEN %DO; data &outlib..dashboard_metric_history; length date 8 var_name $64 label $64 actual 8 trend_line 8 report_date 8 dashboard $25 category $45; format date date9.; format report_date date9.; label date='Date'; label var_name='Metric Variable Name'; label actual='Actual'; stop; run; %END; /* * Standardize the output dashboard_metric_history dataset to have matching header * to prevent proc append failure */ data &temp_lib..dashboard_metric_history; length date 8 trend_line 8 var_name $64 label $64 actual 8 category $45; set &temp_lib..dashboard_metric_history; run; proc append base = &outlib..dashboard_metric_history data = &temp_lib..dashboard_metric_history; run; %END; /* %DO i=1 %to %get_observation_count(indsn=&temp_lib..vars_to_process); */ /* merge the slope dataset with the metric stats dataset */ data &temp_lib..slope; set &temp_lib..slope; var_name=lowcase(var_name); run; proc sort data=&temp_lib..slope; by var_name; run; data &temp_lib..metric_stats; length var_name $64; set &temp_lib..metric_stats; var_name=lowcase(var_name); run; proc sort data=&temp_lib..metric_stats; by var_name; run; data &temp_lib..merged(drop=label); merge &temp_lib..metric_stats &temp_lib..slope; by var_name; run; /* standardize merged for clean append to &outlib..dashboard_data */ data &temp_lib..merged; length label $64 category $45 business_dir $5 pvalue 8 performance $15; set &temp_lib..merged; run; /* append the merged dataset to the &outlib..dashboard_data dataset */ proc append base=&outlib..dashboard_data data=&temp_lib..merged; run; data &outlib..dashboard_metric_history; set &outlib..dashboard_metric_history; length category $45; length label $64; if dashboard="&dashboard" and report_date=¤t_date then do; category=put(lowcase(var_name),$cat.); label=put(lowcase(var_name),$label.); end; run; data &outlib..dashboard_data; set &outlib..dashboard_data; length category $45; length label $64; length business_dir $5; if dashboard="&dashboard" and report_date=¤t_date then do; category=put(var_name,$cat.); label=put(var_name,$label.); business_dir=put(var_name,$busdir.); end; run; /* Calculate the metrics pvalues and performance */ /* df - Degrees of Freedom */ %IF %lowcase(&time_span)=day %THEN %let df=31; %IF %lowcase(&time_span)=week %THEN %let df=12; %IF %lowcase(&time_span)=month %THEN %let df=12; /* data &outlib..dashboard_data; set &outlib..dashboard_data; if dashboard="&dashboard" and report_date=¤t_date then do; if lowcase(business_dir)='down' then pvalue=probt(std_trend,&df-3); if lowcase(business_dir)='up' then pvalue=1-probt(std_trend,&df-3); if (pvalue lt .2) then performance='Positive'; else if pvalue gt .8 then performance='Negative'; else performance='Steady'; end; run; */ /* c0001 */ /*********************************************/ /* The icon mappings are as follows: /* /* trend up desired up then aboveGood.gif /* trend down desired up then belowBad.gif /* trend up desired down aboveBad.gif /* trend down desired down belowGood.gif /*********************************************/ data &outlib..dashboard_data; set &outlib..dashboard_data; if dashboard="&dashboard" and report_date=¤t_date then do; if lowcase(business_dir)='down' then pvalue=probt(std_trend,&df-3); if lowcase(business_dir)='up' then pvalue=1-probt(std_trend,&df-3); if lowcase(business_dir)='down' then do; if (pvalue lt .2) then performance='BelowGood'; else if (pvalue gt .8) then performance='AboveBad'; else performance='Steady'; end; else if lowcase(business_dir)='up' then do; if (pvalue gt .8) then performance='BelowBad'; else if (pvalue lt .2) then performance='AboveGood'; else performance='Steady'; end; end; run; /* * Add informative labels for presentation as well as decimal limiting formats */ data &outlib..dashboard_data; label actual='Value' business_dir='Desired Direction' category='Category' dashboard='Dashboard' intercept='Intercept' label='Metric' max='30 Day Maximum' mean='30 Day Average' min='30 Day Minimum' performance='Performance' pvalue='Performance Significance' slope='Slope' std='Standard Deviation' std_trend='Standard Trend' var_name='Key Performance Indicator' ; format actual nlnum15.2 min nlnum15.2 max nlnum15.2 mean nlnum15.2 std nlnum15.2 std_trend nlnum15.2 pvalue nlnum12.3; set &outlib..dashboard_data; run; data &outlib..dashboard_metric_history; label trend_line='Trend Line'; format actual nlnum15. trend_line nlnum15.2; set &outlib..dashboard_metric_history; /*norm*/ var_name=lowcase(var_name); run; %END; /* number of obs ge 30 in input data set */ %ELSE %DO; /* * Write out a null line to &outlib..dashboard_data for this dashboard * indicating that for the process day there were not enough days of * detail */ %makenulldash(message=NOT ENOUGH PREVIOUS DAYS OF DATA); /* * Announce not enough detail data to run dashboard for report_date */ %PUT %UNQUOTE(&wab_note) ***********************************************************************************; %PUT %UNQUOTE(&wab_warning) AT LEAST 30 DAYS OF HISTORY DATA REQUIRED TO PROCESS DASHBOARD; %PUT %UNQUOTE(&wab_warning) NOT ENOUGH PREVIOUS DAYS OF DATA IN:; %PUT %UNQUOTE(&wab_warning) &indsn_root; %PUT %UNQUOTE(&wab_warning) FOR THE PROCESSING DATE OF:; %PUT %UNQUOTE(&wab_warning) &_fmt_current_date_; %PUT %UNQUOTE(&wab_warning) A RECORD HAS BEEN WRITTEN TO &outlib..dashboard_data; %PUT %UNQUOTE(&wab_warning) REFLECTING THIS PROBLEM; %PUT %UNQUOTE(&wab_note) ************************************************************************************; %END; %FORWARD: %END; /* Outside loop controling one iteration per dashboard defined in config file */ /* * Reset the root input data set for further date range processing. In the beginning * of this loop we trim off any records beyond the current process date so they will * not be considered in the forecasting. Now we have to reset all the dates so on * the next iteration we pick up the input data for the next day. */ %IF %QUOTE(&indsn_root)^= %THEN %DO; data &temp_lib..dashboard_input; set &indsn; run; %END; %END; /* Outside date range loop*/ /* * Check the output data sets for records that are older than the * number of cut off days specified in the wbconfig file. This * is done to manage the size of the accumulated data in * &outlib..dashboard_data and &outlib..dashboard_metric_history */ %IF %SYSFUNC(EXIST(&outlib..dashboard_data,DATA)) %THEN %DO; data &temp_lib..checkrange; set &outlib..dashboard_data; if report_date lt (¤t_date-&wab_days_in_dashboard); run; %IF %get_observation_count(indsn=&temp_lib..checkrange) gt 0 %THEN %DO; %PUT %UNQUOTE(&wab_note) *******************************************************; %PUT %UNQUOTE(&wab_note) Records outside the cutoff date range found in:; %PUT %UNQUOTE(&wab_note) &outlib..dashboard_data; %PUT %UNQUOTE(&wab_note) Number of days to keep in Dashboard is: &wab_days_in_dashboard; %PUT %UNQUOTE(&wab_note) This can be changed by using WAB_DAYS_IN_DASHBOARD in the; %PUT %UNQUOTE(&wab_note) WBCONFIG file.; %PUT %UNQUOTE(&wab_note) *******************************************************; %END; data &outlib..dashboard_data; set &outlib..dashboard_data; if report_date ge (¤t_date-&wab_days_in_dashboard); run; %END; %IF %SYSFUNC(EXIST(&outlib..dashboard_metric_history,DATA)) %THEN %DO; data &temp_lib..checkrange; set &outlib..dashboard_metric_history; if report_date lt (¤t_date-&wab_days_in_dashboard); run; %IF %get_observation_count(indsn=&temp_lib..checkrange) gt 0 %THEN %DO; %PUT %UNQUOTE(&wab_note) *******************************************************; %PUT %UNQUOTE(&wab_note) Records outside the cutoff date range found in:; %PUT %UNQUOTE(&wab_note) &outlib..dashboard_metric_history; %PUT %UNQUOTE(&wab_note) Number of days to keep in Dashboard is: &wab_days_in_dashboard; %PUT %UNQUOTE(&wab_note) This can be changed by using WAB_DAYS_IN_DASHBOARD in the; %PUT %UNQUOTE(&wab_note) WBCONFIG file.; %PUT %UNQUOTE(&wab_note) *******************************************************; %END; data &outlib..dashboard_metric_history; set &outlib..dashboard_metric_history; if report_date ge (¤t_date-&wab_days_in_dashboard); run; %END; %ERREXIT: /* * Announce end of macro */ %PUT %UNQUOTE(&wab_note) *******************************************************; %PUT %UNQUOTE(&wab_note) Ending execution of macro WADASHDP with parameters:; %PUT %UNQUOTE(&wab_note) BEGIN_DATE =&_fmt_begin_date_; %PUT %UNQUOTE(&wab_note) END_DATE =&_fmt_end_date_; %PUT %UNQUOTE(&wab_note) INDSN =&indsn; %PUT %UNQUOTE(&wab_note) CONFIG =&config; %PUT %UNQUOTE(&wab_note) *******************************************************; %mend wadashdp;