HIMYM …  New York? Legendary? And also: SAS Viya and SAS Visual Analytics in Action!   "How I made your model with SAS Viya". This is my new webinar series featuring model building in SAS Viya.   Here you will learn how easy it is to build predictive models, segmentations, and cluster analyses using No-Code/Low-Code environments and SAS coding.   The series not only focuses on the building of supervised und unsupervised machine learning models but also on the profiling of customer data, the handling of data quality aspects like missing values in the analysis data, the interpretation of complex machine learning models, the profiling of segments and clusters as well as the export of the results for further usage in operation.   "Season 1: The NoCode/LowCode Experience" has just been released and is available now in my Youtube Channel. 21 episodes are waiting for you to show that insights into your data, your first predictive model, segmentation or cluster analyses are just a few mouse clicks away.     The target audience of this webinar are Business users who would like to see how they can touch data and bring in their business expertise into the analysis process, SAS Data Scientists who would like to demonstrate to their peers how easy it is to build models in SAS Viya My students at University of Steyr, University of Burgenland and University of Krems   Future seasons like the “Season 2 - The SAS Programming Experience” and “Season 3 - The ML-Pipeline Experience” are currently in planning stage.     Season 1 - The NoCode/LowCode experience   In this season you will see how you can use SAS Visual Analytics to build analytical models and to perform descriptive analyses. You will learn how you can use  graphical, statistical and machine learning objects that automatically create the analysis for you. You see how you can parameterize the objects to receive correct results that can be presented to your business audience.   Check here for the full list of videos in Season 1.                   Introduction and Getting Started   S01E00a  - Welcome to the series, Introduction of the narrator S01E00b  - Overview and examples of season 1: “The NoCode/LowCode Experience” S01E00c  - Get your analysis data into SAS Visual Analytics S01E00d - Tips and Tricks when navigating through SAS Visual Analytics   Simple Statistical Analysis of your Data   S01E01 - Take a look at your data table S01E02 - Profile your data with interactive graphs S01E03 - Perform simple statistical analyses   Segmentations, Clusterings, Automated Explanations   S01E16- Segment your Customers - Basic Examples S01E14 - Automatically cluster your customers S01E15 - Profile the properties of your segments and clusters S01E04 - Automatically explain relationships in your data   Build Supervised Machine Learning Models   S01E05a - Convert your findings into a decision tree S01E05b - Change the Leaf Size Parameter in the Decision Tree S01E06 - Create a PARTITION Variable S01E07 - Create a Decision Tree S01E07b - Display Assessment Statistics for Test Data S01E09 - Perform Logistic Regression analysis S01E10- Create a Random Forest and Gradient Boosting   Assess, Explain and Use your Machine Learning Models   S01E11 - Compare the accuracy of your models S01E17 – Explain complex machine learning models using a surrogate model S01E12- Add the model predictions to your data table     Other Links   You also find content in my webinars at Youtube: DataPreparation4DataScience  Data Science Use Cases   other articles at SAS Communities Data Science and Data Preparation Article Overview by Gerhard           and in my books in SAS Press   Data Quality for Analytics Using SAS Applying Data Science - Business Case Studies Using SAS Data Preparation for Analytics Using SAS     ... View more

This article shows how you can use the INTNX function to easily determine the date of the 3rd weekend of each calendar month.   The business questions   SAS Skillbuilder and SAS Viya for Learners provide a SAS great offering for educators and students with free-of-charge SAS Viya software access and SAS e-learnings.   I have been teaching data science and SAS programming classes at different universities and business schools in Austria for many years. And I am a happy user of the above offering in my lectures for my students.   Every software system needs maintenance and the SAS learning environment undergoes maintenance once a month. The announcement of the maintenance on the website reads as follows:   SAS Viya for Learners may be unavailable every 3rd weekend of the month from Friday at 8pm ET to Sunday at 2pm ET while we apply enhancements.   (Comment: In case that the first of the month is a Sunday, there will be a weekend that contains the 3rd Saturday of a month (21st of the month) and the 4th Sunday of the month (22nd of the month), while the 3rd Sunday was already the weekend before (15th of the month). The response of SAS tech support is that the 3rd weekend in their schedule is always the weekend in which the 3rd Saturday of the month falls.)   In order to plan my lectures schedule months ahead with the head of studies, I want to be able to generate a list of blocked weekends automatically.   The solution   The following SAS code allows go generate a list of these weekends. You simply need to adopt the year values in the DO year = 2024 TO 2025 statement.     data Weekend_3rd; format third_Saturday next_Sunday date9.; do year = 2024 to 2025; do month = 1 to 12; third_Saturday = nwkdom(3,7,month,year); next_Sunday = third_Saturday + 1; output; end; end; drop month year; run; proc print; run;   The DO loop iterates over the selected years and the 12 calendar months. The 3rd Saturday is determined using the NWKDOM function. the first parameter specifies the n-th repetition of the required weekday the second parameter specifies the day of the week (1 = Sunday, ... , 7 = Saturday)  MONTH and YEAR specify the Year.Month   The resulting output lists the maintenance weekends per month. I use this list to plan my lecture schedule.     Alternate solution using the INTNX function   You can also use the INTNX function for this task.      YearMonth = mdy(month,1,year); third_Saturday = intnx('week.7',YearMonth-1,3); next_Sunday = third_Saturday + 1;     A date variable with the first day of each month is generated using the MDY function. The 3rd Saturday is determined using the INTNX function. The date (first of the month) is moved to the last day of the previous month using the YearMonth -1 calculation. WEEK.7 tells the function to consider weeks to start with the 7th day of the week (= Saturday) the value "3" instructs the INTNX function to increase the date value to the start of the 3rd "week". (as we defined week.7 to the 3rd Saturday of the month).    If your data contains the date value already in a DATE variable (and not in separate YEAR and MONTH variables) the INTNX function allows you to use this variable directly. For the NWKDOM function you need to calculate the YEAR and MONTH value using the YEAR() and MONTH() functions.   The INTNX function is a great tool to calculate specific date values in a very flexible way. See also my article 3 ways to consider movable holidays in SAS .   Credits Many thanks to @BrunoMueller and @Tom for reminding me that the NWKDOM() function is a much more direct solution for this problem than the INTNX function. Using the INTCK and INTNX functions heavily for my many tasks in my time series data preparation projects over the last years, the NWKDOM function disappeared somewhat from my radar 😉 .   References 3 ways to consider movable holidays in SAS SAS Viya for Learners: https://www.sas.com/en_us/software/viya-for-learners.html SAS Skill Builder https://www.sas.com/sas/training/academic/student-skill-builder-emea.html            ... View more

                        Watch this Ask the Expert session to learn importance of data preparation prior to forecasting.     Watch the webinar     You will learn: How to perform feature engineering for time series forecasting. SAS programming techniques for time series forecasting-related data preparation. The difference in data preparation when applying time series versus machine learning methods. How to automatically perform data manipulation tasks in SAS Visual Forecasting UI.   A related ask-the-expert session on the topic "An A to Z Overview of Forecasting in SAS®" by the same authors is available here.   The questions from the Q&A segment held at the end of the webinar are listed below and the slides from the webinar are attached.   Q&A Currently our team utilizes SAS FS and EG - would I be able to: input a . in place of 0 - for 24 months of historical time stamped data? If I understand this question correctly, the answer is yes. You could either do this, for example, by using proc timeseries or coding approach, or there are also options in forecast server itself which allows you to do so programmatically or in the UI where you can handle aggregations and define what should happen with missing values.   Additionally, is it possible to run a program in SAS EG adding the promotional variable to previous time stamped data and scheduling a job to import the change in FS? The answer is yes this is possible. It would be a 2-step approach. First step is data management, where you have a job which adds this promotional variable to your input data. This would be the first thing and then immediately after this you have to update your forecast in your project. I understand from your question that you're asking for forecast server. Forecast server offers you the forecast server batch macros. For example, the “update project” macro will allow you to do so.   Is there a way to do Time Series forecasting in SAS Forecast Studio? The answer is yes, because our Forecast Studio is the front end for time series forecasting in SAS 9. I just want to add to this that the forecast studio is based on the previous developments of SAS platform, while today we showcased SAS Viya and SAS Visual Forecasting. Of course, this doesn't mean that you can't do some things that we showcased today in SAS 9 as well, but you'll see a lot of limitation for machine learning, deep learning, and more advanced techniques, as well as things like the segmentation that we showed which is not available in SAS 9 and Forecast Studio.   Is 'SAS/ETS' or a SAS Econometrics license a plug-in or part of a program like SAS EG? Enterprise guide itself is just a user front end, which helps you to access the SAS server. If you have Enterprise Guide, you don't have automatically all the modules available which SAS offers. You have to check your SAS license whether it includes SAS ETS or SAS Econometrics or SAS Visual Forecasting. This is a prerequisite for example to have e.g., PROC EXPAND. You can use “PROC SETINIT” in SAS EG or SAS Studio to see the programs that you’ve licensed.   Recommended Resources Replace Missing Values in Time Series Data Using PROC EXPAND and PROC TIMESERIES Using the TIMESERIES procedure to check the continuity of your timeseries data Have a look at your TIMESERIES data from a bird's-eye view - Profile their missing value structure 3 ways to consider movable holidays in SAS 5 things you should know about SAS Visual Forecasting Using SAS Viya for Time Series Data Handling Forecasting with SAS free ebook Moving from SAS®9 to SAS® Viya® Please see additional resources in the attached slide deck.   Want more tips? Be sure to subscribe to the Ask the Expert board to receive follow up Q&A, slides and recordings from other SAS Ask the Expert webinars.   ... View more

  A topic "Close to my heart"     Back in 1997 when I was a researcher at the Medical University of Vienna, I wrote the first version of the %MAKELONG and %MAKEWIDE macro to transpose data from clinical trials in the appropriate data structure. At that time, I was not yet aware that I started my journey through the world of "data preparation for data science" (or "data preparation for analytics" how it was called at that time). At the beginning of  2007 my first SAS Press Book "Data Preparation for Analytics" was published, followed by "Data Quality for Analytics" in 2012.         Today, 25 years later my #datapreparation4datascience collection contains 18 SASCommunities articles, 12 articles at medium.com, LinkedIn and other media, 14 webinars on Youtube, 3 SAS Press Books, 4 SAS Global Forum papers, 2 ask-the-expert sessions, 2 SAS tips at support.sas.com, 3 SAS Blogs and more than 60 presentations on data science conferences.   The purpose of this article it to provide an overview over #datapreparation4datascience and a link collection for the contributions by topic. However before I start with the overview and the list, I want say that this has not only been my own achievement. I have to thank you, the SAS User Community, for your support!     The topic of my customers     I could only develop these topic and write the SAS Press books because I was in conversation with our SAS Customers and SAS Users. The questions you asked me and the challenges you gave me, were often a trigger for new ideas, tips and tricks and solutions. Here are a few examples: In long nights of detecting and fixing data quality problems we found out and discussed that the statement "the data quality is in good shape" needs to be differentiated between technical data quality and data quality for analytics. The fact, that data is fine from an IT perspective because the values are between the lower and upper boundary or the categories are in a predefined list, does not mean that data can be used for machine learning models. In many machine learning projects we worked on how to improve the predictive power of the models. And some of the challenges you presented to me resulted in creative solutions for feature engineering and deriving customer behavior from transactional data. The above mentioned %MAKEWIDE and %MAKELONG macro for transposing the data have been reworked and augmented over the years. They were a frequent, but not the only, challenge how to structure data for being used in analytical models, SAS procedures and SAS Enterprise Miner or SAS Model Studio. Sometimes we had (quite philosophical) discussions about the origin of data and the fact that the final representation of the data not only depends on the values of the analysis subject themselves but also from the intention of the data retrieval system and the process that collects the data. The "story of my old aunt Susanne" was one of the results of these discussion and has been presented at SAS conferences and my academic teaching now for more than 10 years. The discussion "what would have happened, if we had more/better data (or also less/inferior data)" in time series forecasting, was the trigger for many simulation studies, that have been performed to quantify the effect of more missing values or a longer time series.     #datapreparation4datascience - Digging one level deeper     Data Science methods have specific requirements on analysis data. Occurrences of missing values have to be investigated and handled, the distribution of the values need to be checked, and a sufficient amount of analysis objects is needed. Data Science methods also require certain data structures like the one-row-per subject structure or the timeseries dataset. In return these methods however also enhance the data preparation work and can also be used to verify and improve data quality and allow you to perform powerful feature engineering.   "Data Preparation" and "Data Quality" are therefore much more than just joining tables and checking the possible list of values. Data Preparation for Data Science is a discipline to create and quality check the analytic base tables and to derive the relevant features that shall be used in the analysis. In my publications I categorize my articles into the following three main topics:         1. Feature Engineering     Feature Engineering is an important tool for (supervised) machine learning. Model accuracy and interpretability benefits from variables that precisely describe the behavior of the analysis subjects. In many cases these features are derived from transactional data which are recorded, e.g. over time. In some cases, simple descriptive measures like the mean or the sum provide a very good picture of the analysis subjects.   Often it is important to dig deeper to adequately describe the behavior. Analytic methods help to calculate KPIs that measure the trend over time per customer, the accordance with predefined pattern or the correlation of individual customer with the average customer. For powerful analytical models it is however not enough to "replicate" the original data just in another format, e.g. by transposing. You want to describe the behavior of your analysis subjects. You can to this by calculating different analytic measures.     Feature Engineering 1 - Using Correlation Analysis to Describe Behavior over Time | +Youtube Webinar Feature Engineering #2 - Accordance with predefined pattern | +Youtube Webinar Feature Engineering #3 – Describing the Trend over Time SAS Sample 31582: Creating measures for the course over time 3 ways to consider movable holidays in SAS Forecasting challenges the Easter Bunny brings (LinkedIn) SUGI2006: Efficient “One-Row-per-Subject” Data Mart Construction for Data Mining                 2. Data Quality for Analytics     Analytical methods have specific requirements on the analysis data. These data quality requirements often go beyond classic requirements of basic reporting and descriptive statistics. Missing values for example can quickly reduce the available set of records with complete data.   The data quantity aspect has influence whether statistically significant results can be generated or if certain machine learnings methods can be applied at all. The availability of data in general for the analysis decide whether you are able to perform your analysis or whether you have to reformulate your business questions or postpone the analysis at all.   Analytical methods however not only have requirements on the data. These methods can also be used to profile and improve the quality of the data.       2.1 Conceptual considerations   These articles discuss the conceptual background and the motivation of "Data Quality for Analytics".   Is your data ready for data science? — Motivating this topic from a sail-race-analysis example medium.com + linkedin + IT-Briefcase You just found out that the data quality of your analysis data is poor — What are your options? (medium.com) The origin, detection, treatment and consequences of missing values in analytics (article at informs.com) “Rosetta Stone” — The most important text sample in history and the role of labeled data in machine learning (medium.com) Explaining Sign Inversion of Parameter Estimates in Multiple Regression Models — A Story-Telling Approach  (medium.com) "The hungry statistician"​ – or why we never can get enough data (linkedin)               2.2 Profiling and improving data quality   Data Science methods not only demand a certain data quality level, they also allow you to profile and improve the quality of your analysis data.   General Data Quality Profiling   Sailing and the art of data quality assessmen (JMP-Blog by Bernd Heinen) Youtube: Verifying Data Quality Using Interactive Data Analysis of GPS-Data of a Sail Race SGF2015: Early Picture of the Data Quality Status of Your Analysis Data? SAS® Visual Analytics Shows You How    Cross Sectional Data   Missing Values in Machine Learning: Why my old aunt Susanne gives data scientists a hard time (Youtube) The structure of MISSING VALUES in your data - get a clearer picture with the %MV_PROFILING macro   Time Series Data   Using the TIMESERIES procedure to check the continuity of your timeseries data Replace MISSING VALUES in TIMESERIES DATA using PROC EXPAND and PROC TIMESERIES Youtube: Detecting and Treating Missing Values in Longitudinal data Have a look at your TIMESERIES data from a bird's-eye view - Profile their missing value structure  + Youtube Webinar Youtube: Detecting Structural Changes and Outliers in Longitudinal Data   Using Individual Reference Limits for Data Quality Checks   Youtube: Providing a reference value that considers all available co-information  Youtube: Self Service Analytics using SAS Viya - Calculating Individual Reference Limits Using the GLMSELECT procedure to calculate individual reference values in outlier detection     2.3 Simulation case studies   Simulation case studies have been performed to quantify the effect of bad data quality on the performance of supervised machine learning models and on time series forecasting models.            The effect of (bad) Data Quality on Model Accuracy in Supervised Machine Learning — Results from Simulation Studies  Medium + Youtube Webinar Quantifying the Effect of Missing Values on Model Accuracy in Supervised Machine Learning Models  Medium  + Youtube Webinar Determining the best length of the history of your timeseries data for timeseries forecasting  SAS Communities + Medium + Youtube Webinar Using SAS Enterprise Miner for Predictive Modeling Simulation Studies   3. Data Assembly      In order to be able to analyze the data with data science methods, the data needs to be structured in a appropriate form. The one-row-per-subject data structure and the longitudinal data structure are the two most prominent data structures in data science and machine learning. Data Assembly here refers to the exercise of joining tables together, aggregating data across hierarchies like time or product group, transposing data from one structure into another.    However this is not only a technical task of "programming the data management exercise". It also involves business considerations like the selection of the right data sources to answer the business question, the alignment of data at the time axis for predictive modeling, and the discussion which input variables can be used for the analysis.           Youtube: Conceptual Considerations when Preparing Data  Transpose your analysis data with the %MAKELONG and %MAKEWIDE macro SGF2018: An Easier and Faster Way to Untranspose a Wide File (co-authorship with A. Tabachneck, M. Kastin, J. Matise) Youtube: SAS Tips and Tricks #1: Using Data from a Lookup Table Encoding of CLASS Variables in Regression Analysis - Better understand the ORDINAL encoding SAS Sample 31584: Creating a Key-Value-Table  SUGI2006: Efficient “One-Row-per-Subject” Data Mart Construction for Data Mining     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 SGF2018: An Easier and Faster Way to Untranspose a Wide File (co-authorship with A. Tabachneck, M. Kastin, J. Matise)       SAS Press books   Data Preparation for Analytics Using SAS (2007) Data Quality for Analytics Using SAS (2012) Applying Data Science - Business Case Studies Using SAS (2017)     Further References   Ask-the-expert Sessions (in German) Womit Sie „DATA=“ in den analytischen Procedures von SAS am besten füttern - Teil 1 Womit Sie „DATA=“ in den analytischen Procedures von SAS am besten füttern - Teil 2   Articles  Medium LinkedIn   Github Data Preparation for Data Science Data Quality for Data Science   SAS Communities: Article Overivew   Youtube Playlists Data Preparation for Data Science Data Science Case Studies   Gerhard Svolba, April 2022 (18°12'45" - 16°19'26"E) ... View more

This article introduces the %TS_HISTORY_CHECK macro which analyses the influence of the length of the available data history of a time series on the quality of the forecast for future periods.  Additionally, for each of the time series and the different simulation repetitions per time series, the length of the time series that delivers the best forecast quality is selected. Based on these results, the distribution of the optimal length of the time history over all-time series examples can be seen in more detail.   The rationale of the article and the work on this macro is that analysts usually invest a lot of time in finetuning their forecasting models. However little focus is given to the, positive and negative, influence of the length of the time series. Not all models and model parameters handle the (downweighing) of "old" data in a sufficient way. Long or short data history?   This allows you to gain a better understanding on the influence on the length of your timeseries on forecast accuracy. There are cases which correspond to intuition that, on average, a longer time history improves forecast quality. However there are also cases where the individual optimal lengths of the time history can also be very short (from 1 to 12 months). This is especially true for time series where the underlying patterns change frequently and quickly and, therefore, the more recent history is more important than the older history.   The author published an article on medium.com "Determining the best length of the history of your timeseries data for timeseries forecasting" which shows the business results and business interpretation of this analysis. Find more links in the last section of this article.   Two macros are presented here. Macro %TS_HISTORY_CHECK that uses PROC HPFENGINE and requires SAS High-Performance Forecasting Macro %TS_HISTORY_CHECK_ESM that used PROC ESM and requires SAS/ETS or SAS Econometrics   Both macros are used on two different dataset to illustrate the usage and the interpretation of the findings. The classic SASHELP.AIR dataset with one time series on a monthly basis and 144 time points. Data from a retail company: 16 different time series with a length of 170 are available here.   Simulation procedure   The simulation procedure is as follows: For all time series that are available for analysis, the time history is truncated to the length of 1. Based on this 1 value data, a forecast for the next 12 periods is performed and evaluated against the true (untouched) data from the out-of-sample data. In the next step, the available time history is increased backwards to the last 2 months. Again, the forecast quality is assessed on the next 12 months. This procedure is iterated by adding an additional historic month to each iteration until a maximum data history of 48 months is reached.   This procedure is also iterated for different start points of the time series. This means that the above setup is “shifted” in the time series to avoid studying results which only depend on one start point.   Use Case 1 - SASHELP.AIR   Preparation of the data   An example call of the macro is shown based on the SASHELP.AIR data. As the macro requires finding time series ID for each time series, this variable has to be provided, even if the data only contain one time series.   data air;  set sashelp.air;  tsid = 1; run;   Option 1 - %TS_HISTORY_CHECK (requires the HPFENGINE procedure)   You can invoke the macro on the SASHELP.AIR data as follows   %ts_history_check(data=air,tsid=tsid,y=air, timeid=date,interval=month, minhist=2,maxhist=48, shiftfrom=0,shiftto=4,shiftby=1, periodvalid=12, mrep=sashelp.hpfdflt,sellist=tsfsselect, stat=mape,aggrstat=median);   The output shows a linechart. The X-axis represents the respective number of available history months. The Y-axis represents the median MAPE value over all-time series and all shift scenarios. You see that especially in the first 12-18 months additional history information decreases the forecast error. You also see that the median MAPE however slightly increases with a longer time history after ~2 years, indicating that a longer history is not necessarily beneficial for forecasting on this data.     The data generated for the above analysis are also used to display a barchart of history lengths which led to the smallest forecast error. You see a cumulation of values between 15-18 months. Note that there is only one time series (the data from SASHELP.AIR), however as you specified a shift from 0-4 by 1, you receive 5 results as the timeseries is used more than once in different points of the history.     Option 2 - %TS_HISTORY_CHECK_ESM (based on the ESM procedure of SAS/ETS or SAS Econometrics)   The advantage of  the HPFENGINE procedure is that is automatically evaluates a large list of possible timeseries models (ARIMA, ExpSmoothing, ...) . If you however do not have the HPFENGINE available, you can use the %TS_HISTORY_CHECK_ESM macro which uses exponential smoothing models.   %ts_history_check_esm(data=air,tsid=tsid,y=air, timeid=date,interval=month, minhist=2,maxhist=48, shiftfrom=0,shiftto=4,shiftby=1, periodvalid=12, stat=mape,aggrstat=median);    You receive a similar output as above. However as only a limited set of forecasting models is used here the MAPE level is higher as above. You also see that if you only have ExpSmoothing models available, a longer time history is still beneficial. You also see the steep drop as soon as a full year of data history is available in the data (12, 24, 36, ... months).     The same result is seen in the barchart of history lengths which led to the smallest forecast error, where longer time series are beneficial.     Usage tip: Handling of log content and occurrence of errors   As the macro loops over SHIFTS and TIME_HISTORIES and large amount of notes and warnings might be written to the SAS log. This can lead to the fact that the log fills up quickly. You can use different options to turn of and turn on the generation of NOTES in the log or the printing of the macro code.   options nonotes nomprint ; %ts_history_check_ESM(data=timeseries_retail,tsid=tsid,y=sales, ....... ); options notes mprint; Another option is to pipe the log content into a file. Here you can use the PRINTTO procedure or specify an ALTLOG in your session.   proc printto log = "c:\tmp\logfile.txt"; run; %TS_HISTORY_CHECK(...); proc printto log=log; run; It can also happen that the macro execution shows an error, e.g. when you try to fit a certain timeseries model type with not enough data. This does not prevent the macro from functioning correctly. However you will see the error in the Logfile.   Use Case 2 - Retail Data (TIMESERIES_RETAIL)   Option 1 - %TS_HISTORY_CHECK (requires the HPFENGINE procedure)   Again you use the %TS_HISTORY_CHECK macro. Data TIMESERIES_RETAIL contain weekly retail sales data for 170 weeks. The validation period is set to 13 weeks (one quarter) and the time history is iterated from 2 - 130 weeks.   %ts_history_check(data=timeseries_retail,tsid=tsid,y=sales, timeid=date,interval=week, minhist=2,maxhist=130, shiftfrom=0,shiftto=42,shiftby=7, periodvalid=13, mrep=sashelp.hpfdflt,sellist=tsfsselect, stat=mape,aggrstat=median); For the TIMESERIES_RETAIL data you see an interesting result below. a data history from 2 - 35 weeks shows a MAPE of 0.14 increasing the data history to 50 weeks and more increases the MAPE. only as soon as you have 90 weeks and more in your data the MAPE drops to ~0.12.       A similar picture can be seen with the        A note on WEEKLY data and the WEEK interval - you probably need the WEEK.2 interval By default, the first day in a week in the SAS System is Sunday. If you provide weekly data where the date variable is aligned to the Monday as first day of the week (which is more common in European countries) you need to be careful how your data is aligned in the forecasting procedures.   If you use the INTERVAL=WEEK your forecast values will be again be aligned to a "standard week" with a start on Sunday. You might get troubles, joining your data back to the original data (start on Monday) or when you provide them to other systems where they shall be processed further (they maybe expect a timestamp for Monday). In such a case you have to use the WEEK.2 interval value. This tells the system to you want to have weeks that only start on the 2nd day (Monday) and the alignment works.   Your turn! - Perform a pre-analysis on your time series data.   The above result is of course not generalizable on other data. However it shows, that it makes sense to study the influence of the length data history on the forecast error with your individual data to have a better view on the importance of a long or short data history.   Option 2 - %TS_HISTORY_CHECK_ESM (based on the ESM procedure of SAS/ETS or SAS Econometrics)   Fore completeness the case where not HPFENGINE procedure is available is shown as well.   %ts_history_check_ESM(data=timeseries_retail,tsid=tsid,y=sales, timeid=date,interval=week, minhist=2,maxhist=130, shiftfrom=0,shiftto=42,shiftby=7, periodvalid=13, stat=mape,aggrstat=median);       Available parameters for the macro   Parameters for macro %TS_HISTORY_CHECK   The following parameters can be specified with the macro %TS_HISTORY_CHECK: DATA: Name of the data set that contains the time-series data. Note that the data need to be in a longitudinal format and that there needs to be an ID variable that identifies the time series. TSID: Name of the variable that contains the time-series ID variable. Default = TSID. Y: Name of the variable that contains the dependent variable of the time series. Default = Y. TIMEID: Name of the variable that contains the time values of the variable. INTERVAL: Time interval definition of the TIMEID variable; for example, MONTH, WEEK, ... Default = MONTH. MINHIST: Minimum history length that will be tested. MAXHIST: Maximum history length that willl be tested. SHIFTFROM: Shift start value that will be used to run the scenario with different subsets of the data. Default = 0. SHIFTTO: Shift end value that will be used to run the scenario with different subsets of the data. Set this value to the SHIFTFROM value to only run on shift iteration. For example 0 for a non-shifted scenario. Default = 12. SHIFTBY: Shift-BY value for SHIFTFROM and SHIFTTO. PERIODVALID: Number of (future) periods that shall be used to validate the forecasting results. Default = 12. MREP: Name of the SAS catalog that contains the model repository that is used by PROC HPFENGINE. Specify with library name. Default = SASHELP.HPFDFLT. SELLIST: Name of the selection list in MPRE, which contains the list of forecasting models that shall be used by PROC HPFENGINE. STAT: Statistic that shall be used for model selection in HPFENGINE and calculation of the validation statistics. Valid values are MAPE and RMSE. Default = MAPE. AGGRSTAT: Aggregation statistic that is used to aggregate the results over shifts and time series. Refer to the documentation of the means procedure for the possible values. Default = MEDIAN   Parameters for macro %TS_HISTORY_CHECK_ESM   The following parameters are additionally available with the %ts_history_check_ESM macro: SEASONALITY: Specifies the length of the seasonal cycle. For example, 12 for a season with 12 months. Default = 12. MODEL: Specifies the forecasting model to be used to forecast the time series. Possible values include WINTERS, ADDWINTERS, SIMPLE, SEASONAL. Refer to the documentation of the ESM procedure for a complete list of possible values. Default = SEASONAL.   Note that the SELLIST and the MREP parameter are not available with the %TS_HISTORY_CHECK_ESM macro.     A rough overview on the implementation of the %TS_History_Check macro   The macro first prepares the input data by creating a surrogate LEAD variable which has negative values for the available history. Time series which have a length which is shorter then the requirement length based on the parameters (&MAXHIST, &SHIFTTO, &PERIODVALID) are removed from the analysis.   The macro has an outer loop on SHIFTS and an inner loop on available time histories.     %do shift = &shiftfrom. %to &shiftto. %by &shiftby.; %do history = &minhist. %to &maxhist.;   In each run data are prepared accordingly to the parameters and only the defined subset of variables is provided to the analysis.     data _Hist_check_tmp_input_; set _Hist_check_tmp_(where = (_count_obs_ >= &minHist.)); _y_valid_ = &y.; if _lead_*(-1) <= (&periodvalid + &shift.) then &y. = .; if _lead_*(-1) > (&periodvalid + &shift. + &history) then delete;   PROC HPFENGINE and PROC ESM are used to generate the forecasts in the two macros. They can also be replaced by any other forecasting procedure, such as PROC ARIMA, as long as an output is produced that corresponds to the output table structure of PROC HPFENGINE.   Depending on the available SAS modules you can run the macro with an SAS/ETS or SAS Econometrics license where the ESM procedure is used.     proc esm data=_Hist_check_tmp_input_ out=_out_ outfor = _Hist_check_tmp_fc_(drop = lower upper error std) seasonality=&seasonality lead=&periodvalid; by &tsid; id &timeid interval = &interval; forecast &y/model=&model; run;   If you also have a SAS Forecast Server license you can also use macro %TS_History_Check which uses the HPFENGINE procedure.     Then the forecast values are joined with the original data.   proc sql; create table _Hist_check_tmp_fc_xt_ as select a.*,b._lead_,b._y_valid_ from _Hist_check_tmp_fc_(drop = _name_) as a left join _Hist_check_tmp_input_ as b on a.&tsid. = b.&tsid. and a.&timeid. = b.&timeid. order by &tsid., &timeid.; quit;   and the MAPE an RMSE are calculated manually   data _ape_; set _Hist_check_tmp_fc_xt_; _FC_Period_ = (_lead_*(-1) <= &periodvalid); _APE_ = abs(predict-_y_valid_)/_y_valid_; _MS_ = (predict-_y_valid_)**2; run; proc means data = _ape_(rename = (_ape_ = mape _ms_ = _mse_)) noprint nway; class &tsid _fc_period_; var mape _mse_; output out = _mape_(drop=_type_ _freq_ where=(_fc_period_=1)) mean=; run; data _mape_; set _mape_; rmse = _mse_ ** 0.5; drop _mse_; shift=&shift; History=&history; drop _fc_period_; run;   This is performed for each loop and the statistics are appended to a dataset and are then displayed in graphs and tables.   Presentation and Live Demo       Links and References medium.com "Determining the best length of the history of your timeseries data for timeseries forecasting"    There are more SAS Communities from the author with a focus on time series analysis 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 Simulate timeseries data with a SAS DATA Step and SAS Functions Getting More Insight into Your Forecast Errors with the GLMSELECT and QUANTSELECT Procedures 3 ways to consider movable holidays in SAS Webinar at Youtube: https://www.youtube.com/watch?v=-WsYvSasl9w&list=PLdMxv2SumIKsqedLBq0t_a2_6d7jZ6Akq&index=3   This example has been taken from my SAS Press book Data Quality for Analytics Using SAS see especially chapter 20, pp 260, chapter 21, pp 265, Appendix D  Download the SAS programs and SAS datasets: https://github.com/gerhard1050/Data-Quality-for-Data-Science-Using-SAS  Further books of the author in SAS Press: Applying Data Science - Business Case Studies Using SAS Data Preparation for Analytics Using SAS Slide Collection: Slides Deck #102 at https://github.com/gerhard1050/DataScience-Presentations-By-Gerhard      ... View more

This article introduces the macros %MAKEWIDE and %MAKELONG to transpose your data between different formats. The macros have been introduced with the SAS Press Book  Data Preparation for Analytics Using SAS and have been used in the SAS training class "Building Analytic Data Marts". Both macros are based on the TRANSPOSE procedure. The benefit of using the macros (compared to the TRANSPOSE procedure) is you can transpose more than one variable in one macro call you can write shorter code, especially when moving from a WIDE to a LONG data structure    What is a LONG or a WIDE dataset? Why should I transpose them?   Transposing data is a frequent task in data preparation. There are two main data structure in analytics and data science.  The one-row-per-subject data structure, where all information about an analysis subject is stored in one record. This structure is also referred to as a WIDE structure. The multiple-row-per-subject or longitudinal data structure, where the information about an analysis subject is stored in multiple records. These multiple records often exist because of repeated measurements over time, or because of hierarchical relationships with several sub entries. This structure is also referred to as a LONG structure.   For different types of analyses, the data is needed in different structures. Machine learning techniques often need the data in a one-row-per subject structure. Time Series Forecasting techniques or the plotting of information over time usually needs a multiple-row-per-subject structure.   Changing the data structure between LONG <--> WIDE is called TRANSPOSING.     The sample data   This article uses the DOGS dataset as sample data. The data have repeated measurements over time. Histamine and Heamoglobin data for 16 dogs at 4 different timepoints (0,1,3,5) are available. This data has been provided in both data structures, WIDE and LONG.    The DOGS_WIDE dataset   H H H H e e e e H H H H a a a a i i i i m m m m D s s s s o o o o e t t t t g g g g p a a a a l l l l D l m m m m o o o o o D e i i i i b b b b g r t n n n n i i i i I u e e e e e n n n n D g d 0 1 3 5 0 1 3 5 1 Morphine N 0.04 0.20 0.10 0.08 14.7 14.0 14.2 14.1 2 Morphine N 0.02 0.06 0.02 0.02 14.4 14.5 14.2 14.2 3 Morphine N 0.07 1.40 0.48 0.24 14.4 14.2 14.9 14.2 4 Morphine N 0.17 0.57 0.35 0.24 15.0 14.9 14.3 14.3 5 Morphine Y 0.10 0.09 0.13 0.14 14.5 14.7 14.0 14.2 6 Morphine Y 0.12 0.11 0.10 . 14.4 14.5 14.9 15.0 7 Morphine Y 0.07 0.07 0.06 0.07 14.3 14.5 14.0 14.1 8 Morphine Y 0.05 0.07 0.06 0.07 14.3 14.1 14.7 14.2 9 Trimethaphan N 0.03 0.62 0.31 0.22 14.1 14.0 14.1 14.4 10 Trimethaphan N 0.03 1.05 0.73 0.60 14.1 14.7 14.5 14.3 11 Trimethaphan N 0.07 0.83 1.07 0.80 14.6 15.0 14.2 14.0 12 Trimethaphan N 0.09 3.13 2.06 1.23 14.5 14.4 14.3 14.1 13 Trimethaphan Y 0.10 0.09 0.09 0.08 14.7 14.3 14.2 14.6 14 Trimethaphan Y 0.08 0.09 0.09 0.10 14.9 14.2 14.4 14.1 15 Trimethaphan Y 0.13 0.10 0.12 0.12 14.7 14.7 15.0 14.5 16 Trimethaphan Y 0.06 0.05 0.05 0.05 14.8 14.9 14.7 14.5    The DOGS_LONG Dataset (only dogs 1-3 shown)     Dog ID Drug Depleted Histamine Measurement Heamoglobin 1 Morphine N 0.04 0 14.7 1 Morphine N 0.20 1 14.0 1 Morphine N 0.10 3 14.2 1 Morphine N 0.08 5 14.1 2 Morphine N 0.02 0 14.4 2 Morphine N 0.06 1 14.5 2 Morphine N 0.02 3 14.2 2 Morphine N 0.02 5 14.2 3 Morphine N 0.07 0 14.4 3 Morphine N 1.40 1 14.2 3 Morphine N 0.48 3 14.9 3 Morphine N 0.24 5 14.2   Transposing from LONG to WIDE    Using the TRANSPOSE procedure The following code shows how you can use the TRANSPOSE procedure to create a WIDE dataset based on the LONG dataset.    PROC TRANSPOSE DATA = dogs_long PREFIX =histamine OUT = dogs_wide_hist1; BY dogid drug depleted; VAR Histamine; ID Measurement; RUN;   Note the following from the code:  You use a BY statement for DogID to force transposing for each dog. As the static variables DRUG and DEPLETED are the same for each dog, you can transfer them to the output dataset by adding them to the BY statement. Note that the data must be sorted BY DogID. The prefix for the new variables names HISTAMINE is specified in with the PREFIX option. You use the VAR statement to specify the variable which contains the values that shall be transposed.   The output data (only 3 dogs are shown) looks as follows:   Dog ID Drug Depleted _NAME_ histamine0 histamine1 histamine3 histamine5 1 Morphine N Histamine 0.04 0.20 0.10 0.08 2 Morphine N Histamine 0.02 0.06 0.02 0.02 3 Morphine N Histamine 0.07 1.40 0.48 0.24   Using the %MAKEWIDE macro The %MAKEWIDE macro facilitates transposing data from a LONG to a WIDE structure. If you only transpose one variable, there is not a big different to the PROC TRANSPOSE code. The %MAKEWIDE macro, however, can transpose more than one variable on one go.   The macro has the following parameters:   DATA and OUT: The names of the input and output data sets, respectively. ID: The name of the ID variable that identifies the subject. COPY: A list of variables that occur repeatedly with each observation for a subject and will be copied to the resulting data set. Note that the COPY variable(s) must not be used in the COPY statement of PROC TRANSPOSE for our purposes, but are listed in the BY statement after the ID variable. We assume here that COPY variables have the same values within one ID. VAR: The variables that holds the values to be transposed.  TIME: The variable that numerates the repeated measurements.   You use the following code to transpose both variables, HISTAMINE and HEAMOGLOBIN to a WIDE structure with the %MAKELONG macro.    %MAKEWIDE(DATA=dogs_long, OUT=dogs_wide_both2, ID=dogid, COPY=drug depleted, VAR=heamoglobin Histamine, TIME=Measurement); Technically the %MAKEWIDE macro scans the list of variables and performs a transpose for each variable and then merges the results together. The output for 3 dogs is shown below, note that both variables, HISTAMINE and HEAMOGLOBIN, are now transposed.    Dog ID Drug Depleted heamoglobin0 heamoglobin1 heamoglobin3 heamoglobin5 Histamine0 Histamine1 Histamine3 Histamine5 1 Morphine N 14.7 14.0 14.2 14.1 0.04 0.20 0.10 0.08 2 Morphine N 14.4 14.5 14.2 14.2 0.02 0.06 0.02 0.02 3 Morphine N 14.4 14.2 14.9 14.2 0.07 1.40 0.48 0.24   Transposing from WIDE to LONG   Using the TRANSPOSE procedure   You can also use the TRANSPOSE procedure to transpose the data from a WIDE to LONG structure.     proc transpose data=dogs_wide Name=_measure out=dogs_long_1var(rename=(col1=Histamine) where=(_measure contains "Histamine")) ; by dogid drug depleted; run;   Again, a BY statement is used where you can copy variables, which do not differ by DOGID variables to the output dataset. Note that the variable that contains the names the former variables that where transposed can be specified with the NAME option. The value _MEASURE is used here. As both variables, HISTAMINE and HEAMOGLOBIN are transposed here and aligned in rows underneath each other, a WHERE clause is used to only show the HISTAMINE values. You might want to keep both variables in the output dataset, however then you need to treat them separately if you want to retrieve the measurement numbers and you cannot use the datastep as shown below. After you run this code, you are almost done. You see the data in a LONG structure. However you need to extract the measurement values 0,1,3,5 manually from the _MEASURE values.       Dog ID Drug Depleted _measure Histamine 1 Morphine N Histamine0 0.04 1 Morphine N Histamine1 0.20 1 Morphine N Histamine3 0.10 1 Morphine N Histamine5 0.08 2 Morphine N Histamine0 0.02 2 Morphine N Histamine1 0.06 2 Morphine N Histamine3 0.02 2 Morphine N Histamine5 0.02 3 Morphine N Histamine0 0.07 3 Morphine N Histamine1 1.40 3 Morphine N Histamine3 0.48 3 Morphine N Histamine5 0.24   You can use a datastep for this task. Here you use the TRANWRD function to replace the "HISTAMINE" string with BLANK and you use the INPUT function to convert the numbers 0,1,3,5 from character to numeric format.     data dogs_long_1var; set dogs_long_1var; format Measurement 8.; Measurement = input(tranwrd(_measure,"Histamine",""),$8.); drop _measure; run;   Finally you end up, with the desired data structure.     Dog ID Drug Depleted Histamine Measurement 1 Morphine N 0.04 0 1 Morphine N 0.20 1 1 Morphine N 0.10 3 1 Morphine N 0.08 5 2 Morphine N 0.02 0 2 Morphine N 0.06 1 2 Morphine N 0.02 3 2 Morphine N 0.02 5 3 Morphine N 0.07 0 3 Morphine N 1.40 1 3 Morphine N 0.48 3 3 Morphine N 0.24 5   Using the %MAKELONG macro   The %MAKELONG macro performs the TRANSPOSE step and the datastep for you in one go, without having to code it extra. Also you can transpose more than one variable, as shown in this example.     %MAKELONG(DATA=dogs_wide, OUT=dogs_long_both2, ID=Dogid, COPY=drug depleted, ROOT=heamoglobin Histamine, MEASUREMENT=Measurement);   The macro transposed each variable separately and merges the output datasets.       Dog ID Drug Depleted heamoglobin Measurement Histamine 1 Morphine N 14.7 0 0.04 1 Morphine N 14.0 1 0.20 1 Morphine N 14.2 3 0.10 1 Morphine N 14.1 5 0.08 2 Morphine N 14.4 0 0.02 2 Morphine N 14.5 1 0.06 2 Morphine N 14.2 3 0.02 2 Morphine N 14.2 5 0.02 3 Morphine N 14.4 0 0.07 3 Morphine N 14.2 1 1.40 3 Morphine N 14.9 3 0.48 3 Morphine N 14.2 5 0.24   Using the %UNTRANSPOSE macro In 2018, Arthur S. Tabachneck, posted a paper on SAS Global Forum where he introduced a much more detailed version of a macro to perform a wide-to-long transposing. His work extends the %MAKEWIDE macro by providing more options and considering more special cases. Some years ago one of the current paper’s authors realized this and offered the MAKELONG macro to the SAS community (Svolba, G. 2008, 2014). Dr. Svolba’s macro was definitely a step in the right direction, as it ran the necessary PROC TRANSPOSE and merge steps without requiring the user to do such things as redundantly type variable names, assign unique names for all of the temporary files that might have to be created, write code to extract ID values from the transposed variable names, or create a data step to merge the resulting files. Dr. Svolba’s macro did everything that was needed as long as the input and ID variables were all numeric, and the wide file’s transposed variable names didn’t include such strings as prefixes, delimiters, and suffixes.   Feature Engineering or Transposing?   In some cases it is important to have the original data just in another structure. Here, transposing is the correct task. In other cases you want to derive the content from the longitudinal data and describe the behavior of the analysis subjects, rather than having the original data. Here Feature Engineering (creating derived variables) is more important. Articles on feature engineering can be found here: Feature Engineering #1 - Using Correlation Analysis to Describe Behavior over Time Feature Engineering #2 – Accordence to Predefined Pattern Feature Engineering #3 – Describing the Trend over Time 3 ways to consider movable holidays in SAS   Further Reading This example has been taken from my SAS Press book Data Preparation for Analytics Using SAS  see chapter 14 and chapter 15 Further books of the author in SAS Press: Data Quality for Analytics Using SAS  Applying Data Science - Business Case Studies Using SAS   (48°13'01''N - 16°21'29''O) ... View more

Authors       Gerhard Svolba, SAS Austria Paper 078-31 - SUGI 31 in SAS Francisco, CA, 2006   Abstract Creating a "one-row-per-subject" data mart is a fundamental task when preparing data for data mining. To answer the underlying business question, the analyst or data-mart programmer is challenged to distill the relevant information from various data sources. Creating a data mart involves more than reading columns from a source table to the data mart. It also includes the aggregation or transposition of observations from "multiple-row-per-subject" tables like transactional tables and time histories. This process is a critical success factor for being able to answer the business question or to have good predictors available for a target event or target value. This paper shows the details of input data structures for a "one-row-per-subject" data mart. It also discusses the "one-row-per-subject" paradigm from a technical and a business point of view, and shows how data, some of which include hierarchical dependencies, is aggregated into a “single-row-per-subject.” A comprehensive example shows how a "one-row-per-subject" data mart is created from various data sources. 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.                     DOWNLOAD THE FULL PAPER https://support.sas.com/resources/papers/proceedings/proceedings/sugi31/078-31.pdf   DOWNLOAD THE SLIDES Slide Collection: Slides Deck #121 and #122 at https://github.com/gerhard1050/DataScience-Presentations-By-Gerhard  Find the original presentations from SUGI 31 (2006) in the attachment. Note that you find the re-worked presentation in the link above.   CONCLUSION This paper shows that data preparation is a key success factor for analytical projects. Besides multiple-row-persubject data marts or longitudinal data marts, the one-row-per-subject data mart is frequently used and is central for statistical and data mining analyses. In many cases the available source data have a one-to-many relationship to the subject itself. Therefore data needs to be transposed or aggregated. The selection of the transposition and aggregation methods is primarily driven by business considerations of what will give meaningful derived variables. This paper shows you ways to create a one-row-per-subject data mart from a conceptual and a coding point of view. Anyone who is interested in more details on Data Preparation for Analytics is referred to my book with the same name, which will be published by SAS Press (Book# 60502) in September 2006 References     Recommended Reading 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   ASK THE EXPERT SEMINAR (in German) Womit Sie „DATA=“ in den analytischen Procedures von SAS am besten füttern - Teil 1 Womit Sie „DATA=“ in den analytischen Procedures von SAS am besten füttern - Teil 2   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

(This article is part of a growing collection of articles on feature engineering for data science. Find more links at the end of this article.)   Feature Engineering is an important tool for (supervised) machine learning. Model accuracy and interpretability benefits from variables that precisely describe the behavior of the analysis subjects. In many cases these features are derived from transactional data which are recorded, e.g. over time. In some cases, simple descriptive measures like the mean or the sum provide a very good picture of the analysis subjects. Often it is important to dig deeper to adequately describe the behavior. Analytic methods help to calculate KPIs that measure the trend over time per customer, the accordance with predefined pattern or the correlation of individual customer with the average customer.       Transposing the data is not enough! - Describe the behavior   Transposing longitudinal data to "WIDE" format also results in a table which has one record per analysis subset. Of course, SAS offers a large and powerful toolset to change between different data structures, e.g. by using the TRANSPOSE procedure, using the DATASTEP. Many SAS users have also programmed and published different macros for that purpose. For powerful analytical models it is however not enough to "replicate" the original data just in another format. You want to describe the behavior of your analysis subjects. You can to this by calculating different analytic measures.   This article focuses on the "accordance of an analysis subject with predefined pattern”  and shows the rationale, the coding and the interpretation of the results. You will see that it is very easy in SAS to use the FREQ procedure to create a powerful feature that helps to describe the behavior of analysis subjects. This feature can also be used to rank the analysis subjects by the accordance with the predefined pattern and to prioritize analysis subjects, in our case account managers, for further investigation and discussion.   There is also a link to a Youtube Recording with a presentation on this topic, which you can find below.   Using the historic seasonal demand pattern as a target for sales planning   Assume a company which is active in catering business. The company runs some own restaurants in different (tourism) regions of Austria, and it also performs catering at large events. In order to fulfill the demand, the company also has sub-contrators which provide staff, logistics, services, food, and drinks. The sales force of this company should make pre-contracts with companies to secure the capacities already early, to be able to fulfill the demand throughout the year.   The historic monthly demand can be aggregated in different ways in SAS. Here an example using the FREQ procedure is shown.    proc freq data=sales_month; table month / nocum out=HistoricDemand(rename=(percent=HistoricPct)); weight Sales_EUR; run;   The WEIGHT statement is used to aggregate the sales amount in euros. The TABLE statement uses month as a category to have monthly aggregates. The output statistics PERCENT is used to describe the relative distribution per calendar month and is renamed to "HistoricPct".   proc sgplot data=HistoricDemand; vline Month / response=HistoricPct lineattrs=(thickness=3 pattern=1 color=grey ) ; yaxis min=0 max=20; run; The SGPLOT procedure and the VLINE statement is used to draw a line chart for the historic percentages.     You see a remarkable historic pattern with a first peak in February. This can be due to the business in some skiing regions where February is a very busy month due to the winter school holidays in these months. The summer months from May till August also have a very high value, which might be due to many outdoor events and open air concert caterings. Finally December shows a peak due to Christmas parties and also skiing events in the Christmas holidays and New Years Eve.   Which account managers comply with the pre-set target pattern?   If you just plot the individual values against the pre-defined pattern you can hardly see who has a similar pattern. Using analytics measures like the Chi2-statistic allows you to order the account managers by their similarity with the predefined pattern.     You use the FREQ procedure to calculate the Chi2 statistic for each AccountManager.   ods exclude all; proc freq data=sales_month ; by AccountManager; table month / nocum out=Sales_AccMgr chisq(testp=HistoricDemand(rename=(HistoricPct=_testp_))); weight Sales_EUR; ods output OneWayChiSq=Chi2_AccMgr(drop=table label cvalue); run; ods exclude none;   Use a BY statement to run the analysis by AccountManager. Again you use variable MONTH as a categorical variable and SALES_EUR as the weight variable. However here you also specify the CHISQ option and use the HistoricDemand which has been derived and stored in a SAS dataset above as the test proportion with the TESTP option. The FREQ procedures now calculates a Chi2 statistic for the deviation of the monthly distribution for each account manager from the pre-defined target distribution. The output object OneWayChiSq contains the chi-square values and the p-values of the respective test. The output object is output to a dataset using the ODS OUTPUT statement. Note that using the NOPRINT option in the PROC FREQ statement would avoid the output window from filling up, but also suppresses the creation of the output object at all. For this reason the ODS EXCLUDE statement has been used to turn off the create of output before the procedures runs and and turn it on again afterwards.  The output dataset has the following content and structure. Multiple lines are shown per account manager. So you either need to pick one statistic or it needs to be transposed to a one-row-per-subject structure.   Account Manager Name1 Label1 cValue1 nValue1 Alfred _PCHI_ Chi-Square 1720.9937 1720.993729 Alfred DF_PCHI DF 11 11.000000 Alfred P_PCHI Pr > ChiSq <.0001 0 Alice _PCHI_ Chi-Square 1669.5182 1669.518205 Alice DF_PCHI DF 11 11.000000 Alice P_PCHI Pr > ChiSq <.0001 0 Barbara _PCHI_ Chi-Square 2205.1988 2205.198761 Barbara DF_PCHI DF 11 11.000000 Barbara P_PCHI Pr > ChiSq <.0001 0 You use the TRANSPOSE procedure to transpose it to keep the Chi2 value and the p-value for each account manager.   proc transpose data=Chi2_AccMgr out=Chi2_Results (drop=_name_ df_pchi); by AccountManager; var nValue1; id name1; run;   In order to improve the display you can sort the table by descending p-value.   proc sort data=Chi2_Results; by descending _pchi_; run;   And use a SAS datastep to format the output and the _N_ variable to create a rank variable.   data Chi2_AccMgr_Results; format Rank 3.; set Chi2_Results; Rank = _N_; rename _pchi_ = Chi2_Value p_pchi = P_Value; format p_pchi percent8.3 _pchi_ 8.1; run;   Interpreting the output table You see the ranked output table below. As you ordered the table by descending Chi2 statistics (or p-value) you find those account managers at the top of the list which have the largest deviation from the predefined pattern. In order to illustrate this results for two account managers you find the individual line-chart compared with the overall line chart for Joyce (very high deviation) and for Jeffrey (very low deviation) below.   From a business point of view, this table allows you to see which account managers stick to the predefined targets and acquire resources according to the predefined pattern. And you also see which account managers are quite apart from the target pattern. You might want to investigate their individual sales pattern in more details. Maybe they have specific circumstances why they close their contracts in a certain way. Maybe importance of the predefined target pattern has not been made clear to them. The feature that has been derived just by using the FREQ procedure and a pre-aggregated target pattern can be used directly to create a sorted list and to see where to start such conversations first. In a machine learning context this derived feature can be used to describe the accordance of an analysis subject to a certain pattern. This feature can be used in unsupervised machine learning models to better segment your customer base or the members of your sales force. You can also use the feature in supervised machine learning models as input variable to potentially predict the outcome.         Reviewing individual line charts   Joining the overall pattern to the individual demand values   You can use the SQL procedure to join the HistoricDemand as created above to the monthly sales data.   proc sql; create table AccMgr_XT as select a.AccountManager, a.month, a.Sales_EUR as Sales_EUR_Obs format = 8., b.Count/11 as Sales_Eur_Exp format = 8. from sales_month as a left join HistoricDemand as b on a.month = b.month order by a.AccountManager, a.Month; quit;   Review the demand curve of an account managers with low deviation (Jeffrey)   You use the SGPLOT procedure with a line-chart for the (individually) observed amount for a certain account manager and a line chart for the overall demand distribution. Note that PATTERN, COLOR and TRANSPARENCY options are used to finetune the line chart.   proc sgplot data=AccMgr_XT; vline Month / response=Sales_EUR_Obs lineattrs=(thickness=3 ) transparency=0.2; vline Month / response=Sales_Eur_Exp lineattrs=(thickness=3 pattern=2 color=grey ) transparency=0.5 ; yaxis max=3000; where AccountManager = 'Jeffrey'; run;   You see that account managers "Jeffrey" who has a very low Chi2-statistic also exhibits a monthly demand distribution which is very similar to the predefined pattern.     Review the demand curve of an account managers with low deviation (JOYCE)   Whereas the demand curve for account manager "JOYCE" is quite different from the overall demand pattern. The chi2 statistic of ~2377 is almost three times as high as those of JEFFREY. This illustrates that using the chi2-statistic is a good indicator. However only reviewing the pattern shows you the individual difference. She has quite low values at the beginning of the year, but high values at the end of the year. It therefore makes sense to discuss with Joyce the rationale for her deviation. Maybe there is a good reason that she has a pattern like that.   proc sgplot data=AccMgr_XT; vline Month / response=Sales_EUR_Obs lineattrs=(thickness=3 ) transparency=0.2; vline Month / response=Sales_Eur_Exp lineattrs=(thickness=3 pattern=2 color=grey ) transparency=0.5 ; yaxis max=3000; where AccountManager = 'Joyce'; run;     Conclusion   You have seen that it is very easy in SAS to use the FREQ procedure to create a powerful feature that helps to describe the behavior of analysis subjects. This feature can also be used to rank the analysis subjects by the accordance with the predefined pattern and to prioritize analysis subjects, in our case account managers, for further investigation and discussion.   Be creative!   The article could also encourages you to to be creative, when it comes to using longitudinal data in your machine learning analyses. In this example the Chi2-statistic has been a good indicator whether an account manager is adhering to the predefined pattern.  Your data might require other forms of aggregations or calculations - Be creative! The analytic toolbox is very large and you can find the appropriate measure for your use case.             Youtube Webinar There is a webinar on the same topic in my Data Preparation for Data Science Playlist. It explains the case as a software demo. Links and References   There are more SAS Communities article which show examples for feature engineering:  Feature Engineering #1 - Using Correlation Analysis to Describe Behavior over Time Feature Engineering #3 – Describing the Trend over Time 3 ways to consider movable holidays in SAS   This example has been taken from my SAS Press book  Applying Data Science - Business Case Studies Using SAS see chapter 19 and chapter 20 Further books of the author in SAS Press: Data Quality for Analytics Using SAS Data Preparation for Analytics Using SAS   "Ask-the-expert sessions" on Data Preparation for Data Science (in German): https://www.sas.com/de_at/webinars/ask-the-expert-serie-data-preparation-fuer-data-science-teil1.htm... https://www.sas.com/de_at/webinars/ask-the-expert-serie-data-preparation-fuer-data-science-teil2.htm... Slide Collection: Slides Deck #121 and #122 at https://github.com/gerhard1050/DataScience-Presentations-By-Gerhard  ... View more

(This article is part of a growing collection of articles on feature engineering for data science. Find more links at the end of this article.)   Feature Engineering is an important tool for (supervised) machine learning. Model accuracy and interpretability benefits from variables that precisely describe the behavior of the analysis subjects. In many cases these features are derived from transactional data which are recorded, e.g. over time. In some cases, simple descriptive measures like the mean or the sum provide a very good picture of the analysis subjects. Often it is important to dig deeper to adequately describe the behavior. Analytic methods help to calculate KPIs that measure the trend over time per customer, the accordance with predefined pattern or the correlation of individual customer with the average customer.       Transposing the data is not enough! - Describe the behavior   Transposing longitudinal data to "WIDE" format also results in a table which has one record per analysis subset. Of course, SAS offers a large and powerful toolset to change between different data structures, e.g. by using the TRANSPOSE procedure, using the DATASTEP. Many SAS users have also programmed and published different macros for that purpose. For powerful analytical models it is however not enough to "replicate" the original data just in another format. You want to describe the behavior of your analysis subjects. You can to this by calculating different analytic measures.   This article focuses on the "Describing the Trend over Time”  and shows the rationale, the coding and the interpretation of the results.    Example Data "Service Usage"   Consider the following usage data for 10 customers in a wide format. WIDE refers here to the fact that the data are not in transactional/longitudinal form (LONG), but are already arranged for one row per customer. Variables M1-M6 contain the usage for the months 1-6.   data usage_wide; input CustID M1 M2 M3 M4 M5 M6 8.; Cards; 1 92 94 98 87 78 72 2 22 24 30 28 31 30 3 100 120 125 128 120 115 4 43 43 43 . 42 41 5 20 27 30 35 31 35 6 16 24 18 25 30 24 7 80 70 60 50 57 63 8 90 95 80 100 100 90 9 47 47 47 47 47 47 10 50 52 0 50 0 52 ; run;   Note that in the attached example program this data is available as a dataset with 10 customers. If you want to run the example on larger data, you can use the attached USAGE_LONG data directly in the REG procedure below.   Investigating the data graphically   Step 1 - Transpose from a WIDE to a LONG structure In order to plot this data you have to transpose it to a long format. You can use the TRANSPOSE procedure for this purpose:   PROC TRANSPOSE DATA = usage_wide OUT = usage_long; BY custId; RUN;   In addition you need a SAS datastep to extract the month value (1-6) from the former variable names M1-M6.   DATA usage_LONG; SET usage_LONG; FORMAT Month 8.; RENAME col1 = Usage; Month = compress(_name_,'M'); DROP _name_; RUN;   This leads to a dataset that has the following structure and content (for the first 2 customers):   Cust Obs ID Usage Month 1 1 92 1 2 1 94 2 3 1 98 3 4 1 87 4 5 1 78 5 6 1 72 6 7 2 22 1 8 2 24 2 9 2 30 3 10 2 28 4 11 2 31 5 12 2 30 6   Step 2 - Use the SGPLOT procedure   Next you use the SGPLOT procedure to create a line chart for selected customers (1,3,4,5,7):   proc sgplot data=usage_LONG; series x=Month y=Usage / group=CustID lineattrs=(thickness = 3) curvelabel; where custid in (1,3,4,5,7); run;   Note that the CURVELABEL option is used to add the group-id at the end of each line.   Business interpretation of the line curves   You see the following lines:   If you now investigate the example lines for their long term trend (month 1-6) and for their short term trend (month 4-6) you can classify them as follows: Customer 4: LongTerm Trend FLAT (=), ShortTerm Trend FLAT (=) Customer 5: LongTerm Trend UP (+), ShortTerm Trend UP (+) Customer 3: LongTerm Trend UP (+), ShortTerm Trend DOWN (-) Customer 1: LongTerm Trend DOWN (-), ShortTerm Trend FLAT (-)   Describing the TREND with the REG procedure   In order to perform such a classification automatically you can use the REG procedure as show in the following example: (Note that in the attached example program this data is available as a dataset with 10 customers. If you want to run the example on larger data, you can use the attached USAGE_LONG data directly in the REG procedure below.)   PROC REG DATA = usage_LONG NOPRINT OUTEST=Trend_LongTerm(KEEP = CustID month RENAME = (month=LongTerm)); MODEL usage = month; BY CustID; RUN;   You run the analysis for each Customer (BY CustID). Make sure that your data are sorted by CustID. You model the dependent variable USAGE on the independent variable MONTH. It is important to use the NOPRINT option to avoid the create of printed output in the output window for each BY group! You are interested in the SLOPE of the regression line. You can obtain the parameter estimates for example by using the OUTEST= option. The SLOPE value is stored under the same variable name as the independent variable (MONTH).  You use the RENAME option to rename the SLOPE value for the long-term trend to LONGTERM in the dataset TREND_LONGTERM.   This results in a dataset with one row per subject, where the slot for each customer is stored:   Cust ID LongTerm 1 -4.54286 3 2.22857 4 -0.39535 5 2.62857 7 -3.82857   Calculate the Short-Term trend   You calculate the short-term trend with similar statements. Here you add a WHERE clause to restrict the analysis to months 4-6.   PROC REG DATA = usage_LONG NOPRINT OUTEST=Trend_ShortTerm(KEEP = CustID month RENAME = (month=ShortTerm)); MODEL usage = month; BY CustID; WHERE month in (4 5 6); RUN;   Combine the long-term and the short-term trend   You now want to combine the original customer data with the coefficients for the longterm and the shortterm trend.  You use a datastep with a MERGE statement as shown below to merge the 3 datasets. In order to classify the values for SLOPE into "-" (if lower than -1), "+" (if larger than 1) and "=" otherwise, you can use the FORMAT procedure to specify a format for this task.     PROC FORMAT; VALUE est LOW -< -1 = '-' -1 - 1 = '=' 1 <- HIGH = '+'; RUN;   You use this format EST. in the datastep to derive a new variable which concatenates the class for the longterm and the shortterm trend.   DATA usage_wide_xt; MERGE usage_wide Trend_LongTerm Trend_ShortTerm; BY CustID; Format ShortTerm LongTerm 8.1 LongShortInd $2.; LongShortInd = CAT(put(LongTerm,est.),put(ShortTerm,est.)); RUN;   The PUT statement with the created EST. format is used to convert the numeric slope value into the classes "+", "=", "-". You use the CAT function to concatenate these values.   The resulting output in dataset looks as follows:   Cust Short Long ID M1 M2 M3 M4 M5 M6 LongTerm Term ShortInd 1 92 94 98 87 78 72 -4.5 -7.5 -- 2 22 24 30 28 31 30 1.7 1.0 += 3 100 120 125 128 120 115 2.2 -6.5 +- 4 43 43 43 . 42 41 -0.4 -1.0 == 5 20 27 30 35 31 35 2.6 0.0 += 6 16 24 18 25 30 24 1.9 -0.5 += 7 80 70 60 50 57 63 -3.8 6.5 -+ 8 90 95 80 100 100 90 1.0 -5.0 =- 9 47 47 47 47 47 47 0.0 0.0 == 10 50 52 0 50 0 52 -2.7 1.0 -=   You see that you have used the REG procedure to derive the trend feature for both, the longterm and the shortterm trend. These features can be used as input variables for example in supervised machine learning models. You have also classified these trend values and concatenated them to an easy-to-interpret category which shows how how the long and short term trend looks for each customers.   Optional: Display the classification in the line chart (for selected customers)   Adding the classification back to LONG data structure If you want to add this category to the LONG data structure to be able to plot it in a line chart, you can use the SQL procedure and a left join.   proc sql; create table long_xt as select catx("|",a.CustID,b.LongShortInd) as NewID, a.*, b.LongShortInd from usage_long as a left join usage_wide_xt as b on a.custid = b.custid order by a.custid, a.month ; quit;   Running the SGPLOT procedure Now you can again use the SPLOT procedure to plot the lines as shown above. However you use the newly created NewID variable as GROUP variable and show them at the end of each line using the CURVELABEL option.   proc sgplot data=long_xt; series x=Month y=Usage / group=NewID lineattrs=(thickness = 3) curvelabel;*datalabel=NewID; where custid in (1,3,4,5,7); run; In the visual verification you see that the categorization of the usage courses which have been discussed above are now automatically created for each customer.           Conclusion   The idea of this article is to illustrate how you can derive features from longitudinal data and use them for other data science tasks. The TREND feature which has been illustrated in this example are often a good indicator to describe the change of customer behavior over time. These features have been helpful for example to early indicate whether customers drop their usage and are at risk to cancel their contracts.   Be creative!   The article could also encourages you to to be creative, when it comes to using longitudinal data in your machine learning analyses.    Your data might require other forms of aggregations or calculations - Be creative! The analytic toolbox is very large and you can find the appropriate measure for your use case.             Links and References There is a SAS tip at Support.sas.com which contains non-commented version of this example: https://support.sas.com/kb/31/582.html   There are more SAS Communities article which show examples for feature engineering:  Feature Engineering #1 - Using Correlation Analysis to Describe Behavior over Time Feature Engineering #2 - Accordance with predefined pattern 3 ways to consider movable holidays in SAS   This example has been taken from my SAS Press book  Applying Data Science - Business Case Studies Using SAS see chapter 19 and chapter 20 Further books of the author in SAS Press: Data Quality for Analytics Using SAS Data Preparation for Analytics Using SAS   "Ask-the-expert sessions" on Data Preparation for Data Science (in German): https://www.sas.com/de_at/webinars/ask-the-expert-serie-data-preparation-fuer-data-science-teil1.htm... https://www.sas.com/de_at/webinars/ask-the-expert-serie-data-preparation-fuer-data-science-teil2.htm... Slide Collection: Slides Deck #121 and #122 at https://github.com/gerhard1050/DataScience-Presentations-By-Gerhard  ... View more

(This article is part of a growing collection of articles on feature engineering for data science. Find more links at the end of this article.)   Feature Engineering is an important tool for (supervised) machine learning. Model accuracy and interpretability benefits from variables that precisely describe the behavior of the analysis subjects. In many cases these features are derived from transactional data which are recorded, e.g. over time. In some cases, simple descriptive measures like the mean or the sum provide a very good picture of the analysis subjects. Often it is important to dig deeper to adequately describe the behavior. Analytic methods help to calculate KPIs that measure the trend over time per customer, the accordance with predefined pattern or the correlation of individual customer with the average customer.           Transposing the data is not enough! - Describe the behavior   Transposing longitudinal data to "WIDE" format also results in a table which has one record per analysis subset. Of course, SAS offers a large and powerful toolset to change between different data structures, e.g. by using the TRANSPOSE procedure, using the DATASTEP. Many SAS users have also programmed and published different macros for that purpose. For powerful analytical models it is however not enough to "replicate" the original data just in another format. You want to describe the behavior of your analysis subjects. You can to this by calculating different analytic measures.   This article focuses on “correlation analysis between individual customers and the average customer”  and shows the rationale, the coding and the interpretation of the results. There is also a link to a Youtube Recording with a presentation on this topic, which you can find below.           How similar is my customer with the average customer?   Let's have a look at a line chart with usage curves for 4 selected customers (orange, green violet, blue) and the average usage curve over all 50000 customers. You can see that some customers have a usage curve which is very similar with the average customer, e.g. the orange one, which other customers show a different pattern over time. You can describe the usage curves for example with the follow three parameters: 1) Average Level, 2) Variability 3) Similarity with the "average customer"                                   Describing the usage of the customers   The orange customer has a very similar, slightly lower LEVEL as the average customer. His VARIABILITY is over the six time periods is rather large (++). And his SIMILARITY with the average customer is very high (++). When the average of customers is rising their usage, the orange customer is also rising his usage. You can document the measures for the orange customer as shown in the first line of the following table.   When you look at the green customer, you see that his average level is almost the same as the average customer, so you can set a "=" sign. His variability is very high. His similarity with the average customer is not only low, it is negative. When the average goes up, the green customer decreases his usage and vice versa. So you can set "--" here.   Other remarkable behavior which you can see form the chart is the the violet customer has almost no variability, his usage stays at the same level over all periods.   And the blue customer has a remarkable low level with some variability and some similarity with the average customers.   You have now described the different usage of the customers using certain features. You are not looking at the original individual data any more, but on features that allow to infer behavior, study causality between factors or define segments.           Use statistical measures to describe the above mentioned features   In order to represent the features LEVEL, VARIABILITY and SIMILARITY with statistical measures, it is straightforward to use the MEAN for level and the STANDARD DEVIATION for variability. (Pearson) CORRELATION can be used to describe similarity between two vectors of measures. The CORR procedure in SAS not only allows to calculate Pearson or Spearman correlation, it also calculates the mean and the standard deviation and outputs it into a dataset. The following 3 steps show how to code the example with SAS.     Step 1 - Calculate the average usage per time interval ("Average Customer")   In the first step you calculate the average usage per time interval. The code below shows how you can do this with a SQL query. You could also use SAS procedures like the MEANS, or the SUMMARY procedure for aggregation.   proc sql; create table monthly_average as select month, mean(usage) as MonthlyAverage format = 8.2 from usage group by month order by month; quit;   Thus code generates a dataset with one entry per time interval:    Monthly Month Average 1 59.01 2 62.79 3 55.11 4 62.68 5 54.59 6 58.49     Step 2 - Join the lookup table with the average usage to the original data   In the next step, you join this (lookup) table to the original data. You need to do this because the CORR procedure which will be used to calculate the correlations per CUSTID, needs to have the two variables in columns in the same dataset.   The code uses a SQL query to join the tables. A session describing different methods in SAS to join lookup tables to a master table will be published soon in my youtube webinar playlist.    proc sql; create table usage_enh as select u.custid, u.month, u.usage, m.MonthlyAverage from usage as u, Monthly_Average as m where u.month = m.month; quit;   The resulting table looks as follows. You see that the 6 monthly average values for the months 1-6 are replicated for each CustID                                         Step 3 - Calculate the correlation between the average customer and the individual values per customer    In the next step you use the CORR procedure to calculate the (Spearman) correlation between the usage values of each customer and the monthly average of all customers.   PROC CORR DATA = usage_enh OUTS = Corr_Usage NOPRINT; BY CustID; VAR Usage; WITH MonthlyAverage; RUN; Note the following from the code: Don't forget to specify a NOPRINT statement, otherwise the printed output with correlation results for each BY group will fill up your output window or output file. Use the BY statement to calculate the correlation coefficients by CustID. Note that the dataset needs to be sorted BY CUSTID. The OUTS= option defines the dataset, where the correlation coefficients shall be written to. The "S" in "OUTS" defines that you calculate the Spearman correlation coefficients. Use OUTP to receive Pearson correlations. The 2 variables USAGE and MONTHLYAVERAGE are specified with the VAR and the WITH statement. The resulting output dataset looks as follows:   You see that the relevant statistics (mean, standard deviation and correlation) are available in the output table. You just need to re-arrange the structure to a one-row-per-subject data structure to use it for machine learning purposes.                                 Step 4 - Rearrange the data to a one-row-per-subject data structure   You can use the TRANSPOSE procedure to arrange the data with one row per subject. Use a BY statement to run the transpose per customer. The ID statement specifies with column in the input data contains the variable names in the output data The VAR statement specifies the variable that is used for the values.   proc transpose data=Corr_Usage out=customer_ABT(drop= _name_); by custid; id _type_; var usage; run; Finally you can perform some formatting on the table and rename column names and specify formats.   data customer_ABT; set customer_ABT; drop n; rename mean=Level std=Variability corr=Similarity; format mean 8.1 std 8.1 corr 8.2; run;   The resulting table shows that you have derived 3 features, LEVEL, VARIABILITY and SIMILARITY from the original transactional table. You can use this table now as addtitional input variables in supervised machine learning. Or you can use the variables to segment your customers into groups with similar behavior, e.g. AVERAGE LEVEL/LOW VARIABILITY/HIGH SIMILARITY.         Conclusion   The idea of this article is to illustrate how you can derive features from longitudinal data and use them for other data science tasks. You have seen how easy it is to code the creation of 3 powerful features with SAS code.   Be creative!   The article could also encourage you to to be creative, when it comes to using longitudinal data in your machine learning analyses. In my example the SIMILIRATY feature has been a good indicator whether a customer is close to the average customer or, for whatever reason, is behaving rather in the opposite way. This indicator can be a good predictor for product upselling or cancellation risk analyses. Your data might require other forms of aggregations or calculations - Be creative! The analytic toolbox is very large and you can find the appropriate measure for your use case.           Youtube Webinar There is a webinar on the same topic in my Data Preparation for Data Science Playlist. It explains the case as a software demo.     Links and References   There are more SAS Communities article which show examples for feature engineering:  Feature Engineering #2 - Accordance with predefined pattern Feature Engineering #3 – Describing the Trend over Time 3 ways to consider movable holidays in SAS   This example has been taken from my SAS Press book Data Preparation for Analytics Using SAS see chapter 18.3, page 184pp Further books of the author in SAS Press: Applying Data Science - Business Case Studies Using SAS Data Quality for Analytics Using SAS   "Ask-the-expert sessions" on Data Preparation for Data Science (in German): https://www.sas.com/de_at/webinars/ask-the-expert-serie-data-preparation-fuer-data-science-teil1.html https://www.sas.com/de_at/webinars/ask-the-expert-serie-data-preparation-fuer-data-science-teil2.html Slide Collection: Slides Deck #121 and #122 at https://github.com/gerhard1050/DataScience-Presentations-By-Gerhard      ... View more

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