Authors      Gerhard Svolba, SAS Institute Inc. Austria             Paper SAS1440-2015 - SAS Global Forum in Dallas, TX   Abstract When you are analyzing your data and building your models, you often find out that the data cannot be used in the intended way. Systematic patterns, incomplete data, and inconsistencies from a business point of view are often the reason. You wish you could get a complete picture of the quality status of your data much earlier in the analytic lifecycle. SAS® analytics tools like SAS® Visual Analytics help you to profile and visualize the quality status of your data in an easy and powerful way. In this session, you learn advanced methods for analytic data quality profiling. You see case studies based on real-life data, where we look at time series data from a bird’s-eye view and interactively profile GPS trackpoint data from a sail race.   Watch the presentation This SAS Global Forum paper has been published as a number of presentations of in my Data Preparation for Data Science webinar series.   Missing Values in Machine Learning: Why my old aunt Susanne gives data scientists a hard time   Detecting Systematic Missing Values in Cross-Sectional Data   Detecting and Treating Missing Values in Longitudinal data   Profiling the Missing Value Structure of Your Timeseries Data   Verifying Data Quality Using Interactive Data Analysis of GPS-Data of a Sail Race   Simulation Studies to Quantify the Effect of Data Quality on Model Accuracy   DOWNLOAD THE FULL PAPER https://support.sas.com/resources/papers/proceedings15/SAS1440-2015.pdf    DOWNLOAD THE SLIDES Navigate to https://github.com/gerhard1050/DataScience-Presentations-By-Gerhard and download presentation #118.   SAMPLE GRAPHS                 CONCLUSION It is important to differentiate between regular data quality and data quality for analytics. Analytic methods have additional requirements on data quality. But they also offer methods to profile and improve data quality. SAS analytic procedures and SAS Visual Analytics offer a rich set of methods to get insight into the quality status of your data. SAS macros and SAS sample programs help you to profile your data in a very powerful way. It is very important to find out whether there are systematic patterns in your data, such as the systematic patterns that can occur in the case of missing values. Simulation studies give important insight into the consequences of using poor data quality for predictive analytics. SAS® Visual Analytics offers powerful methods to interactively profile your data. It allows you to get closer to the data values to find out where inconsistencies or strange value combinations occur.   References Svolba, Gerhard, 2021. Have a look at your TIMESERIES data from a bird's-eye view - Profile their missing value structure, SAS Communities Svolba, Gerhard, 2021. Replace MISSING VALUES in TIMESERIES DATA using PROC EXPAND and PROC TIMESERIES, SAS Communities Svolba, Gerhard, 2021. Using the TIMESERIES procedure to check the continuity of your timeseries data, SAS Communities Svolba, Gerhard, 2021. The structure of MISSING VALUES in your data - get a clearer picture with the %MV_PROFILING macro, SAS Communities Svolba, Gerhard. 2007. Data Preparation for Analytics Using SAS®. Cary, NC: SAS Institute Inc.  Svolba, Gerhard. 2012. Data Quality for Analytics Using SAS®. Cary, NC: SAS Institute Inc.  Svolba, Gerhard. 2013. “Is your data ready for analytics?” IT Briefcase.    Acknowledgements Many people have helped and inspired me to write and to complete this paper: Udo Sglavo, Sascha Schubert, Mike Gilliland, Anne Milley, Rainer Sternecker, Diane Hatcher, Anette Almer, Anne Baxter, and Robin Langford.   Recommended Reading Svolba, Gerhard. 2006. “Efficient ‘One-Row-per-Subject’ Data Mart Construction for Data Mining.” Proceedings of the Thirty-First Annual SAS Users Group International Conference. Cary, NC. SAS Institute Inc Svolba, Gerhard. 2013. “’The hungry statistician’ or why we never can get enough data.” Subconscious Musings. SAS blog Svolba, Gerhard. 2013. “Why my aunt Susanne and her friends give us a hard time in statistical analysis.” Subconscious Musings. SAS blog Barkaway, David. 2010. “dATa qWaliti 4 Analytics.” Proceedings of the SAS Global Forum 2010 Conference   SAS PRESS BOOKS Data Preparation for Analytics Using SAS Data Quality for Analytics Using SAS Applying Data Science - Business Case Studies Using SAS  ... View more

  Authors  Gerhard Svolba, SAS Institute Inc. Austria         Paper SAS1673-2018 - SAS Global Forum in Denver, CO   Abstract   Is it sufficient just to monitor the quality of your forecast models over time? Can data science methods identify the drivers for large forecast errors and provide more insights than descriptive statistics? Do demand planners really improve forecast accuracy with their manual overwrites? Using a real-life case study, this paper answers these questions. It shows how you can study the impact of factors like product group, forecast horizons, seasonality, or the forecast model type on forecast accuracy and convert them into actionable results. You learn how univariate methods provide first insights into the structure and relationships of your forecast data. You gain insight into how manual overwrites of the statistical forecast change forecast accuracy in both directions and how you use analytical and graphical methods to illustrate these findings. You see how multivariate analytical methods like linear and quantile regression provide additional relevant insight. You learn how to use the GLMSELECT, QUANTSELECT, and QUANTREG procedures to identify the most important influential factors on the forecast error. You see how you can enhance and interpret the output of these procedures to quantify the effects of the influential factors. You learn how to convert the results from the SAS® procedures into actions to improve your forecasting process. The paper shows an outline of how to use the REGSELECT and QTRSELECT procedures to apply these methods in SAS® Viya®.   Watch the presentation       Download the full paper   https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2018/1673-2018.pdf   Download the SLIDES   Navigate to https://github.com/gerhard1050/DataScience-Presentations-By-Gerhard and download presentation #111.   Sample Graphs           Conclusion    You have seen that the application of analytical methods provides many relevant insights to help you make better business decisions. This is not only the case for the analysis of the forecast error as presented in this paper, but also for many other business questions. Svolba 2016 presents a collection of examples and SAS code where relevant business questions are analyzed with analytical methods. In the example presented here, you have seen that the descriptive method also provides a lot of insight. Using linear regression enables you to better quantify the importance of different influential factors and to assess the strength and the direction of different categories. You see that the multivariate analysis provides a more comprehensive picture than the isolated univariate analysis of influential factors. Quantile regression enables you get a clearer picture about the extremes of your distribution. You learn which influential factors trigger the fact that forecast errors do not exceed a certain limit. In the above example you have seen that some variables are important to explain the higher quantiles of the outcome but not the lower quantiles of the outcome. The SAS platform with SAS9 and SAS Viya procedures provides a comprehensive set of analytical methods that enable you gain more insight in the relationships between your data and your business processes.   References   Svolba, Gerhard. 2017. Applying Data Science: Business Case Studies Using SAS®. Cary, NC: SAS Institute Inc. Fildes R., P. Goodwin, M. Lawrence, and K. Nikolopoulos K. 2009. “Effective Forecasting and Judgmental Adjustments: An Empirical Evaluation and Strategies for Improvement in Supply-Chain Planning.” International Journal of Forecasting 25(1): 3–23 (DOI: 10.1016/j.ijforecast.2008.11.010). Gilliland, M. 2010. The Business Forecasting Deal. Hoboken, NJ: Wiley. Svolba, Gerhard. 2006. Data Preparation for Analytics Using SAS®. Cary, NC: SAS Institute Inc. Link Svolba, Gerhard. 2012. Data Quality for Analytics Using SAS®. Cary, NC: SAS Institute Inc. Link   Acknowledgements   Many people have helped and inspired me to write and to complete this paper: Bob Rodriguez, Paul Goodwin, Mike Gilliland, Mihai Paunescu, Albert Tösch, and Robin Langford.   Recommended Reading   Svolba, Gerhard. 2006. “Efficient ‘One-Row-per-Subject’ Data Mart Construction for Data Mining.” Proceedings of the Thirty-First Annual SAS Users Group International Conference. Cary, NC: SAS Institute Inc. Available http://www2.sas.com/proceedings/sugi31/078-31.pdf. Svolba, Gerhard. 2015. “Want an Early Picture of the Data Quality Status of Your Analysis Data? SAS® Visual Analytics Shows You How.” Proceedings of the SAS Global Forum 2015 Conference. Cary, NC: SAS Institute Inc. Available http://support.sas.com/resources/papers/proceedings15/SAS1440-2015.pdf.     SAS PRESS BOOKS Data Preparation for Analytics Using SAS Data Quality for Analytics Using SAS Applying Data Science - Business Case Studies Using SAS  ... View more

Sepsis causes about one in five deaths worldwide; in 2017 this amounted to 11 million lives lost. Most patients in intensive care units (ICUs) still die of sepsis. Mortality rises with every hour treatment is delayed because the diagnosis is missed. However, unnecessary treatment based on a wrong diagnosis  causes resistance to antibiotics. In addition, ICU sepsis mortality varies a lot by region. Therefore, there is need and room for improvement! Fortunately, many ICUs already have electronic medical records available that are updated in near real-time. Together we can unleash the potential of these data and existing algorithms to contribute to prevent sepsis and save lives.       Short Video (2 min 54 sec)     Long Video (10 min 57 sec)                                                                                                                                 Team Name Team SIRS/Sepsis Track Healthcare Use Case Fight against sepsis, the leading course of death in critically ill patients Technology SAS Visual Analytics, SAS Visual Statistics, SAS Visual Data Mining and Machine Learning Region EMEA Team lead Verena Schneider-Lindner Team members Verena Schneider-Lindner, Chris Hull @chrishull, Anna Meagan Fairley, Navjot Singh   ... View more

This is a navigation article for all my SAS Communities contributions. It is used as a central navigation point for the content that I provided at this platform and is updated with every new article that I publish. Last updated: 2026-01-16 Articles are clustered by different content dimensions, thus they might appear more than once in the list below.     Most Recent Articles   Creating Synthetic Data using PROC SMOTE, PROC SIMNORMAL and PROC TABULARGAN   How I made your model using SAS Viya series   Season 1 - The NoCode/LowCode experience   Feature Engineering   Feature Engineering 1 - Using Correlation Analysis to Describe Behavior over Time Feature Engineering #2 - Accordance with predefined pattern Feature Engineering #3 – Describing the Trend over Time 3 ways to consider movable holidays in SAS   Data Quality for Analytics   The structure of MISSING VALUES in your data - get a clearer picture with the %MV_PROFILING macro Using the TIMESERIES procedure to check the continuity of your timeseries data Replace MISSING VALUES in TIMESERIES DATA using PROC EXPAND and PROC TIMESERIES Have a look at your TIMESERIES data from a bird's-eye view - Profile their missing value structure Using SAS Enterprise Miner for Predictive Modeling Simulation Studies Using the GLMSELECT procedure to calculate individual reference values in outlier detection Early Picture of the Data Quality Status of Your Analysis Data? SAS® Visual Analytics Shows You How   Data Assembly Transpose your analysis data with the %MAKELONG and %MAKEWIDE macro      Tipps and Tricks   Automatically highlight data-driven events with reference lines in line-charts 3 ways to consider movable holidays in SAS   Analytics and SAS/STAT Procedures   Encoding of CLASS Variables in Regression Analysis - Better understand the ORDINAL encoding Display the hidden estimate for the reference category in EFFECT coding for better interpretability %CALC_REFERENCE_CATEGORY displays the "hidden" coefficient in EFFECT encoding for CLASS variables     Synthetic Data, Simulating Data and Models   Creating Synthetic Data using PROC SMOTE, PROC SIMNORMAL and PROC TABULARGAN Simulate timeseries data with a SAS DATA Step and SAS Functions Using SAS Enterprise Miner for Predictive Modeling Simulation Studies   SAS Global Forum Papers SUGI2006: Efficient “One-Row-per-Subject” Data Mart Construction for Data Mining SGF2015: Early Picture of the Data Quality Status of Your Analysis Data? SAS® Visual Analytics Shows You How SGF2018: Getting More Insight into Your Forecast Errors with the GLMSELECT and QUANTSELECT Procedures   References   Find more details in my webinars at Youtube: DataPreparation4DataScience  Data Science Use Cases and on Medium | LinkedIn | Github | SAS-Books   Most of the content of these articles is taken from my SAS Press books Data Quality for Analytics Using SAS Applying Data Science - Business Case Studies Using SAS Data Preparation for Analytics Using SAS       ... View more

Yes, but in this month we often see higher values. Yes, but in this segment there are much older customers, therefore the profit ration might be different. Yes, but ...   You might hear such replies frequently when you present your analytic results to business users. Especially when you highlight outliers, anomalies or suspicious observations that are quite distant form the expected value.   Different from simple one-size-fits-all average values, analytic methods allow to calculate individual reference values that already consider seasonal pattern, trends, and other co-variables of your analysis subjects. Consequently the highlighted observations are not selected because of its absolute value but based on its deviation from their individually expected value.   This article illustrates how you can use the GLMSELECT procedure to calculate an individual reference value per analysis subject. This reference value is based on the value of other variables for this subject and thus considers his individual situation. If the actual value then lies outside the boundaries for this reference value, you know that you get a much more specific alert compared to a comparison against the overall mean.   In some of my data science webinars I have illustrated such examples. You find the links to the videos at the end of this article.   The Available Data   The available data for this demo example are from car insurance. The bluebook value for the car in $ is available, as well as other variables like the car age in months, the car type, the car value, and the average travel time to work.   proc print data=work.carvalue(obs=10); var SequenceID policyno bluebook car_type car_use Car_Age_Months travtime; run;       The Basic Idea of the Analysis   The idea is to calculate stratified values for the bluebook that base on these variables. One example can be seen in the boxplot below, where different bluebook distributions by car type can be seen.   proc sgplot data=work.carvalue; vbox bluebook / category=car_type; run;     You see from the chart, that it does not make sense to compare different car types against one common reference value for bluebook.     Calculating an Expected Value based on other Variables   You can use the GLMSELECT procedures (and many other procedures in SAS/STAT, SAS/Visual Statistics or SAS/Visual Data Mining and Machine Learning) to build a model that explains the bluebook values and to calculate an expected value for bluebook.     proc glmselect data=CarValue; class car_use car_type ; model bluebook = Car_Age_Months car_use car_type travtime / selection = none; output out=pred_bluebook p=reference r=residual; run;   You use the explanatory variables in the MODEL statement as input variables. Note that if you use a selected subset of variables it might make sense to turn variable selection off, in order to make sure that you stratify by all your desired variables. You have to list variables that contain categories in the CLASS statement. You use the OUTPUT statement to write the predicted value for bluebook in a new dataset "PRED_BLUEBOOK". This variables is name REFERECE in this example. You see that the parameter estimates reflect the dependency of the bluebook variable on the input variables.      If you would like to calculate the predicted bluebook values for observations from other datasets ("Fresh Data") you can use the CODE statement to export the datastep code that allows to score new observations.   You can now use variable REFERENCE to compare it with the actual bluebook value per analysis subject and see how far (e.g. in standard deviations) BLUEBOOK apart from the expected value.   Preparing for the comparison   For this comparison you first calculate the mean and the standard deviation for bluebook and also calculate an 3 standard deviation upper boundary based on the overall mean (mean + 3 * standard deviation). You can use PROC SQL for that calculation and use the INTO expression to store the calculated statistics in macro variables.   %let sigma=3; proc sql; select mean(bluebook) as bluebook_mean, std(bluebook) as bluebook_std, calculated bluebook_mean + calculated bluebook_std*&sigma. as bluebook_3sigma into :bluebook_OverallMean, :bluebook_OverallStd, :bluebook_Overall3sigma from CarValue; quit;   In order to be able to display the analysis subject in ascending order according to their expected value, you sort it with the SORT procedure.   proc sort data=pred_bluebook out=pred_bluebook; by reference; run;   Calculating the individual Upper Limit    Finally you use the output data from the GLMSELECT procedure and calculate variable UpperLimitIndividual based on the individually predicted value (REFERENCE) plus 3 standard deviations. data pred_bluebook; set pred_bluebook; SequenceRef = _N_; UpperLimitIndividual = reference + &bluebook_OverallStd*&sigma.; format Segment $2.; Segment = cats(put(bluebook > &bluebook_Overall3sigma,$1.), put(bluebook > UpperLimitIndividual,$1.)); run; Note that this code calculates variables SEQUENCEREF based on the ROWID (_N_) of the sorted data. creates variables SEGMENT by comparing the BLUEBOOK variable against the overall-3-sigma limit (contained in macro variable BLUEBOOK_OVERALL3SIGMA) and the individual limit UpperLimitIndividual Variable SEGMENT is a two-character variables for this two comparisons which is 0 if the limit is not exceeded and 1 otherwise. Here variable SEGMENT is defined as the concatenation (SAS function CATS) of the result of the two comparisons. You could also use IF/THEN/ELSE statements to calculate them, as shown here.   /*** if bluebook > upper_indiv then do; if bluebook > &bluebook_Upper then Segment = "11"; else Segment ="01"; end; else do; if bluebook > &bluebook_Upper then Segment = "10"; else Segment = "00"; end; ***/   Plotting the Results   You can now use the SGPLOT procedure to illustrate the datapoints and whether they exceed the individual and/or the overall limits.   First you create an attribute map for the individual colors for the four cases.   data attrmap; input id $8. @10 value $2. @20 markercolor $14.; datalines; Segment 00 MediumSeaGreen Segment 01 orange Segment 10 DodgerBlue Segment 11 Firebrick ; run;   The you use the SPLOT procedure to plot the datapoints in the respective colors as well as the individual and overall reference lines.   proc sgplot data = pred_bluebook dattrmap=attrmap; scatter y=bluebook x=SequenceRef / group= segment markerattrs=(size = 5 symbol=circlefilled ) attrid=segment; step y=UpperLimitIndividual x=SequenceRef / lineattrs=(color=Black); step y=reference x=SequenceRef / lineattrs=(color=Black thickness=3) ;*curvelabel="Reference Value" curvelabelpos=end; refline &bluebook_OverallMean / axis=y lineattrs=(color=grey) label ="Overall Mean"; refline &bluebook_Overall3sigma / axis=y lineattrs=(color=grey) label="Mean+3Sigma"; run;   The SCATTER statement plots the data points as filled circles. The first STEP statement plots the individual 3-sigma upper reference line. The second STEP statement plots the individual reference value. The REFLINE statements plot the overall 3-sigma upper reference lines as well as the overall mean.   Interpreting the Graph   In the graph you find on the x-axis the data points for each analysis subject ordered by increasing value of REFERENCE (y-axis). You see how the individual reference values as step lines differ from the "one-size-fits-all" overall means and provide more insight whether a value might be too high or not. In GREEN you see data points that are considered to be within in the limits with both methods, overall and individual. Data points that look fine in an overall comparison, but lie above its individual value are shown in YELLOW. These are the cases which you would miss when only looking at overall limits. Data points that lie within the individual limit but would be classified as "too high" when just comparing with the overall mean are shown in BLUE. These are the cases that you would falsely alert when only looking at overall limits. Observations that lie outside of both limits, overall and individual are shown in RED.       Conclusion and Business Applications   This example and the graphical representation show how you can easily calculate and visualize individual reference limits. You also see how you can make much more informed decisions about classifying an observation as "too high" or not.   There are many business applications for this method: Outlier detection and data quality: You can verify whether your data points are within certain limits based on individual thresholds. Auditing: Based on the individual reference values, you can select those applications that have a value which is above their individually expected value. This also prevents you from looking at many cases that lie above the overall limits because of explainable effects.   Links and References   This example has been taken from my SAS Press book "Data Quality for Analytics Using SAS", see chapter 13.4, page 171 Further books of the author in SAS Press: Applying Data Science - Business Case Studies Using SAS Data Preparation for Analytics Using SAS             ... View more

This article introduces the %PROFILE_MV_TS macro and illustrates a different view on your time series data. You will learn how you can profile your data for missing values, zero values as well as different start points and end point of groups of timeseries.   Note that a webinar on this topic has been recorded in my "Data Preparation for Data Science" webinar series. You find a link to this session at the end of this article. #datapreparation4datascience Yes! Line-charts are helpful, but:   Usually we use a line-chart to "look" at our timeseries data. Sure, this is definitely a good way to visualize trends, seasons, cycles, outliers in your data and it allows you to decide about the modeling strategy that you would like to apply.       From a data quality point of view, however, you would in addition like to get a different look at our data. You would like to see Are there missing value in the time series? Are the zero values in the time series? Do you have time periods where we have a large cumulation of missing values or zero values? Are there leading or trailing missing values or zero values for time series, which you have to treat before we run the timeseries analysis? What is the length of our timeseries? Do they have a sufficient data history to run timeseries forecasting models? Do your timeseries have different start- and end-dates? Usually it does not work to browse through your data table to answer that questions. If you have a large number of time series you won't be able to see such pattern.   Counting the number of missing values for each time series and comparing it with the number of observations identifies the proportion of missing values. However, it makes a difference whether these values occur within the series or at the beginning or end of the series. It also makes a difference whether the missing values occur in blocks or are distributed over different periods.   Missing or zero values at the beginning or end of a series may result when no data are available before or after a certain point in time (for example, when a product has not been launched before a certain date or is not sold anymore after a certain date).   Change your view! Look at your data from a bird's-eye view.    Imagine you could look at your timeseries data from a bird's-eye view as shown in the following graph.          This diagram does not focus on timeseries elements like trend or seasonality. However it gives you insight into the data quality status of your time series. Time series are shown in the graph as horizontal rows over the time axis. Grey regions represent non-zero values of the timeseries. Missing values in the timeseries are represented with red markers. Zero values are shown as yellow markers. The ordering of the timeseries at the y-axis considers the start-time, the length and the number of missing value or zero values of the time series. This allows you to see important details about your timeseries data that you want to know before you start analyzing them. It allows the data scientist to decide whether additional data preprocessing is required and how data should be used in the analysis.   Use the %PROFILE_TS_MV macro to obtain a bird's-eye-view on your timeseries   You can use the %PROFILE_TS_MV macro to create a view on timeseries as shown in the above graph. Use the program "CreateTimeseriesDemodata.sas" to create artificial timeseries data. Of course you can also use your own time series data. Load the "PROFILE_TS_MV macro, which is contained in the attached program "PROFILE_TS_MV_Macro.sas". Run the following code to obtain the results. %Profile_TS_MV(data = timeseries_demodata, id = tsid, date = MonYear, value= Quantity, mv = (.), zv = (0), scatterplot=YES, NMAX_TS=1000);   Maybe you want to use ODS GRAPHICS before the macro to specify the size for a (larger) graph. ods graphics / width= 18cm height=13cm; The macro outputs the following tables and the above shown graph.   The TS_PROFILE_CHAIN First you receive a table with a frequency count for the TS_PROFILE_CHAIN. This chain is a concatenation of the following characters for each timepoint: 1: non-zero and non-missing value 0: zero-value X: missing-value Thus a TS_PROFILE_CHAIN of 111111111_9_0 represents a timeseries with 9 timepoints and for each timepoint a non-zero and non-missing value was found. Whereas a TS_PROFILE_CHAIN of 00111XX111000_13_2 represents a time series with 13 datapoints, with 2 (embedded) missing-values and 2 leading and 3 trailing zero values.   From the frequency table below you can see the frequency distribution of the TS_PROFILE_CHAIN. The data contains 96 timeseries with a length of 24 months, 79 time series with 48 months.  You also see, that you have 35 timeseries of length 12. You might want to consider a different method for time series forecasting for these timeseries compared to those where you have a length of 24 months or more. There is also a group of timeseries with 6 months of leading zeros. In retail sales this might indicate that the product was already available in the system, before it was actually sold in the shops and therefore create zero-values. You might want to remove these periods from your time series. Note that the title displays the number of individual timeseries and the dataset name.     Frequency of the TS_PROFILE_CHAIN      There is also a compressed version of the TS_PROFILE_CHAIN. Here only changes between values of 1, 0 or X are indicated.   Compressed version of TS_PROFILE_CHAIN     You see that all timeseries with non-zero or non-missing values are represented in the "1" category and those with leading zeros are represented in the "01" category.   Other tables show the distribution of the length of the time series and the distribution of missing values per time series.             Ordering the timeseries for the display in the graph   Note that the macro orders the timeseries in order to be able to display it (as shown in the above picture) in a grouped way based on startdate, timeseries length and number of missing values or zero values. With the following code you could create the above graph in an "unsorted" way. proc sgplot data=MV_PROFILE_TS_PLOT dattrmap=attrmap; title2 Profile Plot for the Missing Value Structure of the Time Series; scatter x=monyear y=_ID_ / group=MV_Ind markerattrs=(symbol=squarefilled) attrid=MV_Ind; run; You see that the ordering from the macro gives much more insight into the structure and blocks compared to the non-sorted display as shown below.       From a data quality perspective with the focus to check the length and the completeness of the time series, the above graphs still give you more insight compared to the pure line chart. proc sgplot data=timeseries_demodata; series x=MonYear y=Quantity / group=tsid; where quantity ne 0 ; run;     The Macro %PROFILE_TS_MV   Note the prerequisites for using the macro: Each time series needs to have an &ID variable. If a time series with the same &ID occurs more than once in the data (for example, per sales region or sales channel), the respective cross-sectional dimensions variable needs to be specified with the &CROSS macro variable. The macro then concatenates these variables to a new ID variable. As a best practice, use GIF or JPG as graphical devices, especially if the input data have hundreds of time series. Otherwise, if JAVA or ACTIVEX is used, creating the profile plot is very time consuming. The statement GOPTIONS DEVICE = GIF can be used before the macro call to change the graphics device to GIF.   The following parameters can be specified with the macro:   DATA Name of the input data set (mandatory)   ID Name of the time series ID variable (mandatory).   CROSS List of variables for the cross-sectional dimensions (optional). The values of these variables are concatenated first to a string and then to the ID variable in order to allow the analysis of the same time series ID for different subgroups.   DATE Name of the time ID variable (mandatory).   VALUE Name of the variable that holds the values of the time series (mandatory).   MV List of values to be considered as missing. Values need to be specified in brackets, separated by a comma. Examples are mv=(.) and mv=(.,9). The default value is (.). Note that this list is used with an IN operator in the DATA step.   ZV List of values to be considered as zero. Values need to be specified in brackets, separated by a comma. Examples are mv=(0) and mv=(0,-1). The default value is (0). Note that this list is used with an IN operator in the DATA step.   PLOT Profile plots are only produced if PLOT is set to YES. Default = YES.    LINEPLOT Shall the profile plot be generated as a line plot. YES|NO. Default = NO   SCATTERPLOT Shall the profile plot be generated as a scatter plot. YES|NO. Default = YES   NMAX_TS Depending on the number of time series in the data, this option controls whether a profile plot will be produced. The default number is 500. Thus, if your data contain more than 500 time series, a profile plot will not be produced and you will need to set the value higher. In this case, the following message in the log file is produced:   ------------------------------------------------------------------------------ Number of time series = 1026, is higher than nmax_ts value(= 100). No plot has been created. Reset parameter NMAX_TS to a value of at least 1026 -----------------------------------------------------------------------------   W Defines the thickness of the lines used in the line plot to represent one time series.     Links and References   This example has been taken from my SAS Press book "Data Quality for Analytics Using SAS". Further books of the author in SAS Press: Applying Data Science - Business Case Studies Using SAS Data Preparation for Analytics Using SAS: See page 250f for examples on how to aggregate and disaggregate for different levels of granularity at the time axis.   Related SAS-Communities articles The structure of MISSING VALUES in your data - get a clearer picture with the %MV_PROFILING macro Using the TIMESERIES procedure to check the continuity of your timeseries data Replace MISSING VALUES in TIMESERIES DATA using PROC EXPAND and PROC TIMESERIES             ... View more

This article illustrates how you can use the EXPAND and the TIMESERIES procedure to replace missing values in timeseries data. A separate SAS Communities article "Using the TIMESERIES procedure to check the continuity of your timeseries data" focuses on the problem of missing records in your analysis data. Note that in order to run PROC TIMESERIES and PROC EXPAND you need SAS/ETS.   Replacing Missing Values with PROC TIMESERIES   This section discusses using the TIMESERIES procedure to replace missing values in time series data. Missing values in this context mean that the missing values occur explicitly in time series data where the value for a certain time period is missing.   PROC TIMESERIES allows you to replace missing values by using one of the replacement methods listed in the table below. These methods are controlled with the option SETMISS. For details, refer to the documentation of PROC TIMESERIES, section ID statement, SETMISS option.    Option value Missing values are set to <number> Any number. (for example, 0 to replace missing values with zero) MISSING Missing AVERAGE Average value of the time series MEDIAN Median value of the time series MINIMUM Minimum value of the time series MAXIMUM Maximum value of the time series FIRST First non-missing value LAST Last non-missing value PREVIOUS Previous non-missing value NEXT Next non-missing value   PROC TIMESERIES Examples   In order to demonstrate how to use PROC TIMESERIES to impute missing values, artificial missing values are inserted in the SASHELP.AIR data set. A new variable, AIR_MV, is created as a copy of the AIR variable and approximately 15% of the missing values are inserted: data air_missing;  set sashelp.air;  if uniform(12345) < 0.15 then air_mv = .;  else air_mv = air; run; The following table shows the first 17 rows of table AIR_MISSING with missing values for April and July 1949 and March 1950. Note that the AIR variable is only kept for explanatory purposes. In a real-world situation, variable AIR would not exist, and the missing values found in AIR_MV would need to be replaced for the analysis.       PROC TIMESERIES can now be used to set missing values to zero with the following code:   proc timeseries data = air_missing  out = air_setmissing_zero;  id date interval =month setmiss=0;  var air_MV; run;   Another example shows how to replace missing values with the previous value in the time series:    proc timeseries data = air_missing   out = air_setmissing_previous;   id date interval =month setmiss=PREVIOUS;   var air_MV;  run; A third example shows how to replace missing values with the mean value in the time series:   proc timeseries data = air_missing  out = air_setmissing_mean;  id date interval =month setmiss=MEAN;  var air_MV; run;     In the following table the output data sets of these three examples have been joined together for illustrative purposes.   Different replacement methods in PROC TIMESERIES   In the table above only a subset of the observations of a timeseries with an upward trend are shown. As the mean for the replacement in the last column has be calculated based on all observations of the time series, it looks like as it would be to high for this subset of observations.   Note that you can also specify a BY statement with PROC TIMESERIES in order to replace missing values by BY group.   Changing zero values to missing values     PROC TIMESERIES also offers the ability to change zero values to missing values. This is important if the data show zero values, which, for the analysis, however, should be interpreted as missing values (for example, the introduction of new products or the retirement of products in the retail or fashion industry). Here, the zero value represents different phases of the product lifecycle rather than a comparable quantity.     proc timeseries data=sales_original out=sales_corrected;  id date interval=month zeromiss=both;  var sales; run; The first table shows the data before PROC TIMESERIES is applied, and second table shows the data after PROC TIMESERIES has set the leading and trailing zeros to missing. Note that beside ZEROMISS=BOTH, which replaces both leading and trailing zeros, LEFT and RIGHT can also be used as option values.   Data with leading zeros     Data with zeros replaced by missing values     Interpolating Missing Values in Time Series Data with PROC EXPAND   The EXPAND procedure allows you to convert time series data from one sampling interval to another and to interpolate missing values. Time series data can be collapsed from a higher frequency interval to a lower frequency interval or expanded from a lower frequency interval to a higher frequency interval. Monthly data can be aggregated into annual data, and quarterly data can be interpolated from an annual series.  Aperiodic time series that are observed at non-regular points in time can be converted into periodic estimates. For example, events that are documented at random time points can be interpolated to form weekly average event rates.   This section focuses on interpolating missing values with PROC EXPAND.   Statistical methods in PROC EXPAND By default, PROC EXPAND fits cubic spline curves to the non-missing values of the input variable to form continuous approximations of the time series. The respective output series are then generated from the spline approximations. Different from commonly used natural spline methods, which use zero second-derivate endpoint constraints, the SPLINE method in PROC EXPAND uses the not-a-knot method by default, which has proven to be more appropriate for time series data. For more details see the SAS documentation for PROC EXPAND, chapter “Conversion Methods”   Other methods to interpolate missing values with PROC EXPAND include: the JOIN method, where interpolations are calculated based on the successive straight line collection between the non-missing points. the STEP method, which is a discontinuous piecewise constant curve where the value is interpolated from the most recent non-missing value.   Examples for PROC EXPAND   This example demonstrates how to use PROC EXPAND based on the data that have been used in the PROC TIMESERIES example above.   With the following SAS statements, a new variable, AIR_EXPAND, is produced that contains the non-missing values of AIR_MV as well as interpolated values for those months where AIR_MV is missing:   proc expand data = air_missing out = air_expand;  convert air_mv=air_expand;  id date; run; The following table shows the first 17 rows of data set AIR_EXPAND. The interpolated values are stored in variable AIR_EXPAND.   Imputed values with PROC EXPAND   PROC EXPAND can also be used with a BY statement if the input data contain different time series in a cross-sectional format that are identified by one or more BY variables.   Assuming that the SASHELP.AIR data set contains a COUNTRY variable that separates the number of airline passengers by country, the previous code example would appear as follows:   proc expand data = air_missing out = air_expand;  convert air_mv=air_expand;  id date;  by country; run;   Links and References   This example has been taken from my SAS Press book "Data Quality for Analytics Using SAS". Further books of the author in SAS Press: Applying Data Science - Business Case Studies Using SAS Data Preparation for Analytics Using SAS: See page 250f for examples on how to aggregate and disaggregate for different levels of granularity at the time axis.   Related SAS-Communities articles The structure of MISSING VALUES in your data - get a clearer picture with the %MV_PROFILING macro Using the TIMESERIES procedure to check the continuity of your timeseries data                 ... View more

This articles illustrates how you can use the TIMESERIES procedure to check whether your timeseries data contain a record for every time period and how to insert records for these periods. The article illustrates the rationale for checking your timeseries data for missing records and introduces the %CHECK_TIMEID macro that automates the tasks of checking time series data and inserting records.   Note that the TIMESERIES procedure is part of the SAS/ETS package, thus you only can run the code if you have SAS/ETS licensed. You could create a workaround solution using a SAS Datastep, however as soon as you have BY-groups in your data your SAS Datastep code gets complicated.       MISSING RECORDS or MISSING VALUES?   Timeseries data with missing values and missing records     Consider the following table: You can easily detect a missing value for variable AMOUNT in July 2004. However there is more missingness in the data. If you investigate the DATE variable you see the records for October and November 2005 are missing. The December record immediately follows the September records. Also in 2006 a couple of months are missing in the time series. How can that happen? It might be that the data have been deleted accidentally or where not properly loaded from the source systems. If the data in this table have been aggregated from transactional data, it might be the case that no transactions took place in the particular month. Thus the aggregation did not return a value.   Lets have a look at such an example in more detail.        The difference between transactional data and time series data   In order to explain the necessity for checking and correcting the completeness of time series data, it is important to differentiate between transactional data and time series data. Transactional data are time-stamped data that are collected with no particular frequency over time. Examples of transactional data include Internet data like website visits or point-of-sale data in retail. Time series data, on the other hand, are time-stamped data that are collected at a particular frequency over time, like web hits per hour or sales amounts per day. The following 2 tables show examples of transactional data and time series data, which have been aggregated on an hourly basis.   Transactional Data   Here is the aggregated version of this data:   Timeseries Data       Note that in the aggregated table, there is no record for time 5:00:00 because no web server traffic occurred during that time. For time series analysis, however, the records need to be equally spaced at the time axis. The existence of all consecutive periods is called contiguity. Time series that are not contiguous can cause problems in analyses because the time series methods will not work properly.     Non-continuous time series data cause problems in the analysis   From the SASHELP.AIR data, approximately 10% of the records have been deleted with the following statements: data air_missing;  set sashelp.air;  if uniform(12345) < 0.1 then delete; run; From the following table, you can see that now some records are missing (for example, there is no record for month JULY 1949).       Next, a time series analysis with PROC ESM is executed:   proc esm data = air_missing;  id date interval = month;  forecast air; run;   PROC ESM checks the data and recognizes that the specified ID variable DATE is not contiguous with the specified INTERVAL MONTH. Thus, the following warning is displayed in the SAS log:   WARNING: 1 omitted observations have been detected before observation  number 7 according to the INTERVAL=MONTH option and the ID  values. The current ID is DATE=AUG1949, the previous ID was  DATE=JUN1949.   Similar warnings are displayed when the data are analyzed with PROC FORECAST or PROC ARIMA.     Checking and assuring contiguity     From this example, you can see that it is important to check time series data for contiguity before running the analysis. If a non continuous time point is detected, the missing records need to be inserted into the data. Note that the point here is not that missing values will be imputed with another value, but that missing records will be inserted into the data, most likely with a value of 0.   Inserting a record with a zero value is plausible when, for example, data such as those shown in the two tables above are considered. Because there was no web server traffic at hour 5, no records are found in the transactional data for this time interval and, thus, the respective record is not created in the time series data. If a record is inserted, the value that makes most sense for this interval is 0 because it represents no traffic.   There are various ways to check for missing records and to insert those records. Implementations include DATA step programs that check for the time interval between two records as well as the creation of a master table that holds a contiguous set of records, which is then merged with the original data to find records for the missing time intervals.   These implementations, however, can become very complicated for time series data with cross-sectional dimensions. In this case, it is necessary to check the contiguity for all possible cross-sectional dimensions that also may have different start and end points.     Using the TIMESERIES procedure to handle missing records     A very smooth implementation of this task can be achieved with PROC TIMESERIES, which provides the following functionalities: Aggregates, aligns, and describes transactional or time series data Converts transactional data into time-stamped data Performs seasonal decomposition of the data Checks for and inserts missing records Handles and converts missing and zero values A typical PROC TIMESERIES example is shown here:   proc timeseries data = air_missing  out = TIMEID_INSERTED;  id date interval = MONTH setmiss=0;  var air; run;   The ID statement sets the time variable and the interval spacing. Here, the SETMISS option defines which values to insert for the VAR variable AIR if a missing record is detected. Missing records are identified automatically based on the ID variable and the spacing.     The %CHECK_TIMEID macro     A macro has been coded to help you use PROC TIMESERIES to check for and insert missing records in timeseries data.   This macro can run in two modes: CHECK mode and INSERT mode. CHECK mode checks for the existence of missing records.  INSERT mode inserts the missing records.   Note that INSERT-mode is very similar to the code PROC TIMESERIES shown above. The rationale of the macro is to provide you a single tool to check for missing records first, and discuss the resulting table with IT and business people. And then insert the the missing values in the second step with almost the same syntax.   Running this macro in CHECK mode for the AIR_MISSING data with the following syntax generates the records in the TIMEID_MISSING data set as shown below.     %check_timeid(data = air_missing,  mode = CHECK,  timeid = date,  value = air);     If there were no missing records from a contiguity perspective, the data set would have been empty. Note that the displayed values for the AIR variable for these records are only an arbitrary value that is used to check for contiguity in the data. They are not inserted into the original table.                       Running the macro in INSERT mode with the following syntax creates an output data set called TIMEID_INSERTED: %check_timeid(data = air_missing,  mode = INSERT,  timeid = date,  value = air);     For month JUL1949, a record has been inserted and the value of AIR has been set to 0. This data set now fulfills the requirements of contiguity.     Code Example to use the %CHECK_TIMEID macro     Note that when using the macro, you can specify a list of variables that receive 0 values for the inserted records. And the macro can also be run with one or more BY variables to insert records if the data are structured with cross-sectional dimensions. For example, if there are missing records in the SASHELP.PRDSALE data (or in a copy of the data set called PRDSALE_MISSING), these records can be inserted for each BY group with the following statements:     %check_timeid(data = prdsale_missing,  out = prdsale_insert,  mode = INSERT,  timeid = month,  value = actual,  by = country region division prodtype product);     You can see from the syntax that a list of variables defining the cross-sectional dimensions has been specified with the BY variable. Note the following from the syntax: PROC TIMESERIES is used to check for missing records and to insert the missing records. Inserting the missing records is done using typical PROC TIMESERIES syntax. An insert value different from 0 can be specified with the INSERTVALUE macro variable. Checking for missing records is also done with PROC TIMESERIES: Here, records with an arbitrary value of -123456789.123456789 are inserted if a record is missing. The inserted value is then used to identify the inserted records. If the default value of -123456789.123456789 conflicts with real data, a different value using the CHECKDUMMYVALUE variable can be specified. Cross-sectional dimensions are treated with the BY statement in PROC TIMESERIES. If BY variables are specified with the macro, the input data are sorted for these variables, which may take some time for large data sets.   Parameters of the %CHECK_TIMEID macro   The following variables can be specified with the macro:   DATA   Name of the input data set that holds the time series data (mandatory).   OUT   Name of the output data set that holds the original data plus the inserted records. Default = TIMEID_INSERTED.   OUT_CHECK   Name of the output data set that holds missing records. Default = TIMEID_MISSING.   MODE   Defines whether the macro will run in CHECK or INSERT mode. Valid values are CHECK or INSERT. Default = INSERT.   TIMEID   Name of the time ID variable (mandatory).   INTERVAL   Time interval of the time series data. This interval is used to check for contiguity.   VALUE   Name(s) of the value variable(s) that receives the inserted value (mandatory). Note that a list of variables or the global variable _NUMERIC_ can be specified here to denote all numeric variables in the data set.   INSERTVALUE   Value that will be inserted into the value variable for missing records. Default = 0.   BY   Optional variable that contains the names of the variables that define the cross-sectional dimensions.   CHECKDUMMYVALUE   Arbitrary value that is inserted into the data in CHECK mode to identify the missing records. Default = 123456789.123456789.     Links and References   This example has been taken from my SAS Press book "Data Quality for Analytics Using SAS". Further books of the author in SAS Press: Applying Data Science - Business Case Studies Using SAS Data Preparation for Analytics Using SAS   Related SAS-Communities articles The structure of MISSING VALUES in your data - get a clearer picture with the %MV_PROFILING macro Replace MISSING VALUES in TIMESERIES DATA using PROC EXPAND and PROC TIMESERIES       ... View more

Missing values are often profiled in a univariate way. This however only tells you how many variables are infected by the "missing value" disease to what extent. It does not give you insight on how many full records you have or if there is a structure of missing data.         However you should be interested to profile the missing value structure of the variables in your analysis data. This can give you insight whether there are relationships in the data that you should be aware of, before you start with the analysis.   This article introduces the %MV_PROFILING macro which allows you to profile missing values in your data in a more comprehensive way. You receive insight into the missing value pattern and potential relationships of the data. The %MV_PROFILING macros has been published with my "Data Quality for Analytics Using SAS" book in 2012. It also has been featured in my SAS Global Forum presentation "Want an Early Picture of the Data Quality Status of Your Analysis Data? SAS® Visual Analytics Shows You How" in 2015.    The output of the %MV_PROFILING macro shows you for example how many FULL RECORDS (records with any missing value in one of the variables), how many records with a missing value in one variable, or with missing values in more than 4 variables exist.     In this article you find usage examples and the interpretation of the output of the %MV_PROFILING macro. It also shows explains the macro parameters.   The idea of the %MV_PROFILING macro   The missing value profiling (%MV_PROFILING) macro defines a segmentation of the observations based on its missing value structures. For each variable, a dummy variable is created that indicates whether a value is missing or not. This set of indicator variables is then concatenated into a string. The following illustrates the definition of the profile chain.       Note that after the profile chain itself, the total number of missing values per observation is concatenated separated by a ‘_’. This approach shows the number of missing values for each profile and is very useful when larger numbers of variables are analyzed. Using the %MV_PROFILING macro has many advantages: It provides insight into the structure of missing values across variables. It identifies the frequency and accumulation of missing value patterns. It shows information efficiently. Instead of looking at multiple graphs and reports, an analyst can get a quick overview of the missing value structure.   Data to illustrate the %MV_PROFILING macro   Data from the HMEQ data set, which is frequently used in SAS Enterprise Miner or SAS VDMML examples and demos, illustrate the missing value profile chain macros. The data set is available together with the programs and macro in the attachment to this article.. The HMEQ data set contains histories of credit customers with different types of home equity loans. The variables shown in the following table were used to analyze the missing values. Variable Name Description BAD Defaulting or repaying the loan DELINQ Number of delinquent trade lines DEROG Number of major derogatory reports NINC Number of recent credit inquires CLAGE Age (in months) of the oldest trade line CLNO Number of trade lines JOB Current job, six categories     Simple reports based on the missing value profile chain   The following table shows a lookup table that displays the MV_PROFILE assigned to each variable. This identifies which digit of the missing value profile chain is used for which variables and eases navigation through the %MV_PROFILING outputs.     MV_PROFILE pattern for each variable  The order of the 0s and 1s in the %MV_PROFILE_CHAIN macro depends on the order of the variables names in the &VARS macro variable. If the &VARS parameter is omitted in the macro, all variables of the data set are used in alphabetical order for the %MV_PROFILING macro by default. The macro variable ORDER=POS can be used to force the order to match the order in the data set.                       The following output shows the frequencies of the different patterns in the %MV_PROFILING macro. Output of the %MV_PROFILING macro for the HMEQ data set   First, the output shows that there are 82.6% complete records without missing values. The next line shows that there are 174 observations (2.9%) that have a missing value for the third variable (in this case, variable DEROG). The next entries in the table reveal that in 2.5% of the cases three variables are missing together (namely, variables DELINQ, DEROG, and NINQ) (the second, third, and fourth variables) and in 2.2% of the cases variables DELING and DEROG are missing. This provides first-hand insight not only into the frequency but also into the structure of the missing values.   Displaying the pattern distribution in a TILE-chart   Tile chart for the frequency of the %MV_PROFILING macro   This information is also displayed as a tile chart. A tile chart splits a rectangle that represents the population into smaller rectangles, where the area of each rectangle is proportional to the frequency of the respective category. In this case, this gives a graphical representation of the proportion of the complete records and the records with missing values. Additionally, it can easily be seen that the top four patterns with missing values make up more than 50% of all missing value patterns.         The next two output examples show the distribution of the number of missing values per record and a ranked list of missing values per variable. Distribution of the number of missing values per record   This table shows that there are 82.6% complete records and that in 6.4% (2.7+1.5+2.2) of the cases three or more variables have a missing value.                         Ranked list of missing values per variable Looking at this table, you can see that target variable BAD does not include any missing values. Combining this information in the MV_Profile chain above lets us conclude that in 2.2% (pattern 0111111_6) of the cases all input variables are missing.                   Advanced reports based on the missing value profile chain   In the next step, the structure and patterns of the missing values are analyzed in more detail. In order to do this, a variable clustering and a principal component analysis are applied to binary indicator variables that were created earlier. The binary indicator variables are assigned a value of 1 if the respective variable has a missing value and 0 otherwise. For this analysis, all observations without missing values are excluded from the analysis because they do not provide any additional insight.   Note that from a statistical point of view, variable clustering and principal components analysis are not intended to be used with binary variables. Here the methods are however used just in an explanatory way to provide insight into potential relationships of the missing value structure.    Creating a TREE plot   The tree plot in the next graph displays the result of the variable clustering. Two main clusters with three variables each are produced. The DELINQ, NINQ, and DEROG variables form one cluster, indicating that missing values for these variables occur frequently together.      A preliminary indication of this fact has already been seen from the distribution of the %MV_PROFILING macro. The other cluster comprises the variables CLAGE, CLNO, and JOB. From a business point of view, this analysis is helpful because it identifies which missing value patterns occur together. Reasons for this pattern may be found in the data collection process or in the data availability in general, as well as in the business background of the data. Tree plot for missing values based on variable clusters The component pattern plot   The component pattern plot shows a similar picture, where the closeness of the variables is derived from the first and second principal component of the missing value variable indicators.     Principal component plot based on missing value indicators Note that the proportion of the Eigenvalues for each component is shown in brackets on the respective axis.   Usage examples   Consider the following usage examples for the %MV_PROFILING macro:   Create the same output as shown in the example outputs above: %MV_Profiling (data=hmeq, vars = bad delinq derog ninq clage clno job); Profile all variables from data set HMEQ: %MV_Profiling (data=hmeq, vars = _all_) Profile the variables JOB, REASON, DEBTINC, and VALUE from data set HMEQ: %MV_Profiling (data=hmeq, vars = job reason debtinc value); Sample the data for better performance to 10%: %MV_Profiling (data=hmeq, vars = _all_, sample=0.1); Using only variables starting with “D,” turn off creating the tile chart, the variable clustering, and the principal component analysis: %MV_Profiling (data=hmeq,vars = d:,ods = NO, princomp=NO);   Usage information for the %MV_PROFILING macro   In order to profile the structure of missing values and to create these results, the SAS macro %MV_PROFILING has been programmed. The macro requires Base SAS, SAS/STAT, and SAS/GRAPH software. The full code for this macro is documented in appendix A. Note the following: In order to produce the principal component chart, ODS HTML has to be activated for the macro. You can do this either by submitting the statement ODS HTML; before the macro and the statement ODS HTML CLOSE; after the macro running the macro within SAS Enterprise Guide or SAS Studio In order to produce the tile chart, the graphical device has to be set to ACTIVEX or JAVA in addition to ODS HTML. This can be done either by submitting GOPTIONS DEVICE = JAVA; or GOPTIONS DEVICE = ACTIVEX; in the SAS code or by specifying ActiveX or JAVA as graphic format in the SAS Enterprise Guide options. The list of variables can also be quoted by a character followed by a colon “:”. This allows you to specify all variables that start with the same character(s). If no variables match the quoted string, the macro stops executing. The following parameters can be specified with the %MV_PROFILING macro:   DATA Name of the input data set.   VARS List of variables in the data set to be analyzed. Variable names are separated by a blank. If _ALL_ is specified, all variables in the data set are used. The default value is _ALL_.  A wildcard notation with a colon “:” can also be used (for example, DEMO: causes all variables starting with “DEMO” to be used).   ORDER Only relevant when VARS=_ALL_ is specified. Possible values are POS or ALPHA. The default value is ALPHA. ALPHA means that the variables of the data set are used in alphabetical order for the %MV_VALUE_CHAIN macro. POS means that the variables are used in the same order as in the data set for the %MV_VALUE_CHAIN macro.   ODS Specifying ODS = YES creates the tile chart, the VARCLUS tree plot, and the principal component analysis. Default = YES.   VARCLUS Specifying VARCLUS = YES creates the VARCLUS tree plot. Default = YES.   PRINCOMP Specifying PRINCOMP=YES creates the principal component analysis. Default = YES.   NCOMP Defines the number of principal components that are used for the plots of components. NCOMP = 2 creates one plot for the first and second components. NCOMP = 3 creates three plots: one for the first and second components, one for the first and third components, and one for the second and third components, and so forth.   SAMPLE Specifies a sample proportion to allow you to run missing value profiling on a sample rather than on the entire data set. Default = 1 (100%), no sampling. Values from 0 to 1 are valid.   SEED Seed value that is used for sampling. Default = 18419.     Links   Related Articles Using the TIMESERIES procedure to check the continuity of your timeseries data Feedback on the macro is welcome and can be sent to mailto:sastools.by.gerhard@gmx.net. This macro has been taken from my SAS Press Book "Data Quality for Analytics Using SAS".   ... View more

In this article, I give you an example of how to improve interpretability of a machine learning model by explaining the coefficients of categorical variables in regression models. The article uses the macro %CALC_REFERENCE_CATEGORY, that can be download from my github page. More details on the macro can be found in a future SAS Communities article, "The %CALC_REFERENCE_CATEGORY macro." In my Webinar Series "Home alone? Invest now in your data science skills, and help solve the problems of the moment and the future" (LinkedIn + Medium) you find a recording of the presentation on this topic.   Business background The interpretation of analytics results is a key topic for many data scientists. If you can better interpret and communicate the outcome of your models, you will receive a higher acceptance of your results and your work. A good interpretation of the models also provides better understanding for the business user and consequently a better usage of the analytics results in the business process. An illustrative interpretation and explanation of the analytical models also decreases the risk of last minute misunderstandings when the model is moved into production.   A case study: Survival analysis performed for employee headcount data The application of the macro is illustrated with a business case study for the human resources sector. It shows how survival analysis can be used to analyse employee headcount data. In the following graph, the career for each employee is illustrated by a horizontal line. You see that some careers are quite short, others are longer and some of them extend until the end of the data collection in December 2016.      You see that we are dealing with right-censored data here. To analyze such data, you can use SAS/STAT procedures like the LIFETEST, the LIFEREG or the PHREG procedure.  This case study is taken from my SAS Press book, "Applying Data Science - Business Case Studies Using SAS." In chapters 1-4, I explain the case study "Employee Headcount Analysis." In the attachment to this article, you'll find a SAS file "CREATE_DATA_employees.sas" that creates the data from a datastep.         This example analyzes the effect of the factors DEPARTMENT, GENDER, TECHNICALKNOWHOW on the expected survival and the probability to resign within the next 6 months.     Analysis with the PHREG procedures To perform such an analysis, you use the PHREG procedure with the following syntax. proc phreg data=employees; CLASS department gender TechKnowHow / PARAM=reference REF=first; MODEL Duration*Status(1)= department gender TechKnowHow / SELECTION=stepwise; run; You see that a CLASS statement is used to list the categorical variables. The PARAM= option defines that the REFERENCE code shall be used here. Note that the REF option is set to FIRST here. This specifies that the first category of each CLASS variable is used as reference category. The output of the PHREG procedures shows how the categorical variables are encoded by design variables. For variable DEPARTMENT, you see that the ADMINISTRATION department has been used as the reference category with a 0-value for all variables.   The interpretation of the regression coefficients for variable DEPARTMENT can now be interpreted as the difference of each department compared to the ADMINISTRATION department with value 0.  You see that only the MARKETING department has a lower risk the employees resign. All other departments have a higher risk for an employee resignment.    Defining a reference category is not always possible from a business point of view! From a business point of view however, it is not always possible or straightforward to define a reference category to serves as the comparison basis for the other categories. And the arbitrary selection of a category is often confusing for the business user in the interpretation of the model.   We see examples where it's difficult to select a reference category in multiple business domains: If you analyse blood donation, it is hard to decide which blood group shall serve as the reference group. In credit scoring, it is often not obvious which loan type shall serve as the reference. In analytical CRM, it is hard to define which geographic region shall be the base region that is compared to the other regions.  The EFFECT coding for categorical variables can help here! The EFFECT coding also generates design variables. However, as opposed to the REFERENCE coding, the reference category gets assigned value of -1.  The code to run such an analysis is similar to the code above. Only the value for PARAM= is changed to EFFECT.   proc phreg data=employees; CLASS department gender TechKnowHow / PARAM=effect REF=first; MODEL Duration*Status(1)= department gender TechKnowHow / SELECTION=stepwise; run; Here are the parameter estimates generated by this analysis:   You see from the parameter estimates that the coefficient for MARKETING is still the lowest value. However the values are different from the results from above when the REFERENCE coding has been used.   Note that the EFFECT coding is available for the following: SAS/STAT procedures: CATMOD, GENMOD, GLMSELECT, LOGISTIC, PHREG, and SURVEYPHREG SAS Viya Visual Statistics Procedures: GAMMOD, GAMSELECT, GENSELECT, LMIXED, LOGSELECT, MODELMATRIX, PHSELECT, PLSMOD, QTRSELECT, REGSELECT, SANDWICH, and TREESPLIT Graphical comparison The following graph compares the parameter estimates from the two PHREG procedure calls graphically. The coefficients for the REFERENCE coding are shown at the right and the coefficients for EFFECT coding at the left.    Find the code for this graph in the Appendix to this plot.   You see that the distance between the individual categories is the same in the two encoding options. You also see that the values are just shifted by a constant factor. For the REFERENCE coding, the values are located with ADMINISTRATION = 0. The values for the EFFECT coding are not bound to any fixed zero value. From the graph you see that the (hidden) regression coefficient for ADMINISTRATION is somewhere around -0.6 or -0.7. You see that the EFFECT coding provides regression coefficients for each category without having to artificially assign one category the zero value. How can you calculate the (hidden) value of the reference category in effect coding? Of course you do not always want to look this regression coefficient up from a diagram. You want to calculate it. And this is very easy! Just: Sum up the regression coefficients for the respective variable from the output of the PHREG procedure. Change the sign of this sum. In our example from above this results in the following calculation:  Sum it up: (-1.155)+0.823+0.630+0.356 = 0.654 Change the sign: -0.654 If you compare this value with the output in the graph, you see that this is a reasonable value for ADMINISTRATION. Warning and usage note: Note that this calculation shall only be applied for interpretation purposes to understand the magnitude of the coefficient for the reference category. It must not be additionally included in the calculation of the predictor if new observations shall be scored. The design variables and the parameter estimates of the regression procedure output are already scaled that way that the correct prediction is made for the reference category, even if no value is shown in the parameter estimate output. The %CALC_REFERENCE_MACRO does the work for you! In my SAS Press book "Applying Data Science - Business Case Studies Using SAS" a SAS macro is introduced that automatically performs this calculation for you. You can download this macro at my github page. The macro is tested for the SAS/STAT procedures:  GLMSELECT, PHREG, LOGISITC and the SAS Viya Visual Statistics procedures: LOGSELECT, GENSELECT, PHSELECT.  For the employees data example with the PHREG procedure, the macros generates the following output: Observations 5 and 7 have been inserted by the %CALC_REFERENCE_CATEGORY macro. Feedback from the business user I have used the EFFECT coding in many analyses across industries and business domains where I've built regression models. Business departments usually do not appreciate the fact that they have to select a specific category as a reference. And it is often counter intuitive for them why a certain category should receive the value 0 and act as the comparison level for the other categories.   In days where the interpretation of the analytical model and the involvement of the business user play and increasing role, I often use this method to better communicate my findings and my machine learning models. Appendix - Code to create the comparative graph This code shows how the graph comparing the two encodings has been created.   1. Prepare the coefficients from REFERENCE coding Add a record for the reference category at the begin of the datastep. You use the OUTPUT statement here and only export this once (before the first record, _N_=1). data RefParmEstimates_XT(Keep=parameter classval0 estimate ModelID); if _N_ = 1 then do; Parameter = "Department"; ClassVal0 = "ADMINISTRATION"; Estimate = 0; ModelID=2; output; end; set RefParmEstimates; ModelID = 2; if parameter = "Department"; output; run; Assign MODELID = 2 for the REFERENCE encoding. Restrict the output only to coefficients for parameter DEPARTMENT.   2. Prepare the coefficients from EFFECT coding Use the %CALC_REFERENCE_CATEGORY macro to add and calculate a record for the ADMINISTRATION department in EFFECT coding. %Calc_Reference_Category(ParmEst=EmpParmEstimates, ClassLevels=EmpClassLevels,PROC=PHREG); Run a datastep to prepare the data in the same structure as in REFERENCE coding. data EffectParmEstimates; set _parmest_xt_; keep estimate parameter ClassVal0 ModelID; ClassVal0 = scan(Parameter,2); Parameter = scan(Parameter,1); ModelID=1; if effect = "Department"; run; You assign MODELID = 1 for the EFFECT encoding. You restrict the output only to coefficients for parameter DEPARTMENT.   3. Append the two datasets You append these two datasets with a SAS datastep. data ParmEstimates; set RefParmEstimates_XT EffectParmEstimates; run; 4. Create the Plot with SGPLOT procedure First a FORMAT is created for the two encodings. proc format; value mid 1="EffectCoding" 2="ReferenceCoding"; run; Use the SGPLOT procedure to create the plot. proc sgplot data=ParmEstimates; format ModelID Mid.; scatter x=ModelID y=Estimate / datalabel=ClassVal0 markerattrs=(symbol = circlefilled); xaxis values=(0 to 3 by 1) label="Parameterization"; yaxis min=-2 max=2; refline 0 / axis=y; run; Note that variable CLASSVAL0 contains the category name and is used as a DATALABEL.   Use the MID format that displays the value on the x-axis.   Use the XAXIS and YAXIS statement to format the axis with the MININUM and MAXIMUM value or by providing a range of values. Note the BY 1 in  values=(0 to 3 by 1)  is important to make sure that only integer steps are shown at the x-axis.   For better visibility it is advisable to add a reference line at the y-axis a value 0. Use the REFLINE statement for that.   Other articles in this series Encoding of CLASS Variables in Regression Analysis - Better understand the ORDINAL encoding           ... View more

This article explains how you can automatically insert and label reference lines into your line-chart that indicate events in your data. The focus is on the automation of this task. You can always add reference lines using the REFLINE statement in the SGPLOT procedure. The method that is described here, uses output values from SAS analytics procedures (e.g. PROC ADAPTIVEREG or PROC X13) to automatically position and label the reference lines, instead of having to rewrite the REFLINE code every time your re-run the analysis.   The article uses two examples. In the first examples it is show how you can automatically position the reference lines.It explains how you can use the output dataset shown on the left to add reference lines as shown in the graph on the right.                                The second example extends the task from example 1 by also automatically adding a label to the reference line based on the analytic output.                             This articles shows you can use a SAS DATA step with random number generators and a SAS Informat to simulate monthly time series data with specific patterns like trends, seasonal variation, breakpoints and outliers. It outlines options to analyze the course of the time series with analytical methods to identify breakpoints and outliers.   This tip is taken from the book Applying Data Science - Business Case Studies Using SAS. In case study 2 I show methods to detect structural changes like breakpoints and outliers in your data.  You can find more details on the detection of structural changes also in my Youtube Video Detecting Structural Changes and Outliers in Longitudinal Data. There is a related article at SAS Communities on how to Simulate timeseries data with a SAS DATA Step and SAS Functions.        Overview over the necessary steps In order to automatically display events as vertical reference lines in the line chart, the following steps are needed: Use ODS OUTPUT to save the points on the x-axis calculated by a SAS procedures in a SAS data set. Use a SAS DATASTEP to convert the dataset into SAS statements that print the vertical reference lines and optionally also label them. Save these program statements in a SAS program file. Use the SGPLOT procedure to create your line chart. Include this SAS program file in the SGPLOT procedures that contains the the REFLINE statements.   Example 1 - Create reference line for breakpoints calculated by the ADAPTIVEREG procedure   Create the Output Data Sets using ODS OUTPUT In the ADAPTIVEREG procedure, you use the ODS OUTPUT statement to save the knot points that shall be shown at the x-axis   proc adaptivereg data= AirlinePassengersSmooth plots=all ;  model pass_smooth_backwTr = date/ maxbasis=11;  ods output BWDParams=KnotPoints;  output out=flights_adpt predicted=pred_adpt; run; The content and structure of the this table looks as follows:                             Use a SAS DATASTEP to create the REFLINE statements This data set can now be used to generate SAS code that displays the vertical reference lines. The following statements use the values in the KNOTS variable of the KNOTPOINTS data set and combine them with syntax statements.   filename reflines 'c:/tmp/reflines_brk.sas'; data _NULL_;  set KnotPoints;  where name not in ("Basis0", "Basis1");  file reflines;  put @04 "refline " knot " / axis = x;"; run;   The FILENAME statement creates a file reference to a file in your directory. You can modify this path and filename. Note that you need write permissions in the directory that you specify. A _NULL_ DATA step is used as no output data set shall be created but the code is written to the above specified file. Data set KNOTPOINTS is used as input data set. Only the records starting with the 3rd line are used. The FILE statement exports the result of the PUT statement in the next line to the file specified above. In the PUT statement, you see a combination of text, for example, “REFLINE” and the value of the knot variable in the KNOTPOINTS data set. The content of the file REFLINE_brk.SAS in the above example is the following:      refline 14792  / axis = x;    refline 15553  / axis = x;    refline 15096  / axis = x;    refline 11596  / axis = x;    refline 15706  / axis = x;   You see that SAS statements that can directly be used in the SGPLOT procedures were generated automatically.   Plotting the lines with the SGPLOT procedure Finally, you use the SGPLOT procedures to create the line chart with the reference lines.   proc sgplot data=flights_adpt;  series x=date y=passengers;  series x=date y=pass_smooth_backwTr/lineattrs=(pattern=4);  series x=date y=pred_adpt/lineattrs=(pattern=3);  %include reflines; run;   The SERIES statement is used to plot a separate line for the actual values, the smoothed values, and the predictive values from the multivariate adaptive splines regression. The PATTERN option in the LINEATTRS option is used to create dashed and dotted line types. The statements for the reference lines are included with the %INCLUDE statement. The output of this procedures looks as follows:                           Example 2 - Create reference line for outliers detected with the X13 procedure   Overview In order to illustrate the location of the automatically identified outliers, you might want to add reference lines to the line plot. In order to do this, you follow the same procedure automatically detected breakpoints above. This example extends the approach form above by also adding an automatic label to the reference line. Save the list of detected events in a SAS data set Use this data set to create SAS statements that print and label the vertical reference lines. Save these program statements in a SAS program file Use the SGPLOT procedures to draw the actual values. Include the SAS program file from step 2 in the SGPLOT procedure to create the reference lines with a REFLINE statement.   Save the list of detected events in a SAS data set In the X13 procedure, you use the ODS OUTPUT statement to store the list of detected outliers in a SAS data set.   proc x13 data=flights_911 date=date;    var passengers;    arima model=( (0,1,1)(0,1,1) );    outlier;    ods output RegParameterEstimates=RegParameterEstimates; run ; This outputs the following information in a SAS datset:                   Use a SAS DATASTEP to create the REFLINE statements   Next you use a DATA _NULL_ step to write the REFLINE statements to a SAS file.   filename reflines 'c:/tmp/reflines_outliers.sas'; data _NULL_;  set RegParameterEstimates;  file reflines;   Date=cats("'01",compress(substr(regvar,3,length(regvar))),"'d");   put @04 "refline " Date " / axis = x label = '"regvar "';"; run; The FILENAME statement creates a file reference to a file in your directory. You can modify this path and filename. Note that you need write permissions in the directory that you specify. A _NULL_ DATA step is used as no output data set shall be created but the code is written to the above specified file. Data set RegParameterEstimates is used as input data set.  The FILE statement exports the result of the PUT statement in the next line to the file specified above. In the PUT statement, you see a combination of text, for example, “REFLINE” and the values from the dataset (note that in the dataset the values shown as PARAMETER in the output text are stored in variable REGVAR). Note that SAS text functions are used to isolate and concatenate the date value "01SEP2001"d from the text string "LS SEP2001" in the output data set. The value of REGVAR is also used as label in the LABEL= option. The content of the file REFLINES_OUTLIERS.SAS in the above example is the following: refline '01SEP2001'd / axis = x label = 'LS SEP2001 '; refline '01NOV2001'd / axis = x label = 'LS NOV2001 '; refline '01DEC2002'd / axis = x label = 'AO DEC2002 '; refline '01DEC2003'd / axis = x label = 'LS DEC2003 '; You can now use this file directly and include it in your PROC SGPLOT code.   proc sgplot data=flights_911;    series x=date y=passengers;    yaxis label='Passengers';    xaxis label="Date";    %include reflines; run;                                       Creating Individual Labels Alternatively, you might want to add specific labels to each vertical reference line. In that case, you copy and paste the SAS statements from the REFLINES.SAS file to your SGPLOT procedure statements. Then you customize the labels in the LABEL option of the REFLINE statement. The following example code creates the output below   proc sgplot data=flights_911;    series x=date y=passengers;    yaxis label='Passengers';    xaxis label="Date";    refline '01SEP2001'd  / axis = x label="Level Shift at 9/11";    refline '01NOV2001'd  / axis = x label="Level Shift +";    refline '01DEC2002'd  / axis = x label="Outlier +";    refline '01DEC2003'd  / axis = x Label="Level Shift -"; run;                             Can I use this method also without analytical procedures?   Yes, you can define simple business rules. For example: Flag all months where Easter-Sunday does not fall into April And indicate whether there is an increase or decline from March to April Pass_Change_PrevMonth = dif(passengers); EasterMonth = (month(date)=month(holiday('Easter',year(date))); SAS Communities Article: 3 ways to consider movable holidays in SAS   filename refl_dif 'c:/tmp/sasensei/reflines_dif.sas'; data flights_911_flag; set flights_911; Pass_Change_PrevMonth = dif(passengers); EasterMonth = (month(date) = month(holiday('Easter',year(date)))); run; ** Be careful when combining DIF/LAG with IF statements in a datastep; data _null_; set work.flights_911_flag; format Date 8.; file refl_dif; if month(date)=4 and EasterMonth=0 then do; if Pass_Change_PrevMonth<0 then put @04 "refline " Date " / axis = x label = 'March-Easter April Decrease';"; else put @04 "refline " date " / axis = x label = 'March-Easter April Increase';"; end; run; proc sgplot data=flights_911; series x=date y=passengers; yaxis label='Passengers'; xaxis label="Date"; %include refl_dif; run;                           Links See also the article Simulate timeseries data with a SAS DATA Step and SAS Functions and the Youtube Videos:  Detecting Structural Changes and Outliers in Longitudinal Data SASENSEI SID#6 Session on this topic         ... View more