Combining Datasets in SAS: An AI/ML Perspective

ByJordan Brown

As an artificial intelligence and machine learning expert, I can attest that one of the most critical—yet often overlooked—aspects of any AI/ML project is data preparation. And a key part of data prep is combining datasets from disparate sources into a unified, analysis-ready format.

In fact, a 2020 survey by Anaconda found that data scientists spend a whopping 45% of their time on data preparation and data cleansing tasks. That‘s more time than they spend on model training, model selection, and deployment combined!

Why does data prep take up so much time? One reason is that real-world data is messy. It comes in different formats, contains errors and inconsistencies, and is spread across multiple files or databases. Wrangling all that raw data into a clean, combined dataset that‘s suitable for analysis is a non-trivial task.

But it‘s a necessary one. The quality of your input data directly impacts the quality of your AI/ML model outputs. Garbage in, garbage out, as they say.

That‘s where SAS comes in. As a leader in the world of data analytics and statistical computing, SAS provides powerful tools for data transformation and manipulation. And when it comes to combining datasets, few tools are as essential as the APPEND procedure.

Combining Datasets with PROC APPEND

The APPEND procedure in SAS allows you to vertically combine two or more datasets by stacking the observations from one dataset on top of another. It‘s the go-to method when you have multiple datasets with the same structure (i.e., the same variables in the same order) that you want to concatenate into a single, larger dataset.

The basic syntax for PROC APPEND is straightforward:

PROC APPEND BASE=dataset1 DATA=dataset2; 
RUN;

Here, dataset1 is the base dataset that you want to append to, and dataset2 is the dataset being appended. After running this code, dataset1 will contain all the original observations plus the observations from dataset2 stacked on bottom.

Let‘s look at a concrete example. Suppose you‘re working on a machine learning project to predict customer churn for a subscription-based software service. You have two datasets, churn_data_2022 and churn_data_2023, containing customer information and churn status for the past two years. Each dataset has the following variables:

  • customer_id: unique identifier for each customer
  • age: customer age
  • tenure: number of months the customer has been subscribed
  • total_spend: total dollar amount the customer has spent on the service
  • churn_status: 1 if the customer churned (cancelled their subscription), 0 otherwise

To combine the datasets for analysis, you could use PROC APPEND like so:

PROC APPEND BASE=churn_data_2022 DATA=churn_data_2023;
RUN;

After running this code, churn_data_2022 would contain customer data from both 2022 and 2023, with the 2023 observations appended to the end of the 2022 data.

Why Combining Datasets Matters for AI/ML

You might be wondering: why bother combining the datasets at all? Can‘t we just train separate models on each year‘s data?

Well, we could. But there are several reasons why combining the data is often preferable from a machine learning perspective:

  1. More data = better models. In general, the more data you have to train your model on, the better it will perform. By combining datasets from multiple years (or other sources), you increase the size of your training set, which can lead to more accurate and generalizable models.

  2. Capturing temporal trends. Customer behavior can change over time. By combining data from different time periods, you allow your model to learn and adapt to temporal trends, which can improve its predictive power.

  3. Data augmentation. Combining datasets is a form of data augmentation, a technique used to artificially increase the size of the training set by creating modified versions of the existing data. Data augmentation is commonly used in deep learning to improve model robustness and reduce overfitting.

  4. Transfer learning. In some cases, you may have a model that was trained on one dataset (e.g., churn data from a different company or industry) that you want to fine-tune on your own data. Combining your data with the pre-trained model‘s data allows you to leverage the knowledge learned from the original dataset while adapting the model to your specific use case.

Integrating SAS with Open Source AI/ML Tools

While SAS is a powerful tool for data manipulation and statistical analysis, many data scientists today prefer to use open source languages like Python and R for machine learning tasks. Fortunately, SAS integrates seamlessly with these tools, allowing you to combine the best of both worlds.

For example, you can use PROC APPEND in SAS to combine your datasets, then use the PROC EXPORT statement to save the combined data as a CSV file:

PROC EXPORT DATA=churn_data_combined 
            OUTFILE=‘churn_data.csv‘ 
            DBMS=CSV 
            REPLACE;
RUN;

You can then load the CSV file into Python using the pandas library:

import pandas as pd

churn_data = pd.read_csv(‘churn_data.csv‘)

From there, you can use popular Python libraries like scikit-learn, TensorFlow, or PyTorch to build and train your machine learning models.

Alternatively, you can use the PROC PYTHON statement in SAS to embed Python code directly in your SAS program:

PROC PYTHON;
import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.ensemble import RandomForestClassifier

churn_data = pd.read_csv(‘churn_data.csv‘)

X = churn_data[[‘age‘, ‘tenure‘, ‘total_spend‘]]
y = churn_data[‘churn_status‘]

X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3)

clf = RandomForestClassifier()
clf.fit(X_train, y_train)

print(clf.score(X_test, y_test))
QUIT;

This code reads the combined churn data into a pandas DataFrame, splits it into training and test sets, trains a random forest classifier on the data, and prints the model‘s accuracy score.

The ability to move fluidly between SAS and open source tools is a major advantage for data scientists and machine learning practitioners. It allows you to leverage the strengths of each tool at different stages of the data pipeline.

Advanced Techniques: Hash Tables

While PROC APPEND is a versatile tool for combining small-to-medium sized datasets, it can become computationally inefficient for very large datasets. In these cases, using a hash table can be a more efficient alternative.

A hash table is a data structure that allows for fast insertion, deletion, and lookup of key-value pairs. In SAS, you can use the DECLARE HASH statement to create an in-memory hash table, then use the REF function to add data to the table.

Here‘s an example of how you could use a hash table to combine two large datasets:

data combined_data;
   if _N_ = 1 then do;
      declare hash h(dataset: "dataset1");
      h.defineKey("customer_id");
      h.defineData(all: "yes");
      h.defineDone();
   end;

   set dataset2;
   if h.find() = 0 then output;
run;

In this code, we first create a hash table h that contains all the data from dataset1, with customer_id as the key variable. We then loop through the observations in dataset2, using the find() method to check if each customer_id exists in the hash table. If a match is found, the observation is output to the combined_data dataset.

Using a hash table in this way can be much faster than using PROC APPEND, especially for datasets with millions of observations. In a 2010 paper published in the SAS Global Forum, Loren Becker and Diane Olson demonstrated that using a hash table to combine two datasets with 1 million observations each was over 90 times faster than using PROC APPEND.

Of course, the specific performance gains will depend on factors like the size and structure of your datasets, available system memory, and disk I/O speed. As with any performance-critical code, it‘s important to benchmark different approaches on your own data to determine the best method.

Dataset Combining in Cross-Validation

Another important consideration when combining datasets for machine learning is how the combined data will be used in model training and evaluation. A common pitfall is to combine the datasets first, then split the combined data into training and test sets. This can lead to data leakage and overly optimistic performance estimates, as observations from the same original dataset may end up in both the training and test sets.

A better approach is to use cross-validation, where the data is split into multiple subsets (e.g., 5-fold or 10-fold cross-validation), and the model is trained and evaluated on each subset in turn. When combining datasets for cross-validation, it‘s important to stratify the split by the original dataset to ensure that observations from each dataset are represented in each fold.

In SAS, you can use the SURVEYSELECT procedure to perform stratified sampling for cross-validation:

proc surveyselect data=combined_data 
                  method=srs 
                  samprate=0.2 
                  out=test 
                  outall;
   strata dataset;
run;

This code uses simple random sampling (method=srs) to select 20% of the observations from combined_data for the test set, stratified by the dataset variable. The outall option outputs all observations, with a new variable Selected indicating whether each observation was selected for the test set (1) or not (0).

You can then use the Selected variable to create training and test sets for each fold of cross-validation:

%macro cv_split(k);
   %do i = 1 %to &k;
      data train_&i test_&i;
         set combined_data;
         if Selected = 1 then output test_&i;
         else if &i = int(5*ranuni(123)) then output train_&i;
      run;
   %end;
%mend;

%cv_split(5);

This macro creates 5 pairs of training and test datasets (train_1 and test_1, train_2 and test_2, etc.), where each test set contains 20% of the observations (stratified by original dataset), and each training set contains a random 80% sample of the remaining observations.

By using stratified cross-validation in this way, you ensure that your performance estimates are unbiased and that your model generalizes well to new data from each of the original datasets.

Conclusion

Combining datasets is a fundamental skill for any data scientist or machine learning practitioner. Whether you‘re working with customer churn data, sensor readings from IoT devices, or medical records from different hospitals, the ability to efficiently and accurately combine data from multiple sources is essential for building robust, high-performing models.

SAS provides a variety of tools for combining datasets, from the simple and intuitive PROC APPEND to more advanced techniques like hash tables and stratified sampling. By leveraging these tools—and integrating them with open source libraries like pandas, scikit-learn, and TensorFlow—you can create powerful, end-to-end machine learning pipelines that transform raw, disparate data into valuable insights and predictions.

Of course, combining datasets is just one part of the larger data preparation process. Data scientists must also deal with missing values, outliers, feature scaling, and a host of other issues before their data is ready for modeling. But by mastering the art of dataset combining, you‘ll be well on your way to tackling these challenges and unlocking the full potential of your data.

So the next time you‘re faced with a pile of messy, disjointed datasets, don‘t despair. Fire up SAS, break out PROC APPEND, and start combining! Your models (and your stakeholders) will thank you.

References

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

You May Like to Read,

Similar Posts