Tricky Base SAS Interview Questions: Part II

If you‘re interviewing for analytics and data science roles involving SAS programming, advanced Base SAS skills are essential. While SAS Enterprise Guide and Visual Analytics have become popular for their user-friendly interfaces, many large enterprises still rely heavily on Base SAS for processing huge datasets and complex data transformations.

In a previous post, we looked at some foundational Base SAS interview questions. Here we‘ll dive deeper into more advanced examples that test your ability to efficiently wrangle and analyze big data using Base SAS. We‘ll also discuss when Base SAS is preferable to other SAS tools and some best practices for leveraging its power, especially in big data environments.

Base SAS and Big Data

SAS continues to hold the largest advanced analytics market share at 35.2%, more than double the next vendor (IDC, 2020). A key reason is its entrenchment in industries like financial services, insurance, and healthcare that have some of the largest and most complex data volumes.

Base SAS remains the go-to for many of these organizations dealing with massive transactional and customer datasets, often with billions of records. Its ability to efficiently process huge flat files and integrate with databases like Teradata and Hadoop is crucial.

Some key features that make Base SAS a powerhouse for big data:

• Optimized I/O processing for reading large files
• Ability to process datasets larger than available memory
• Parallel processing and multi-threading
• SAS/ACCESS interfaces for in-database processing
• Integration with Hadoop, Spark, and other big data platforms

To illustrate, a financial services company was able to use Base SAS to optimize a credit risk model processing 3 billion rows of data, reducing runtime from 4-5 hours to under 10 minutes (Zhang, 2019).

So while SAS Enterprise Guide, Visual Analytics, and Viya get a lot of buzz, Base SAS skills remain in high demand and are often a core part of SAS coding interviews. Let‘s walk through a few more complex interview questions.

Example Interview Questions

These examples mimic real-world big data scenarios you might encounter in domains like banking, insurance, telecommunications, retail, and healthcare.

Hash Tables for Deduplication

Suppose you‘re working with web clickstream data that contains multiple records per user session. You want to deduplicate the data to the session level, keeping just the first record for each session.

The data looks like:

With potentially billions of records, efficiency is key. One approach is to use a hash table:

data deduplicated_sessions(drop=rc);
  declare hash h(dataset:‘clickstream_data‘, ordered:‘a‘);
  h.defineKey(‘session_id‘);
  h.defineData(‘user_id‘, ‘session_id‘, ‘timestamp‘, ‘page‘);
  h.defineDone();

  do while(h.do_over() = 0);
    output;
  end;
run;

The declare hash statement creates a hash object that is loaded with the full clickstream dataset. The defineKey method specifies to use session_id as the key, and defineData lists the columns to load.

The do while loop iterates through the hash object, outputting records. Since the hash table automatically deduplicates on the key column, this returns just the first record for each session_id.

This leverages the power of in-memory hash tables to efficiently deduplicate even massive datasets. For more on hash programming techniques, see Dorfman and Vyverman, 2006 and Secosky and Bloom, 2007.

Efficient Sorting with Formats

Another common scenario is needing to sort and aggregate large datasets on custom criteria. Suppose you have detailed insurance claims data with diagnosis codes and need to calculate aggregates at the higher category level.

The data looks like:

Diagnosis codes are hierarchical with the first 3 characters representing a higher-level category. So E11 codes map to a Diabetes category, I50 to Heart Failure, and J44 to COPD.

With billions of claims records, sorting and aggregating on a substring is inefficient. A better approach is to use a format to map codes to categories and then aggregate:

proc format;
  value $dgn_cat 
    ‘E10‘, ‘E11‘ = ‘Diabetes‘
    ‘I50‘ = ‘Heart Failure‘ 
    ‘J44‘ = ‘COPD‘
    other = ‘Other‘;
run;

proc summary data=claims nway;
  class diagnosis_code;
  var amount;
  format diagnosis_code $dgn_cat.;
  output out=summary sum=;
run;

The custom format maps diagnosis codes to their category. Then proc summary can aggregate directly on the formatted category values.

Judicious use of formats is a hallmark of efficient Base SAS programming on large datasets. See Bilenas, 2005 for more examples and tips.

Leveraging Databases

As data volumes grow, organizations increasingly store data in databases rather than flat files. While Base SAS can connect to and extract data from nearly any database platform, more efficient is to push processing to the database via SAS/ACCESS and SQL pass-through.

For example, suppose the insurance claims data from the previous example is stored in a Teradata database. Rather than extracting billions of records into SAS, you can aggregate in-database:

proc sql;
  connect to teradata (server=myserver database=claims);

  create table claims_summary as
  select dgn_cat, sum(amount) as total_paid
  from connection to teradata
    (
      select case when diagnosis_code like ‘E10%‘ or diagnosis_code like ‘E11%‘ then ‘Diabetes‘
                  when diagnosis_code like ‘I50%‘ then ‘Heart Failure‘
                  when diagnosis_code like ‘J44%‘ then ‘COPD‘
                  else ‘Other‘ 
             end as dgn_cat
           , sum(amount) as amount     
      from claims
      group by 1
    );

  disconnect from teradata;
quit;

The SQL pass-through code pushes the aggregation to Teradata, and only the summary records are returned to SAS. This is much more efficient than extracting billions of detail records.

SAS/ACCESS is available for all major databases including Teradata, Oracle, DB2, SQL Server, Hadoop, and more. Definitely highlight any experience optimizing SAS jobs by leveraging in-database processing.

Best Practices and Tips

Some key tips for excelling with Base SAS in big data environments:

1. Minimize data movement. Use SAS/ACCESS and SQL pass-through to process data in-database and return aggregates.

2. Maximize in-memory processing. Leverage hash tables and in-memory sorting techniques to avoid unnecessary I/O.

3. Parallelize and chunk. For data too large to fit in memory, break into smaller chunks and use parallel processing techniques like CPUCOUNT and THREAD.

4. Use formats judiciously. Formats are a highly efficient way to group, sort, and aggregate large datasets with minimal memory overhead.

5. Code with macro flexibility. Write generalized, parameterized macro code that can adapt to different input files and business rules. See Rosenbloom, 2018.

6. Benchmark alternate approaches. With big data, small inefficiencies compound, so regularly benchmark code variations to optimize. See Lafler, 2016.

SAS and the Broader Data Science Toolkit

It‘s important to note that while Base SAS remains a key tool for many enterprises, it‘s increasingly part of a broader data science toolkit. Python and R have grown in popularity, and cloud-native tools like Databricks and Snowflake are also gaining share.

However, strong Base SAS skills provide a great foundation for learning these other languages and platforms. The core programming concepts around data manipulation, SQL, and macro programming are highly transferable.

Python libraries like pandas were directly inspired by SAS data steps and SQL. Learning both will deepen your understanding of data wrangling operations. And experience with SAS macro programming concepts translates well to Python functions and classes.

Many organizations are adopting a polyglot analytics environment with a mix of SAS, Python, R, and SQL. Individuals who can easily switch between them and integrate code across platforms are highly valued.

Conclusion and Next Steps

Hopefully these examples and discussion help you feel prepared to tackle even the most complex Base SAS interview questions and position you for success in a data science career. Remember:

1. Highlight your Base SAS skills, especially around big data processing and database integration
2. Brush up on advanced techniques like hash programming, efficient sorting, and benchmarking
3. Frame your SAS experience in the broader context of tools like Python and cloud platforms
4. Practice walking through complex examples and explaining your approach

With strong Base SAS skills and the ability to integrate with other key data science tools, you‘ll be well-positioned for even the most demanding enterprise analytics and data science roles.

What other tips do you have for acing Base SAS interviews and excelling with SAS in a big data environment? I‘d love to hear about your experiences!