/*********************************************************************/ /* HISTORY: 02/01/04 sassyw adapted v6 service code for use in */ /* SWA product */ /*********************************************************************/ /* %automatic_forecast( indsn, */ /* outdsnb=, */ /* variables=, */ /* confidence_coeff=, */ /* date=, */ /* lead=, */ /* nooutall=, */ /* expand=, */ /* graphdsnb=, */ /* datamodel=, */ /* targets=, */ /* inputs=, */ /* target_goal=, */ /* meanout=, */ /* solveout= */ /* ) */ /* */ /* */ /* Description: */ /* */ /* Forecast daily actual values and produce output */ /* data set containing forecasts and prediction intervals */ /* */ /* Parameters: */ /* */ /* indsn: input data set */ /* outdsnb: output data set for business scorecard */ /* graphdsnb: graph data set for business scorecard */ /* variables: list of all variables to forecast, if none is */ /* provided than _all_ will be used excluding */ /* the date variable */ /* confidence_coeff: confidence coefficient, range 0->1 */ /* 0.10 produces 90% confidence intervals */ /* default is 0.10 */ /* lead: number of periods to extrapolate */ /* date: SAS date variable */ /* print: prints the PROC TREND output */ /* nooutall: YES OR NO to include all forecasts */ /* expand: YES or NO to fix embedded missing values */ /* default is YES */ /*********************************************************************/ %macro goal_seek(target_goal= ,inputs= ,outdsnb= ,retcode= ,outsolve=&solveout); %local macname; %let macname=&sysmacroname; %let &retcode=0; %let ninputs = 1; %let name = %scan(&inputs,&ninputs); %do %while(&name ne ); %let ninputs = %eval(&ninputs+1); %let name = %scan(&inputs,&ninputs); %end; %let ninputs=%eval(&ninputs-1); proc transpose data=&outdsnb out=&temp_lib..goal(keep=&inputs &target_goal); var ACTUAL; id _NAME_; run; proc means data=&temp_lib..daily_stats max noprint; var date; output out=&temp_lib.._wab_gsds_max_date max=max_date; run; data _null_; set &temp_lib.._wab_gsds_max_date; call symput("_wab_max_gsds_date_",max_date); run; proc model data=&temp_lib..daily_stats noprint outmodel=&temp_lib..model noprint; &target_goal=b0 %do i=1 %to &ninputs; + b&i * %scan(&inputs,&i) %end; ; *bounds b1-b&ninputs >0; %ar(&target_goal,7); fit &target_goal/outest=&temp_lib..outest_model maxiter=1000 method=marquardt; run; solve &target_goal/ estdata=&temp_lib..outest_model data=&temp_lib..daily_stats(where=(date=&_wab_max_gsds_date_)) out=&temp_lib..target_forecast(keep=&inputs &target_goal); run; quit; proc datasets lib=&temp_lib nolist; delete solve2; run; %do p=5 %to 50 %by 5; data &temp_lib..target_forecast2; set &temp_lib..target_forecast; &target_goal = (1+(%trim(&p)/100))*&target_goal; call symput("amount",&target_goal); run; proc model model=&temp_lib..model noprint; %do i=1 %to &ninputs; solve %scan(&inputs,&i) / estdata=&temp_lib..outest_model data=&temp_lib..target_forecast2 out=&temp_lib..outsolve&i(keep=%scan(&inputs,&i)); run; %end; data &temp_lib..solve; %do i=1 %to &ninputs; set &temp_lib..outsolve&i; %end; run; data &temp_lib..solve; set &temp_lib..solve; &target_goal=&amount; pct_change=%trim(&p); %do i=1 %to &ninputs; if %scan(&inputs,&i)<0 then %scan(&inputs,&i)=.; %scan(&inputs,&i)=(%scan(&inputs,&i)); %end; run; %if &p gt 5 %then %do; data &temp_lib..solve2; set &temp_lib..solve2 &temp_lib..solve; run; %end; %if &p eq 5 %then %do; data &temp_lib..solve2; length &target_goal pct_change 8; set &temp_lib..target_forecast(in=in1) &temp_lib..solve; if in1 then pct_change=0; run; %end; %end; proc transpose data=&temp_lib..solve2 out=&outsolve /*(rename=(col1=_SOLVE_))*/; id pct_change; run; %mend goal_seek; %macro order_metrics(indsn= ,targets= ,inputs= ,retcode=); %local macname; %let macname=&sysmacroname; %let &retcode=0; %let ntargets = 1; %let name = %scan(&targets,&ntargets); %do %while(&name ne ); %let ntargets = %eval(&ntargets+1); %let name = %scan(&targets,&ntargets); %end; %let ntargets=%eval(&ntargets-1); proc expand data=&indsn out=&temp_lib..daily_stats; id date; run; %do i=1 %to &ntargets; %let target=%scan(&targets,&i); proc autoreg data=&temp_lib..daily_stats outest=%trim(&temp_lib..outest&i) covout noprint ; model &target_goal=&inputs/backstep nlag=8 method=ml; run; data &temp_lib..outesta; point=1; set &temp_lib..outest&i(keep= &inputs) point=point; output; stop; run; proc transpose data=&temp_lib..outesta out=&temp_lib..toutesta(rename=(col1=_PARM&i._)); var &inputs; run; /*sassyw::more norm code*/ data &temp_lib..toutesta; set &temp_lib..toutesta; _name_=strip(lowcase(_name_)); run; data &temp_lib..outestb(keep=_name_ _stderr_ _DF&i._); set &temp_lib..outest&i(where=(_NAME_ ne ' ' and _NAME_ ne 'Intercept')); _DF&i._=_SSE_/_MSE_; run; /*sassyw::more norm code*/ data &temp_lib..outestb; set &temp_lib..outestb; _name_=strip(lowcase(_name_)); run; data &temp_lib..weight&i; set &temp_lib..outestb; set &temp_lib..toutesta; _T&i._=_PARM&i._/_STDERR_; _P&i._=(1-probt(abs(_T&i._),_DF&i._))*2; _WEIGHT&i._=1-_P&i._; if _NAME_="%trim(&target)" then _WEIGHT_=2; run; %end; data &temp_lib..order_metrics; length _name_ $ 25; %do i=1 %to &ntargets; set &temp_lib..weight&i; %end; _WEIGHT_=mean(_WEIGHT1_ %if &ntargets ge 2 %then %do i=2 %to &ntargets; ,_WEIGHT&i._ %end; ); run; data &temp_lib..order_metrics; set &temp_lib..order_metrics end=eof; output; if eof then do; %do i=1 %to &ntargets; _NAME_="%scan(&targets,&i)"; _WEIGHT_=2; output; %end; end; run; proc sort data=&temp_lib..order_metrics(keep=_NAME_ _WEIGHT_); by _NAME_; run; %mend order_metrics; /*****************************************************/ /* REAL beginning of automatic_forecast macro */ /*****************************************************/ /* 12May04-Added measurement_range to config metadata file */ %macro automatic_forecast (indsn=, outdsnb=, variables=, confidence_coeff=, date=, lead=, print=, nooutall=, graphdsnb=, datamodel=, targets=, inputs=, target_goal=, meanout=, solveout=, retcode=); %local macname; %let macname=&sysmacroname; %let &retcode=0; %let abort=0; %if %quote(&indsn)=%str() %then %do; %PUT %UNQUOTE(&wab_error) The Input data set must be specified.; %let abort=1; %goto done; %end; %if %quote(&outdsnb)=%str() %then %do; %PUT %UNQUOTE(&wab_error) Output data set must be specified.; %let abort=1; %goto done; %end; %if %quote(&variables)=%str() %then %do; %let variables=_all_; %end; %if %quote(&lead)=%str() %then %do; %let lead=7; %end; %if %quote(&confidence_coeff)=%str() %then %do; %let confidence_coeff=0.10; %end; %if %quote(&date)=%str() %then %do; %LET date=date; %end; /* how many observations in dataset */ data _null_; point=1; set &indsn point=point nobs=nobs; call symput('nobs',nobs); stop; run; %let nobsm1=%eval(&nobs-1); data &temp_lib..final_day; point=&nobs; set &indsn point=point; output; stop; run; proc transpose data=&temp_lib..final_day out=&temp_lib..final_day(rename=(col1=ACTUAL)); by date; run; /* sassyw::added this code to try and normalize the _name_ column */ data &temp_lib..final_day; length _name_ $ 45; set &temp_lib..final_day; _name_=strip(lowcase(_name_)); run; proc hpf data=&indsn(obs=&nobsm1) lead=1 outfor=&temp_lib..outfor1 nooutall /*print=all*/ outest=&temp_lib..outest; id &date interval=day; forecast &variables / alpha=&confidence_coeff model=best ; run; data &temp_lib..outfor1; set &temp_lib..outfor1; _name_=strip(lowcase(_name_)); run; proc sort data=&temp_lib..outfor1; by _name_ date; run; data &temp_lib..outest; set &temp_lib..outest; _name_=strip(lowcase(_name_)); run; /* data &temp_lib..outfor1; _name_=lowcase(_name_); run; */ proc sort data=&temp_lib..final_day; by _name_ date; run; /* sassyw::added code to norm _name_ */ data &datamodel; set &datamodel; _name_=strip(lowcase(_name_)); run; /* Addition of measurement_range to the config metadata file requires that we keep that variable here alos */ proc sort data=&datamodel(keep=_name_ _label_ business_dir measurement_range) out=&temp_lib..daily_stats_att; by _name_; run; /* sassyw::added code to norm _name_ */ data &temp_lib..daily_stats_att; set &temp_lib..daily_stats_att; _name_=strip(lowcase(_name_)); run; data &temp_lib..final_day; merge &temp_lib..final_day &temp_lib..daily_stats_att; by _name_; run; /* Create the SV.LIMITSB dataset to feed values */ /* into the scorecard/GTable. */ data &outdsnb; format difference nlnum12.2; label difference='Relative Difference'; merge &temp_lib..outfor1(in=in1) &temp_lib..final_day(in=in2); by _name_ date; error=actual-predict; /*****************************************/ /* Pre-set performance variable to "OK" */ /* Performance values are: */ /* 1 = Below and Bad */ /* 2 = Below and Good */ /* 3 = OK */ /* 4 = Above and Good */ /* 5 = Above and Bad */ /*****************************************/ performance = 3; /* Set business_goal values and */ /* re-set performance variable if needed */ if upcase(substr(trim(left(business_dir)),1,2)) ='UP' then do; business_goal=(probnorm(error/std)/2+.5)*100; if ACTUAL lt LOWER then performance=1; /* Below and Bad */ else if ACTUAL gt UPPER then performance=4; /* Above and Good */ end; else do; /* business_dir = 'DOWN' */ business_goal=(1-probnorm(error/std)/2)*100; if ACTUAL lt LOWER then performance=2; /* Below and Good */ else if ACTUAL gt UPPER then performance=5; /* Above and Bad */ end; difference= error/std ; if in1 and in2; run; data &outdsnb; set &outdsnb; if predict lt 0 then predict = 0; if lower lt 0 then lower = 0; if upper lt 0 then upper = 0; run; proc hpf data=&indsn lead=&lead outfor=&graphdsnb outest=&temp_lib..outest(keep = _PARM_ _TVALUE_ _PVALUE_ _NAME_ where=(_PARM_='SEASON')) ; id &date interval=day; forecast &variables / alpha=&confidence_coeff model=best ; run; /* normalize the name for merging */ data &graphdsnb; set &graphdsnb; _name_=strip(lowcase(_name_)); run; data &outdsnb; set &outdsnb; _name_=strip(lowcase(_name_)); run; /* sort for by group processing */ proc sort data=&graphdsnb; by _name_ date; run; data &graphdsnb; merge &graphdsnb &outdsnb(in=in2 keep = _name_ predict date lower upper error std rename=(predict=predict_today lower=lower_today upper=upper_today error=error_today std=std_today)); by _name_ date; if in2 then do; predict=.; lower=.; upper=.; std=.; error=.; end; run; proc sort data=&temp_lib..outest; by _name_; run; data &graphdsnb; merge &graphdsnb &temp_lib..final_day(keep= _name_ _label_) &temp_lib..outest; by _name_; run; %done: %if &abort %then %PUT %UNQUOTE(&wab_error) automatic_forecast macro ended abnormally.; %order_metrics( indsn=&indsn, targets=&targets, inputs=&inputs, retcode=&retcode ); run; %goal_seek(target_goal=&target_goal, inputs=&inputs, outdsnb=&outdsnb, retcode=&retcode, outsolve=&solveout); data &solveout; length _name_ $ 25; set &solveout; _name_=strip(lowcase(_name_)); run; data &outdsnb; length _name_ $ 25; set &outdsnb; _name_=strip(lowcase(_name_)); run; data &temp_lib..order_metrics; length _name_ $ 25; set &temp_lib..order_metrics; _name_=strip(lowcase(_name_)); run; proc sort data=&outdsnb; by _name_; run; proc sort data=&solveout; by _name_; run; proc sort data=&temp_lib..order_metrics; by _name_; run; data &outdsnb; merge &solveout &outdsnb &temp_lib..order_metrics /*&temp_lib..solve*/; by _name_; length _5c _0c _10c _15c _20c _25c _30c _35c _40c _45c _50c $ 12; array nums {11} _0 _5 _10 _15 _20 _25 _30 _35 _40 _45 _50; array chars {11} _0c _5c _10c _15c _20c _25c _30c _35c _40c _45c _50c; do i=2 to 11; if nums{i} gt nums{1} and substr(business_dir,1,4)='DOWN' then nums{i}=.; if nums{i} lt nums{1} and substr(business_dir,1,2)='UP' then nums{i}=.; if nums{i} gt 0 and upcase(strip(compbl(measurement_range)))="NEGATIVE NUMBER" then nums{i}=.; if nums{i} lt 0 and upcase(strip(compbl(measurement_range)))="POSITIVE NUMBER" then nums{i}=.; if ( (nums{i} lt -100) or (nums{i} gt 100) ) and upcase(strip(compbl(measurement_range)))="PERCENTAGE" then nums{i}=.; if ( (nums{i} lt -100) or (nums{i} gt 0) ) and upcase(strip(compbl(measurement_range)))="NEGATIVE PERCENTAGE" then nums{i}=.; if ( (nums{i} lt 0) or (nums{i} gt 100) ) and upcase(strip(compbl(measurement_range)))="POSITIVE PERCENTAGE" then nums{i}=.; if ( (nums{i} lt -1) or (nums{i} gt 1) ) and upcase(strip(compbl(measurement_range)))="PROPORTION" then nums{i}=.; if ( (nums{i} lt -1) or (nums{i} gt 0) ) and upcase(strip(compbl(measurement_range)))="NEGATIVE PROPORTION" then nums{i}=.; if ( (nums{i} lt 0) or (nums{i} gt 1) ) and upcase(strip(compbl(measurement_range)))="POSITIVE PROPORTION" then nums{i}=.; end; do i=1 to 11; chars{i}=put(nums{i},10.2); if nums{i}=. then chars{i}=' '; end; drop i; run; proc sort data=&outdsnb; by descending _WEIGHT_; run; proc means data=&outdsnb noprint; var business_goal; weight _weight_; output out=&meanout(keep=weighted_mean) mean=weighted_mean; run; data &meanout; set &meanout; date=¤t_date; format date date9.; run; %mend automatic_forecast; /*-----------------------------------------------------------------------------*/ /* */ /* HISTORY: 03/08/04 sassyw created */ /* */ /*-----------------------------------------------------------------------------*/ /* DESCRIPTION: This program creates a dataset exactly like the */ /* &outlib..scorecard_data dataset with only one row. */ /* All the numeric variables are set to zero. and the */ /* _NAME_ variable is set to "NOT ENOUGH DAYS OF DATA" */ /* This is done so that when someone picks a scorecard */ /* where it could not be produced due to not having */ /* processed enough days of weblogs, they will get a one */ /* row table returned giving them that information. */ /* */ /* PARAMETERS: NONE */ /*-----------------------------------------------------------------------------*/ %macro makenullscore(message=); /* * See if a null record has already been written for this scorecard * for the current_date. If it has do not write another one. If it * has not then append one to the end of &outlib.scorecard_data */ %let append=; %let scobs=; %IF %SYSFUNC(EXIST(&outlib..scorecard_data,DATA)) %THEN %DO; data &outlib..scorecard_data; set &outlib..scorecard_data; if (scorecard eq "&scorecard") and (report_date=¤t_date) then delete; run; data _null_; set &outlib..scorecard_data; call symput("SCOBS",_n_); run; %END; %ELSE %DO; data &temp_lib..checkforrec; stop; run; %END; %IF %SYSFUNC(EXIST(&outlib..scorecard_data,DATA)) eq 0 %THEN %DO; %LET append=go; %END; %IF (&scobs ge 0) or (&append=go) %THEN %DO; /* S0275306 - scorecard length too short changed to 25 */ data &temp_lib..scorecard_temp_data; attrib scorecard length =$25 report_date length =8 format =DATE7. _LABEL_ length =$40 format =$100. informat=$100. label='LABEL' _0 length =8 label='0% Increase' format =nlnum20.2 _5 label='5% Increase' length =8 format =nlnum20.2 _10 label='10% Increase' length =8 format =nlnum20.2 _15 label='15% Increase' length =8 format =nlnum20.2 _20 label='20% Increase' length =8 format =nlnum20.2 _25 label='25% Increase' length =8 format =nlnum20.2 _30 label='30% Increase' length =8 format =nlnum20.2 _35 label='35% Increase' length =8 format =nlnum20.2 _40 label='40% Increase' length =8 format =nlnum20.2 _45 label='45% Increase' length =8 format =nlnum20.2 _50 label='50% Increase' length =8 format =nlnum20.2 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 difference length =8 label = 'Relative Difference' format =nlnum12.2 importance length =8 measurement_range length = $40 _name_ length =$45; scorecard="&scorecard"; report_date="¤t_date"; _LABEL_="&message"; _0=0; _5=0; _10=0; _15=0; _20=0; _25=0; _30=0; _35=0; _40=0; _45=0; _50=0; DATE="¤t_date"; ACTUAL=0; PREDICT=0; LOWER=0; UPPER=0; ERROR=0; STD=0; business_dir=' '; performance=0; business_goal=0; _WEIGHT_=0; difference=0; measurement_range=' '; importance=0; _name_="&message"; output; run; proc append base=&outlib..scorecard_data data=&temp_lib..scorecard_temp_data; run; %END; %MEND; /*----------------------Web Analytics-----------------------------------------*/ /* Name: */ /* WASCORDP -- Web Analytics SCORecard Daily Process */ /* */ /* Purpose: */ /* Create the Web Analytics Scorecard data set (adapted v6 service code */ /* for use in SWA product). */ /* */ /*----------------------------------------------------------------------------*/ /* Supported By: */ /* Scott Wilkins (SASSYW) */ /* */ /* */ /*----------------------------------------------------------------------------*/ /* History: */ /* Date Description Username Change Code */ /* 20040323 Program Created sassyw NA */ /* 20041231 S0278295: Add missing day check cabahl C0001 */ /* to 30 contiguous days check, handle missing days */ /* */ /*----------------------------------------------------------------------------*/ /* 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. */ /* &WAB_WARNING: the text that identifies WA macro warning messages. */ /* */ /*----------------------------------------------------------------------------*/ /* Parameters: */ /* BEGIN_DATE: (Required) SAS date value for the first date in a */ /* range of 1 or more days to process the scorecard for. */ /* END_DATE: (Optional) SAS date value for the last date in a */ /* range of 1 or more days to process the scorecard 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.WASCRCRD) Name of the data */ /* set that contains the meta data for how to build the */ /* scorecard. Multiple scorecards may be defined in */ /* this file. If no value is passed for this parameter */ /* then the default config file will be used. */ /* OUTLIB: (Optional: Default=SUMMARY) If no value is passed for */ /* this then the default is used. This parameter is */ /* to redirect the output to a library other than the */ /* default. */ /* USE_FIRST_TWO_WEEKS: (Optional: Default=yes) Variable switch that */ /* indicates whether to use the first 14 days of data. */ /* (In some cases, removing the first 14 obs may provide */ /* a better fit for the model). */ /* DEFINITION_TO_RUN:(Optional: Default=blank) If a parameter is specified */ /* for this then only that scorecard will be processed. */ /* It must be a name of a scorecard defined in the */ /* scorecard_config data set. */ /* RETCODE: Mechanism for passing Return Codes. */ /* */ /*----------------------------------------------------------------------------*/ /* Input: */ /* (begin_date= */ /* ,end_date= */ /* ,indsn= */ /* ,config= */ /* ,outlib= */ /* ,use_first_two_weeks= */ /* ,definition_to_run= */ /* ,retcode= */ /* ); */ /* */ /*----------------------------------------------------------------------------*/ /* Output: */ /* SUMMARY.SCORECARD_DATA */ /* */ /*----------------------------------------------------------------------------*/ /* History: */ /* 12May04-Added measurement_range to config metadata file */ /* 13May04-Limited history data for each report_date to 30 days in */ /* &outlib..scorecard_metrics_history */ /* 08Sep04-S0267344: NEED ERROR CHECK FOR INPUT DATA SET THAT HAS MULTI-ROWS */ /* FOR A DATE */ /* 25Oct04-S0273362: Change scorecard_to_run to definition_to_run */ /* 01Mar06-S0347345: Input data set for individual dashboards not reset */ /* changed so that it would */ /*----------------------------------------------------------------------------*/ /* Copyright (c) 2004 SAS Institute Inc. All Rights Reserved */ /*----------------------------------------------------------------------------*/ %macro wascordp(begin_date= ,end_date= ,indsn= ,outlib= ,use_first_two_weeks= ,definition_to_run= ,retcode= ); %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; /* * Set default value for use_first_two_weeks */ %IF %quote(&use_first_two_weeks)= %THEN %DO; %LET use_first_two_weeks=yes; %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 scorecards 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 scorecards 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 %STRIP(&indsn) defined in macro parameter INDSN; %PUT %UNQUOTE(&wab_error) No scorecards 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 swacoredp macro call; %PUT %UNQUOTE(&wab_error) No scorecards 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 scorecards 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 scorecards 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 scorecards 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.wascrinp; /* * 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 scorecards 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 scorecards 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 scorecards will be processed; %LET retcode=1; %GOTO ERREXIT; %END; %IF %quote(&config)^= %THEN %DO; %************************************************************************; %* does CONFIG dataset have all the standard variables (& attribs)? ; %************************************************************************; %watstdsn(test_dsn=&config ,tmpl_dsn=wascrinp ,retcode=wab_rc ); %IF &wab_rc=1 %THEN %DO; %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 WASCORDP 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 WASCRCRD and WASCRINP to get complete Scorecard metadata ; %***************************************************************************; proc sql; create table &temp_lib..scorecard_config as select l.role , l._name_ , l._label_ , l.business_dir , l.measurement_range , r.name as scorecard , r.indsn , r.etl_run_sw from &config l, config.wascrcrd r where l.scorecard_id eq r.id; quit; /* * Make a copy of the orig config file name for later use * in error messages */ %LET config_root=&config; /***********************************************************/ /* Code to loop for each scorecard name in the config file */ /***********************************************************/ %***************************************************************************; %* Figure out how many Scorecards are defined in the metadata ; %***************************************************************************; proc sort data=&temp_lib..scorecard_config out =&temp_lib..sclist (keep=scorecard etl_run_sw) nodupkey; by scorecard; run; /* * If no observations in this data set then we did not find any * scorecard definitions */ %IF %get_observation_count(indsn=&temp_lib..sclist) eq 0 %THEN %DO; %PUT %UNQUOTE(&wab_error) No scorecard names found in config file &config; %PUT %UNQUOTE(&wab_error) No scorecards will be processed; %LET retcode=1; %GOTO ERREXIT; %END; /* * Count the observations and store the number of scorecards * in macro variable */ data _null_; set &temp_lib..sclist nobs=nscorecards; call symput('nscorecards',nscorecards); stop; run; %***************************************************************************; %* Use the argument for the DEFINITION_TO_RUN parameter to control the ; %* selection of which scorecards to run. ; %***************************************************************************; %if %quote(&definition_to_run) eq %str() or %quote(%upcase(&definition_to_run)) eq _ALL_ %then %do; %let nscorecards=%get_observation_count(indsn=&temp_lib..sclist); %put %unquote(&wab_note) DEFINITION_TO_RUN parameter value %QCMPRES(will select) all &nscorecards scorecards.; %if &nscorecards eq 0 %then %do; %put %unquote(&wab_error) No scorecard 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 nscorecards=%get_observation_count(indsn=&temp_lib..sclist); %put %unquote(&wab_note) DEFINITION_TO_RUN parameter value %QCMPRES(will limit) processing to &nscorecards ETL scorecards.; %if &nscorecards eq 0 %then %do; %put %unquote(&wab_error) No scorecard 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 nscorecards=%get_observation_count(indsn=&temp_lib..sclist); %put %unquote(&wab_note) DEFINITION_TO_RUN parameter value %QCMPRES(will limit) processing to &nscorecards non-ETL scorecards.; %if &nscorecards eq 0 %then %do; %put %unquote(&wab_error) No scorecard will be processed.; %let retcode=1; %goto ERREXIT; %end; %end; %else %do; %************************************************************************; %* identify each of the requested scorecards in DEFINITION_TO_RUN ; %************************************************************************; data &temp_lib..def_list; length defs $ 5000; length scorecard $ 25; defs="&definition_to_run"; do while(defs gt ' '); scorecard=scan(defs,1,','); output; defs=strip(tranwrd(defs,strip(scorecard),' ')); if substr(defs,1,1) eq ',' then defs=strip(substr(defs,2)); end; keep scorecard; run; %************************************************************************; %* m/m DEF_LIST with SCLIST to establish the metadata that is for only ; %* the requested scorecards ; %************************************************************************; proc sort data=&temp_lib..def_list; by scorecard; run; data &temp_lib..sclist only_a only_b; merge &temp_lib..sclist (in=in_a) &temp_lib..def_list (in=in_b); by scorecard; if in_a then if in_b then output &temp_lib..sclist; else output only_a; else output only_b; run; %let nscorecards=%get_observation_count(indsn=&temp_lib..sclist); %put %unquote(&wab_note) DEFINITION_TO_RUN parameter value %QCMPRES(will limit) processing to &nscorecards selected scorecards.; %if %get_observation_count(indsn=only_b) ne 0 %then %do; %put %unquote(&wab_error) There are Scorecard 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 &nscorecards eq 0 %then %do; %put %unquote(&wab_error) No scorecard will be processed.; %let retcode=1; %goto ERREXIT; %end; %end; %DO current_date=&begin_date %to &end_date; /* * set the logdt macro variable because it may * be used in the retro code that this macro calls */ /*%LET logdt=¤t_date;*/ data _null_; *tempdate=strip(put(¤t_date,yymmddn8.)); tempdate=strip(put(¤t_date,Date9.)); call symput("_fmt_current_date_",tempdate); run; /*********************************************************************** * Outside loop controling one iteration per scorecard defined * * in config file * ***********************************************************************/ %DO sc=1 %to &nscorecards; /* * Reset scorecard input data set to either the specified data set * or blank to be set later. */ %if &indsn ne %str() %then %let sc_indsn=&indsn; %else %let sc_indsn=; data _null_; point=≻ set &temp_lib..sclist point=point; call symput('scorecard',trim(scorecard)); stop; run; /* * If no input data set name (indsn) passed in then * go get it out of the scorecard config file */ %IF %quote(&sc_indsn)= %THEN %DO; data &temp_lib..checkindsn; set &temp_lib..scorecard_config; if scorecard="&scorecard" 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 scorecard */ %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 &scorecard; %PUT %UNQUOTE(&wab_warning) &scorecard scorecard 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..scorecard_config; if scorecard="&scorecard"; if indsn ne '' then do; call symput('sc_indsn',indsn); stop; end; else do; call symput('sc_indsn',' '); end; run; %END; /* * Validate input data set value */ /*********** 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(&sc_indsn)= %THEN %DO; %PUT %UNQUOTE(&wab_warning) Input dataset value for &scorecard not found in config file &config; %PUT %UNQUOTE(&wab_warning) &scorecard scorecard will not be processed; %LET indsn_root= ; %GOTO FORWARD; %END; %IF %SYSFUNC(EXIST(&sc_indsn,DATA))=0 %THEN %DO; %PUT %UNQUOTE(&wab_warning) Input dataset &sc_indsn defined for &scorecard scorecard in config file &config does not exist; %PUT %UNQUOTE(&wab_warning) &scorecard scorecard will not be processed; %LET indsn_root= ; %GOTO FORWARD; %END; %locktest(dset=&sc_indsn); %if &lock_sw ne 0 %then %do; %PUT %UNQUOTE(&wab_warning) Input dataset &sc_indsn defined in macro parameter INDSN can not be opened; %PUT %UNQUOTE(&wab_warning) No scorecards will be processed; %LET indsn_root= ; %GOTO FORWARD; %END; %IF %get_observation_count(indsn=&sc_indsn) eq 0 %THEN %DO; %PUT %UNQUOTE(&wab_warning) No rows found in input dataset %QCMPRES(&sc_indsn) defined for &scorecard scorecard in config file &config; %PUT %UNQUOTE(&wab_warning) &scorecard scorecard will not be processed; %LET indsn_root= ; %GOTO FORWARD; %END; /* * Report the validated input data set name from the config to the user */ %PUT %UNQUOTE(&wab_note) Input dataset value &scorecard scorecard read from configuration file; %PUT %UNQUOTE(&wab_note) INDSN: &sc_indsn; /* * Keep a copy of the name of the original input data set name */ %LET indsn_root=&sc_indsn; /* * Make sure the input data set for scorecard 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 scorecard for */ data &temp_lib..scorecard_input; set &sc_indsn; run; /* * Set the working indsn variable to the temporary copy we just * made of the original */ /* %LET indsn=&temp_lib..scorecard_input; */ /* * Make sure it can be opened */ %locktest(dset=&temp_lib..scorecard_input); %if &lock_sw ne 0 %then %do; %PUT %UNQUOTE(&wab_warning) Input data set &temp_lib..scorecard_input could not be opened; %PUT %UNQUOTE(&wab_warning) &scorecard scorecard will not be processed; %LET indsn_root= ; %GOTO FORWARD; %END; /* * Validate that there is a date variable in the input dataset */ proc contents data=&temp_lib..scorecard_input 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_root; %PUT %UNQUOTE(&wab_warning) &scorecard scorecard will not be processed; %LET indsn_root= ; %GOTO FORWARD; %END; /* S0278295 - 12/31/2004 changed to extrapolation of missing days data * Validate that there are no missing days in the input data set * for the days previous to the current processing date * S0267344: Also validate that there is only one row per date */ proc sort data=&temp_lib..scorecard_input out=&temp_lib..scorecard_input; by date; run; proc freq data=&temp_lib..scorecard_input; table date / missing noprint out=dup_dates; run; /*01Jun04:Enhance continous date check - can not be missing days */ /* in data previous to the current processing date data &temp_lib..continousdate; set &temp_lib..scorecard_input; if (date le ¤t_date); run; continousdate was scorecard_input data &temp_lib..date_missing; set &temp_lib..continousdate; datedif=dif(date); * S0267344: check for multiple rows with same date * call symput('date_abort','no'); if (datedif eq 0) then do; call symput('date_abort','yes'); stop; end; if (datedif ne 1) and (_n_ ne 1) then do; output; end; run; */ /* S0278295 - handle missing days, extrapolation by using the previous week's weekday values for the missing day - J. Brocklebank */ data date_template; set &temp_lib..scorecard_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..scorecard_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 then use the previous week's same * weekday to place extrapolated values for that date. *----------------------------------------------------------------*/ %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..scorecard_input where date in (select intnx('day',date,-7) from &temp_lib..date_missing); quit; data &temp_lib..scorecard_input(drop=replace_date); set &temp_lib..scorecard_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..scorecard_input; by date; run; %end; /* S0267344: If more than one row with the same date then write warning and move on to the next scorecard definition */ proc freq data=&temp_lib..scorecard_input; table date / missing noprint out=dup_dates; run; data _null_; set dup_dates end=eof; length dup_dates $100; retain dup_date 0 dup_dates ''; if count > 1 then do; dup_date=1; dup_dates=strip(dup_dates)||' '||put(date,mmddyy10.); end; if eof then do; if dup_date=1 then call symput('date_abort','yes'); else call symput('date_abort','no'); call symput('dates_dupped',strip(dup_dates)); end; run; %IF &date_abort=yes %THEN %DO; %PUT %UNQUOTE(&wab_warning) &sc_indsn -- the source data set for %QCMPRES( Scorecard) data has more than one obs per date.; %PUT %UNQUOTE(&wab_warning) Scorecard processing requires %QCMPRES( that) the source data set have only one obs per date.; %PUT %UNQUOTE(&wab_warning) The &scorecard scorecard will not be processed; %LET indsn_root= ; %GOTO FORWARD; %END; /* S0278295: if missing dates then handling by replacing with past week values %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 forecasting; %PUT %UNQUOTE(&wab_warning) In order to process the scorecard 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 &scorecard scorecard with a record indicating this problem; %PUT %UNQUOTE(&wab_warning) When these missing days age out this scorecard will run again; %PUT %UNQUOTE(&wab_warning) The missing date(s) are:; *S0267344: Enhance so that we do not put out too many missing dates and risk filling up the log * %IF %get_observation_count(indsn=&temp_lib..date_missing) le 100 %THEN %DO; data _null_; set &temp_lib..date_missing; format missing_date Date9.; missing_date=date-1; put missing_date=; run; %END; %ELSE %DO; %PUT %UNQUOTE(&wab_warning) Input data set &indsn_root has more that 100 missing days; %PUT %UNQUOTE(&wab_warning) &scorecard scorecard will not be processed; %END; %if &date_abort=yes %THEN %DO; %MAKENULLSCORE(message=MULTIPLE ROWS WITH SAME DATE-CAN NOT PROCESS); %END; %ELSE %DO; %MAKENULLSCORE(message=MISSING DAYS-NO FORECAST); %END; %LET indsn_root= ; %GOTO FORWARD; %END; */ /* * Announce start of each individual scorecard */ %PUT %UNQUOTE(&wab_note) ***************************************************************************; %PUT %UNQUOTE(&wab_note) BEGIN DATA PROCESSING FOR SCORECARD: &scorecard ; %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 scorecard being processed * is in the file */ data &temp_lib..scorecard_extr; set &temp_lib..scorecard_config; if (lowcase(scorecard) eq lowcase("&scorecard")) and (lowcase(role) ne "exclude"); run; %IF %get_observation_count(indsn=&temp_lib..scorecard_config) eq 0 %THEN %DO; %PUT %UNQUOTE(&wab_warning) No input variables defined in &config for &scorecard scorecard; %PUT %UNQUOTE(&wab_warning) &scorecard scorecard will not be processed; %LET indsn_root= ; %GOTO FORWARD; %END; /* * Make a copy of the entire config for this scorecard so we * can check later to make sure all the vars in the input dataset * are defined in the config file */ data &temp_lib..root_config; set &temp_lib..scorecard_extr; if (lowcase(scorecard)=lowcase("&scorecard")); run; /* * Verify that the variables in the config file for the scorecard * all have valid ROLE values */ data &temp_lib..checkrole(keep=_name_ role); set &temp_lib..scorecard_extr; if lowcase(scorecard)=lowcase("&scorecard"); if lowcase(role) not in ('input','target','exclude'); run; %IF %get_observation_count(indsn=&temp_lib..checkrole) ne 0 %THEN %DO; data _null_; point=1; set &temp_lib..checkrole point=point nobs=nobs2; call symput('nvars',nobs2); stop; run; /* Make a variable listing for invalid role values */ %LET rolevars = ; %DO i=1 %to &nvars; data _null_; point=&i; set &temp_lib..checkrole point=point; call symput('rolevar',left(trim(_name_))); call symput('roleval',left(trim(role))); stop; run; %LET rolevars= &rolevars &rolevar=&roleval ; %END; %PUT %UNQUOTE(&wab_warning) Invalid variable ROLE values found in config file &config; %PUT %UNQUOTE(&wab_warning) Valid values for ROLE are INPUT, TARGET, and EXCLUDE; %PUT %UNQUOTE(&wab_warning) These invalid Variable=Role pairs were found: &rolevars; %PUT %UNQUOTE(&wab_warning) &scorecard scorecard will not be processed; %LET indsn_root= ; %GOTO FORWARD; %END; /* * Retrieve the target variable from the config table */ data &temp_lib..target; set &temp_lib..scorecard_extr; if lowcase(role)='target'; run; /* No target variable defined? */ %IF %get_observation_count(indsn=&temp_lib..target) eq 0 %THEN %DO; %PUT %UNQUOTE(&wab_warning) No target variable defined in &config for &scorecard scorecard; %PUT %UNQUOTE(&wab_warning) &scorecard scorecard will not be processed; %LET indsn_root= ; %GOTO FORWARD; %END; /* Too many target variables defined? */ %IF %get_observation_count(indsn=&temp_lib..target) gt 1 %THEN %DO; %PUT %UNQUOTE(&wab_warning) Multiple target variables defined in &config for &scorecard scorecard; %PUT %UNQUOTE(&wab_warning) &scorecard scorecard will not be processed; %LET indsn_root= ; %GOTO FORWARD; %END; /* Extract the target_var value from the file */ data _null_; set &temp_lib..scorecard_extr; if lowcase(role)='target'; call symput('target_var',lowcase(trim(left(_name_)))); stop; run; /* * Verify that the target variable is in the input data * and that it is numeric */ proc contents data=&sc_indsn out=&temp_lib..metric_vars noprint; run; data &temp_lib..checktarget; set &temp_lib..metric_vars; if lowcase(name) eq lowcase("&target_var"); if type=1; run; %IF %get_observation_count(indsn=&temp_lib..checktarget) ne 1 %THEN %DO; %PUT %UNQUOTE(&wab_warning) Numeric target variable &target_var defined in &config for &scorecard scorecard was not found in &indsn_root; %PUT %UNQUOTE(&wab_warning) &scorecard scorecard will not be processed; %LET indsn_root= ; %GOTO FORWARD; %END; /* start cut*/ /*sassyw: 19MAR04 - Removed code that required all indsn variables */ /* to be defined in the config */ /* end cut*/ /* * Verify that the day to process exist in the input dataset */ data &temp_lib..checkforday; set &temp_lib..scorecard_input; if date eq ¤t_date; run; %IF %get_observation_count(indsn=&temp_lib..checkforday) ne 1 %THEN %DO; %PUT %UNQUOTE(&wab_warning) No data found for &_fmt_current_date_ in &indsn_root; %PUT %UNQUOTE(&wab_warning) &scorecard scorecard will not be processed; %PUT %UNQUOTE(&wab_warning) Updating &scorecard scorecard with a record indicating this problem; %MAKENULLSCORE(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..scorecard_input which is a copy of the input dataset passed in or defined in the config */ data &temp_lib..scorecard_input; set &temp_lib..scorecard_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..scorecard_input) ge 30 %THEN %DO; /* * Create a variable listing from all legal varriables in * the config file */ data &temp_lib..outcontents; set &temp_lib..scorecard_extr; if lowcase(role) not in ("target","exclude"); run; /* * Get the number of variables by counting the number of rows in the * subsetted output dataset (outcontents) */ data _null_; point=1; set &temp_lib..outcontents point=point nobs=nobs2; call symput('nvars',nobs2); stop; run; /* * Make a variable listing for reg call */ %LET vars = ; %DO i=1 %to &nvars; data _null_; point=&i; set &temp_lib..outcontents point=point; call symput('var',_name_); stop; run; %LET vars= &vars &var; /*change2*/ %LET vars=%lowcase(&vars); %END; /* * Remove first 14 obs where repeats are just starting to accumulate * This should provide a better model fit */ /* * For some target variables it is best to allow time for the measure * to stabalize. A good example of this would be for REPEATE_VISITORS * or some variable related to repeat_visitors. Because some time has * to pass before someone can be considered a repeat visitor. */ %IF %quote(%LOWCASE(&use_first_two_weeks))=no %THEN %DO; proc means data=&indsn noprint; var date; output out=&temp_lib..min_date min=; run; data _null_; point=1; set &temp_lib..min_date point=point nobs=nobs2; call symput('lowdate',date); stop; run; /* * Build the reg ready dataset with all but the first 14 day/rows of data */ %LET mindate = %eval(&lowdate+14); data &temp_lib..reg_ready; set &sc_indsn; if date ge &mindate; run; %END; %ELSE %DO; /* * Build the reg ready dataset with all the observations */ data &temp_lib..reg_ready; set &sc_indsn; run; %END; /* * ODS Helps grab relavent vars from reg */ %PUT %UNQUOTE(&wab_note) TARGET VARIABLE: >>>&TARGET_VAR<<<; %PUT %UNQUOTE(&wab_note) ANALYSIS VARIABLES: >>>&VARS<<<; /* S0257454 * reg_parms will hold the variables coming out of proc reg. If it * exist prior to the reg call then delete it so we will know if * proc reg had a problem createing it */ %IF %SYSFUNC(EXIST(&temp_lib..reg_parms,DATA)) %THEN %DO; proc datasets lib=&temp_lib nolist; delete reg_parms; run; quit; %END; /* * Determine whether &TEMP_LIB..REG_READY has _TYPE_ and drop it * if it does. */ %let droplist=; proc sql; create table &temp_lib..droplist as select * from dictionary.columns where upcase(libname) eq upcase("&temp_lib") and memname eq 'REG_READY' and upcase(name) eq '_TYPE_'; quit; %if %get_observation_count(indsn=&temp_lib..droplist) gt 0 %then %do; %let droplist=(drop=_type_); %end; ods output SelParmEst=&temp_lib..reg_parms; proc reg data=&temp_lib..reg_ready &droplist OUTEST=&temp_lib..reg_outest; model &target_var = &vars / SLS=.3 selection=stepwise ; run; quit; ods output close; %IF %SYSFUNC(EXIST(&temp_lib..reg_parms,DATA))=0 %THEN %DO; /* * we got a problem. No statistically significant input * variables for the target. We need to write a rec to * the output data set and terminate processing of this * scorecard. */ %makenullscore(message=NO SIGNIFICANT INPUTS FOUND FOR TARGET); %PUT %UNQUOTE(&wab_warning) No statistically significant input variables found in &sc_indsn ; %PUT %UNQUOTE(&wab_warning) for the target variable: &target_var ; %PUT %UNQUOTE(&wab_warning) Updating &scorecard scorecard with a record indicating this problem; %LET indsn_root= ; %GOTO FORWARD; %END; /* * Grab a listing of the vars from proc rec to subset * daily metrics att (the config file) */ proc means data=&temp_lib..reg_parms noprint; var step; output out=&temp_lib..max_steps max=; run; data _null_; point=1; set &temp_lib..max_steps point=point nobs=nobs2; call symput('steps',step); stop; run; data &temp_lib..reg_parms; set &temp_lib..reg_parms(where=(step=&steps)); if lowcase(variable) ne "intercept"; keep variable; run; /* * NOW THAT WE HAVE THE SIGNIFICANT VARIABLES AND THE TARGET VAR * WE CAN SUBSET THE META DATA AND THE MODEL DATA */ /*sassyw::more norm code*/ %LET target_var=%lowcase(&target_var); %LET vars = "&target_var"; /* FOR SUBSETTING META DATA */ /* sassyw::more norm code*/ %LET vars=%lowcase(&vars); %LET varlist = &target_var date; /* FOR SUBSETTING DAILY METRICS */ %LET final_vars = ; /* VARLIST FOR AUTOMATIC_FORCAST */ %DO i=1 %to &steps; data _null_; point=&i; set &temp_lib..reg_parms point=point; call symput('list',variable); CALL SYMPUT(compress("var"),lowcase(trim(left(variable)))); stop; run; %LET vars= &vars , "&var"; %LET varlist = &varlist &list; /*sassyw::more norm code*/ %LET varlist =%lowcase(&varlist); %LET final_vars = &final_vars &var; /*sassyw::more norm code*/ %LET final_vars=%lowcase(&final_vars); %END; /* * Trim down &temp_lib..scorecard_extr list to include only relevent variables */ data &temp_lib..scorecard_config_final; set &temp_lib..scorecard_extr; if strip(lowcase(_name_)) in (&vars); run; /* * Trim down &temp_lib..daily_metrics to include only relevent variables. * Remove first 14 obs where repeats are just starting to accumulate */ /************where date ge mindate was v6 repeat visitor specific*******/ /************will this have negative impact for other target vars*******/ %IF %quote(%LOWCASE(&use_first_two_weeks))=no %THEN %DO; /* * Build the autocast_final dataset without first 14 days/rows */ data &temp_lib..autocast_final; set &sc_indsn; keep &varlist; if date ge &mindate; run; %END; %ELSE %DO; /* * Build the autocast_final dataset with all the observations */ data &temp_lib..autocast_final; set &temp_lib..scorecard_input; keep &varlist; run; %END; %automatic_forecast (indsn=&temp_lib..autocast_final, outdsnb=&temp_lib..limitsb, variables=, confidence_coeff=.10, date=date, lead=7, print=none, nooutall=, graphdsnb=&temp_lib..graphb, datamodel=&temp_lib..scorecard_config_final, targets=&target_var, inputs=&final_vars, target_goal=&target_var, meanout=&temp_lib..mean_limitsb, solveout=&temp_lib..solve, retcode=&retcode); /* * Added this code to create a report date and scorecard name columns * to the &temp_lib..limitsb and &temp_lib..graphb output data sets * Here we also standardize the _name_ var length to 25 */ data &temp_lib..limitsb(drop=_0c _5c _10c _15c _20c _25c _30c _35c _40c _45c _50c); length scorecard $25; label _0='0% Increase' _5='5% Increase' _10='10% Increase' _15='15% Increase' _20='20% Increase' _25='25% Increase' _30='30% Increase' _35='35% Increase' _40='40% Increase' _45='45% Increase' _50='50% Increase'; format report_date date7. _0 _5 _10 _15 _20 _25 _30 _35 _40 _45 _50 nlnum20.2; set &temp_lib..limitsb; importance=_n_-1; * target var has _weight_=2 set it to one; if _weight_=2 then _weight_=1; format _weight_ nlnum12.2; report_date=¤t_date; scorecard="&scorecard"; output; run; /* standardize the _name_ var length to 45 to prevent append failure */ data &temp_lib..limitsb(drop=_name_); set &temp_lib..limitsb; length new_name $45; new_name=_name_; rename new_name=_name_; run; /* add the scorecard column to the table */ data &temp_lib..graphb_history; length scorecard $25; format report_date date7.; set &temp_lib..graphb; report_date=¤t_date; scorecard="&scorecard"; output; run; /* standardize the _name_ var length to 45 to prevent append failure */ data &temp_lib..graphb_history(drop=_name_); set &temp_lib..graphb_history; length new_name $45; new_name=_name_; rename new_name=_name_; run; /* * 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 scorecard 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..scorecard_metric_history,DATA)) %THEN %DO; data &temp_lib..recstodelete; set &outlib..scorecard_metric_history; if (report_date ge ¤t_date)and (lowcase(scorecard)=lowcase("&scorecard")); 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..scorecard_metric_history; %PUT %UNQUOTE(&wab_note) THESE RECORDS WILL BE DELETED BECAUSE THEY ARE NO LONGER VALID; %PUT %UNQUOTE(&wab_note) ************************************************************************************; %END; data &outlib..scorecard_metric_history; set &outlib..scorecard_metric_history; if (report_date ge ¤t_date)and (lowcase(scorecard)=lowcase("&scorecard")) then delete; run; %END; %IF %SYSFUNC(EXIST(&outlib..scorecard_data,DATA)) %THEN %DO; data &temp_lib..recstodelete; set &outlib..scorecard_data; if (report_date ge ¤t_date)and (lowcase(scorecard)=lowcase("&scorecard")); 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..scorecard_data; %PUT %UNQUOTE(&wab_note) THESE RECORDS WILL BE DELETED BECAUSE THEY ARE NO LONGER VALID; %PUT %UNQUOTE(&wab_note) ************************************************************************************; %END; data &outlib..scorecard_data; set &outlib..scorecard_data; if (report_date ge "¤t_date") and (lowcase(scorecard)=lowcase("&scorecard")) then delete; run; %END; /* * Append the contents of limitsb to the scorecard_data table. If it does not * exist yet then proc append will create it */ /* change the label of _label_ : Was- Label of former variable */ /* which came from proc transpose */ data &temp_lib..limitsb; set &temp_lib..limitsb; label _label_='Label'; run; proc append base=&outlib..scorecard_data data=&temp_lib..limitsb force; run; /* limit data to 60 days of history */ /* S0251721-Limit history to 30 days */ /* S0254807-Remove the _today values */ data &temp_lib..graphb_history_orig; set &temp_lib..graphb_history; run; data &temp_lib..graphb_history; set &temp_lib..graphb_history; if predict=. then predict=predict_today; if lower=. then lower= lower_today; if upper=. then upper= upper_today; if error=. then error= error_today; if std=. then std= std_today; if date >=(¤t_date-30); run; data &temp_lib..graphb_history; set &temp_lib..graphb_history(drop=predict_today lower_today upper_today error_today std_today); run; proc append base=&outlib..scorecard_metric_history data=&temp_lib..graphb_history force; run; %END; /* number of obs ge 30 in input data set */ %ELSE %DO; /* * Write out a null line to &outlib..scorecard_data for this scorecard * indicating that for the process day there were not enough days of * data */ %makenullscore(message=NOT ENOUGH PREVIOUS DAYS OF DATA); /* * Announce not enough previous days of data to run scorecard for report_date */ %PUT %UNQUOTE(&wab_warning) ***********************************************************************************; %PUT %UNQUOTE(&wab_warning) AT LEAST 30 DAYS OF HISTORY DATA REQUIRED TO PROCESS SCORECARD; %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..scorecard_data; %PUT %UNQUOTE(&wab_warning) REFLECTING THIS PROBLEM; %PUT %UNQUOTE(&wab_warning) ************************************************************************************; %END; /* number of obs ge 30 in input data set */ %FORWARD: %END; /* Outside loop controling one iteration per scorecard 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..scorecard_input; set &indsn_root; 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..scorecard_data and &outlib..scorecard_metric_history */ %IF %SYSFUNC(EXIST(&outlib..scorecard_data,DATA)) %THEN %DO; data &temp_lib..checkrange; set &outlib..scorecard_data; if report_date lt (¤t_date-&wab_days_in_scorecard); 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..scorecard_data; %PUT %UNQUOTE(&wab_note) Number of days to keep in Scorecard is: &wab_days_in_scorecard; %PUT %UNQUOTE(&wab_note) This can be changed by using WAB_DAYS_IN_SCORECARD in the; %PUT %UNQUOTE(&wab_note) WBCONFIG file.; %PUT %UNQUOTE(&wab_note) *******************************************************; %END; data &outlib..scorecard_data; set &outlib..scorecard_data; if report_date ge (¤t_date-&wab_days_in_scorecard); run; %END; %IF %SYSFUNC(EXIST(&outlib..scorecard_metric_history,DATA)) %THEN %DO; data &temp_lib..checkrange; set &outlib..scorecard_metric_history; if report_date lt (¤t_date-&wab_days_in_scorecard); 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..scorecard_metric_history; %PUT %UNQUOTE(&wab_note) Number of days to keep in Scorecard is: &wab_days_in_scorecard; %PUT %UNQUOTE(&wab_note) This can be changed by using WAB_DAYS_IN_SCORECARD in the; %PUT %UNQUOTE(&wab_note) WBCONFIG file.; %PUT %UNQUOTE(&wab_note) *******************************************************; %END; data &outlib..scorecard_metric_history; set &outlib..scorecard_metric_history; if report_date ge (¤t_date-&wab_days_in_scorecard); run; %END; %ERREXIT: /* * Announce end of macro */ %PUT %UNQUOTE(&wab_note) *******************************************************; %PUT %UNQUOTE(&wab_note) Ending execution of macro WASCORDP 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 wascordp;