PROC SQL and Window Functions

[stuart753]

Hi,

I'm new to SAS and am interested in exploring PROC SQL capabilities because I have some experience working with SQL.  I'm using Base SAS 9.4.

I've been through the PROC SQL examples in the SAS documentation, however I didn't see anything relating to Window functions.  From what I understood in the documentation, SAS SQL is ANSI compliant.  (If I'm running SQL on a SAS dataset versus connecting to some other database provider.)

I created the following simple query to test a window aggregate function:

proc sql;

   select model, make, msrp,
     SUM(MSRP) OVER() AS SumOfMSRP
     from sashelp.cars;

quit;

However this isn't working.  The error highlights the OVER clause in my window aggregate function as the problem.  (For the most part, SAS appears to be expecting a comparison operator.

Has anyone had any success with using SQL Window Functions in PROC SQL when querying SAS datasets?

Any insight is appreciated!

Stuart

[Reeza]

The opposite is true, see the documentation excerpt below.

Typically if you need WINDOW type functions a data step in SAS is more appropriate.

If you have a database, not SAS data set, you can use pass through SQL and pass native SQL code.

SAS 9.4 documentation:

SAS(R) 9.4 SQL Procedure User's Guide

PROC SQL and the ANSI Standard

Compliance

PROC SQL follows most of the guidelines set by the American National Standards Institute (ANSI) in its implementation of SQL. However, it is not fully compliant with the current ANSI standard for SQL. (footnote1)

The SQL research project at SAS has focused primarily on the expressive power of SQL as a query language. Consequently, some of the database features of SQL have not yet been implemented in PROC SQL.

[Patrick · Accepted solution]

There is now also PROC FedSQL which is core ANSI compliant SAS(R) 9.4 FedSQL Language Reference, Third Edition - but this doesn't mean everything has been implemented and there are still no WINDOW type functions (you can always use them directly with native data base SQL using explicit pass-through).

PROC FedSQL is rather new and not commonly used (yet?) so it's may be better you get first familiar with PROC SQL SAS(R) 9.4 SQL Procedure User's Guide   

When working with data bases make sure to also use these manuals SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition , for example when it comes to the list of functions which SAS can pass down to the data base for execution SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition .

[Reeza]

The equivalent to keep in mind for SAS is BY group processing, available in data steps and many procs.

Though it does typically require a sorted data set.

[Tom]

SAS does not support those functions. But it can do your particular example because it will re-merge summary statistics back onto detail records.

proc sql;

   select model, make, msrp,
     SUM(MSRP) /* OVER() */ AS SumOfMSRP
     from sashelp.cars;

quit;

[stuart753]

Reeza, Patrick and Tom, Thank you all for your replies.  As I begin to learn SAS, it's very helpful to get the perspectives of experts!

I've been going through the SAS training course called 'SAS Programming 1: Essentials' and I know from that content there are other ways to achieve SQL Window function capabilities within a Data Step.  My question arose more from me trying to avoid having to learn new material - I was trying to stick with what is familiar to me. 

Reeza - I went through the SAS documentation once more on PROC SQL and I still don't see anything pertaining to the lack of Windows function capability.  On my original research I did see the statement that not all database features of SQL have been implemented in PROC SQL, however I didn't see anything specific to Window functions. Perhaps this is called out specifically somewhere else and I missed it?  Or perhaps this is a situation where it's up to the user to find out which standard ANSI capabilities have yet to be implemented by SAS?

Patrick - I'll check out the other documentation sources you've suggested.  I'll probably also take your advice and try to get more familiar with PROC SQL before exploring PROC FedSQL.

Tom - That's a very helpful tip, I did not realize that could be done.  Do you know if there's anyway to create the summary statistic taking the SUM of Make instead of the entire data set?  My sample code below accomplishes this, but I'm wondering if this can be done in PROC SQL.  (I'm new to SAS so I welcome any improvement suggestions for my code below.  It seems like I have a lot of steps to accomplish something rather simple.)

title;
data work.test (keep = model make type msrp);
set sashelp.cars;
run;

proc sort data=work.test;
by make type;
run;

proc means data=work.test sum NOPRINT;
var msrp;
class make;
output out=work.testAgg sum=SumOfMSRP;
run;

data work.testAgg;
set work.testAgg;
if _TYPE_=1;
run;

data work.testReport;
merge work.test work.testAgg;
by make;
drop _TYPE_;
run;

title 'My Test Report';
proc print data=work.testReport;
run;

Thank you for your help.

Stuart

[Reeza]

Unlike standard SQL, in SAS you can have more variables than in the group statement when summarizing, though this does give you a warning in the log.

This SQL is the equivalent of your code:

proc sql;

create table want as

select make, model, type, msrp, sum(msrp) as sumMSRP format=dollar12., count(*) as FREQ

from sashelp.cars

group by make;

quit;

[stuart753]

Reeza,

Wow... I wouldn't have expected that result.  That certainly simplifies things from what I posted and I'll have to remember this exception when writing SQL against SAS datasets.

Thank you for posting this very helpful approach!!

Stuart

[Tom]

It is extremely painful when having to write SQL for other system that do not do allow the inclusion of columns that are not grouping or aggregate columns.

PROC SQL mainly conforms to SQL-92 - Wikipedia, the free encyclopedia. Window functions were not added to SQL standards until SQL:2003 - Wikipedia, the free encyclope.

[Tom]

That is what GROUP BY is for.

select make,sum(msrp) as sum_msrp

from sashelp.cars

group by make

;

[stuart753]

Tom,

The way you've used GROUP BY is in line with ANSI SQL Standard because you're aggregating msrp and your other variable, make, is included in both the SELECT and GROUP BY.

What Reeza posted is not standard SQL because model, type and msrp are not contained in either an aggregate function or the GROUP BY clause.  However what she posted works on a SAS dataset (with a warning in the log as she stated).  This appears to be some hybrid functionality SAS added for PROC SQL on SAS datasets??  This would not work on a pass-through.

I can see where this would get confusing...

Stuart

[Tom]

The SAS solution pre-dates WINDOW function in SQL standards. It is less powerful than WINDOW functions, but much easier to code.