Automating SAS ETL Process, Part 1

Imagine you’re given an assignment from your manager to build a data pipeline that will be put into and need to be updated regularly to provide business insight. Successfully creating the pipeline, is only the first step. Your manager would like your pipeline to be run every morning at 8:00 AM for daily updates on new incoming data. You could log into the interface and manually update the pipeline every morning at 8:00 AM. However, setting up an automated pipeline saves you from busy-work, is much more efficient and, as we’ll show it does not require a complex enterprise job scheduler like Control-M or Airflow. The SAS platform gives you built-in and native tools to run your ETL flows on autopilot.

In this post, we will describe the first step in the process; creating the pipeline. In subsequent posts in this series, we’ll discuss the three different approaches for extracting, transforming, loading, and modeling our data to automate repetitive data workflows without relying on external third-party tools. Each approach automates the pipeline execution as new input data is received.

The pipeline we’ll create here utilizes a Bank Marketing data set. The data is related to direct marketing campaigns (phone calls) of a Portuguese banking institution. Three different machine learning algorithms are implemented, and the classification goal is to predict if the client will subscribe to a term deposit (Target).

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

Data Description

The banking data that we’re utilizing for this demonstration consist of 17 columns and 45,211 rows with the response variable labeled as “Target”. We start off by loading in our data

A subset of the initial variables in our banking dataset is shown above. The variables consist of the following:

Age
Job
Marital
Education
Default
Balance
Housing
Loan
Contact
Day_Of_Week
Month
Duration
Campaign
Pdays
Previous
Poutcome
Target

These variables contain key measures that we will need for the analysis; inputs and a target. First let’s discuss the “Target” variable. This variable is binary; it indicates whether a client has subscribed to a term deposit. pdays, is a candidate input variable that indicates the number of days that passed by after the client was last contacted from a previous campaign. Poutcome, is a candidate input variable that indicates the outcome of the previous marketing campaign. We need to partition our data, the banking industry uses training, validation, and a test set for analysis. It acts as a final, completely unseen data for the finished model.

Relative Importance

Let’s look at the candidate input variables, in terms of importance to our target variable. After we load our data we will open a pipeline and bring in a ‘Data Exploration node from the Miscellaneous tab.

After our pipeline has successfully run, we can right-click on the Data Exploration node to explore the results.

In the above figure, we have bar chart showing the important variables and the correlation they have to our target variable. The first input variable of highest importance was duration with a relative importance of 1. Next, we see poutcome, month, age, job, pdays, and contact relative importance values associated with subsequent listed variables are scaled to the importance of duration (1).

In the above bar graph, we have our class distribution for the ‘Target’ variable. On the left we the distribution of the number of clients that subscribed to a term deposit. On the right, we have clients that did not subscribe to a term deposit. Next, let’s look at the distributions of our candidate input variables.

The above figure shows an interval variable summary scatter plot. The variable “previously” has levels of skewness around 41 indicating extreme right-skewness and heavy tails. Performing a log transformation on previously will make its distribution less skewed and may prevent it from distorting the performance of the model. We apply the log transformation to the interval variables inside our pipeline by adding a Transformation node to our pipeline. To set up our transformation node, we head over to Data tab and select the variable ‘previous’. In the left pane of the window we change the Transform tab from ‘none’ to Log.

From the figures, we see that we have updated the metadata method to Transform the variable ‘previous’. Let’s look at the entire pipeline and discuss the machine learning nodes we decided to use for our analysis.

The above figure shows our a machine learning pipeline that will be automated in subsequent posts in this series. Note that, we handle any missing in our data by utilizing the imputation node. After handling the missing data, the Transfomation node is used to mitigate the impact of interval variables that are highly skewed, as described above. We’ll use three machine learning nodes; that will classify the the binary target;Logistic Regression, Gradient Boosting, and Decision Tree. The reason for choosing these models is first they balance risk control, speed, and accuracy. Bank’s use these models to predict loan defaults, fraud, and to meet the strict regulations in the banking world.

Let’s dive deep into the machine learning section of the pipeline. The small ribbon with a star at the bottom left of the Logistic Regression node indicates that it was chosen as the champion model for this pipeline. Let’s look at the results from the logistic regression, starting with the ROC curve report.

The above figure shows the Logistic based ROC curve for the Test, Train, and Validation datasets. It, plots Sensitivity (True Positive Rate) against 1 − Specificity (False Positive Rate). All three curves are well above the diagonal reference line, indicating that the model has strong predictive ability and performs substantially better than random guessing. The curves are also very close to one another across the three data roles, which suggests that the model is consistent and generalizes well without a major difference between training, validation, and test performance. At the marked KS Cutoff, the model achieves approximately 82–85% sensitivity while maintaining a relatively low false-positive rate of around 16%, indicating a useful balance between identifying positive cases and avoiding false positives. Next, let’s look at the model comparison node to gain some insight from the KPI’s from each of the machine learning nodes.

The Model Comparison results indicate that the Logistic Regression model provides the strongest overall predictive performance among the three models evaluated (on what partition of the data? Are these training or validation results?). Logistic Regression achieves a KS value of 0.6891, accuracy of 90.82%, and an ROC/Area Under the Curve (AUC) of 0.9154, outperforming Gradient Boosting (AUC = 0.8874) and Decision Tree (AUC = 0.8741). The higher AUC indicates that Logistic Regression does a better job of distinguishing between the two outcome classes across different classification thresholds, while the strong KS statistic shows good separation between the distributions of predicted outcomes. Although the Decision Tree has a slightly higher F1 score in this comparison, Logistic Regression provides the strongest and most consistent overall discrimination, making it a logical model to carry forward. From a modeling perspective, Logistic Regression is also a strong choice when interpretability, stability, and understanding the relationship between predictors and the probability of the target event are important. Rather than selecting a more complex algorithm simply because it can capture nonlinear patterns, the comparison shows that the simpler Logistic Regression approach is already capturing the predictive signal effectively.

Conclusion

Now we have completed the first step in our automated process; building out the analysis to capture the predictive signal effectively. This pipeline provides a natural starting point for the next phase of the project: automation In the upcoming series of posts, I will explore three approaches to pipeline automation: the developer-friendly approach using SAS Studio Scheduling, which is ideal for quickly scheduling SAS programs and flows; the administrator approach using SAS Environment Manager/Management Console, which provides centralized orchestration for multi-step workflows and dependencies; and the cloud-native approach using SAS Viya REST APIs and the SAS Viya CLI, which enables event-driven automation and integration with modern DevOps workflows. Together, the series will demonstrate how a model can move from development and evaluation into a repeatable, scheduled, and automated production pipeline.

For more information:

Find more articles from SAS Global Enablement and Learning here.