Скачать или смотреть How to Write proc sql without window functions in SAS

Discover how to simulate sum window functions in SAS using `proc sql` and alternative SAS methods, including practical examples and step-by-step instructions for efficient data analysis.
---
This video is based on the question https://stackoverflow.com/q/70823592/ asked by the user 'christinelly' ( https://stackoverflow.com/u/9084892/ ) and on the answer https://stackoverflow.com/a/70823672/ provided by the user 'Stu Sztukowski' ( https://stackoverflow.com/u/5342700/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to write proc sql without windowfunction over partition by sum?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Write proc sql without window functions in SAS

As a beginner in SAS, you might find yourself in a situation where you want to use SQL-like commands within proc sql. One common requirement is simulating sum calculations with a window function, which you may be familiar with in standard SQL using OVER(PARTITION BY ...). Unfortunately, proc sql does not support window functions directly. This post will explore how you can achieve similar results in SAS using sub-queries and creative data manipulation techniques.

Understanding the Problem

To illustrate, let’s say you have a dataset of customers and your goal is to calculate the number of customers by active status and store_id, along with a sum of customers relevant to each store_id. In SQL, you might write a query that looks like this:

[[See Video to Reveal this Text or Code Snippet]]

However, since proc sql does not support window functions, you need an alternative approach to produce the same output.

Solution Overview

You can replicate the required functionality in proc sql with two primary methods:

Sub-query Method - Using sub-queries to perform aggregates and joins.

proc means Method - Utilizing the SAS procedure to compute summary statistics and combining results.

Method 1: Sub-query Method

To accomplish this using sub-queries, follow these steps:

[[See Video to Reveal this Text or Code Snippet]]

Explanation:

The first sub-query (t1) counts customers grouped by active and store_id, providing the desired individual counts.

The second sub-query (t2) counts the total customers per store_id.

A LEFT JOIN merges these two datasets on store_id, effectively simulating the desired window function behavior.

Method 2: Using proc means

For a more SAS-oriented approach, proc means can be employed to summarize data efficiently. Here's how it's done:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of Steps:

The proc means calculates totals for different groupings defined by the class statement.

The merge step combines the summary results where TYPE = 3 (which corresponds to combinations of active and store_id) with totals specific to store_id.

SQL Alternative with proc sql

If you prefer a standard SQL structure after using proc means, you can merge the aggregated results back into a new table:

[[See Video to Reveal this Text or Code Snippet]]

Understanding the TYPE Variable:

The TYPE variable helps identify what kind of summary statistic you’re working with, allowing merges based on customized grouping.

Conclusion

The absence of window functions in proc sql may seem restrictive initially, but with clever use of sub-queries and statistical procedures like proc means, you can still perform powerful data analysis in SAS. This technique not only enhances your understanding of data manipulation in SAS but also showcases the flexibility available across its various procedures.

The next time you're faced with a data aggregation challenge in SAS, remember that there are numerous pathways to arrive at your desired result. Happy coding!