PROC SQL: Correlated Subquery Capability Compared to SQL Server

twhite — Thu, 14 Dec 2017 19:07:48 GMT

Hi folks,

Quick question about correlated subqueries in PROC SQL. I am curious whether a specific functionality that exists in Microsoft's SQL Server's T-SQL also exists in PROC SQL.

Please see queries below. Query #1, involving a simple join, works the same way in both PROC SQL and SQL Server's T-SQL. Query #2, though, which uses a correlated subquery in the SELECT clause , works fine in SQL Server's T-SQL but not in PROC SQL. Would you know whether this type of correlated subquery (i.e., one that is embedded within the SELECT clause) is possible in PROC SQL? If so, what might the syntax look like?

Thanks in advance for your help. And please, I already know this is a non-standard thing to do and is highly inefficient. I'm just curious whether the functionality exists in PROC SQL. There are situations where it is very useful. Thanks again!

PROC SQL;

    *---- create some play data -------------------------------------*;
    CREATE TABLE table1 (ID INT, FirstName CHAR(12));
    INSERT INTO table1 (ID, FirstName) VALUES (1, "Tom" );
    INSERT INTO table1 (ID, FirstName) VALUES (2, "Richard" );
    INSERT INTO table1 (ID, FirstName) VALUES (3, "Harry");

    CREATE TABLE table2 (ID INT, City CHAR(12));
    INSERT INTO table2 (ID, City) VALUES (1, "Tampa");
    INSERT INTO table2 (ID, City) VALUES (2, "Boston");
    INSERT INTO table2 (ID, City) VALUES (3, "Miami" );
    *----------------------------------------------------------------*;

    *----------------------------------------------------------------*;
    *Query #1 -- this works the same in both PROC SQL and SQL Server's T-SQL;    
    SELECT    table1.ID,
              table1.FirstName,
              table2.City AS HomeTown

    FROM      table1
    LEFT JOIN table2 ON table1.ID=table2.ID;
    *----------------------------------------------------------------*;
    *Query #2 -- This works in SQL Server's T-SQL but not in PROC SQL;
    SELECT    table1.ID,
              table1.FirstName,
              HomeTown = (SELECT City FROM table2 WHERE table2.ID=table1.ID)

    FROM      table1;
    *----------------------------------------------------------------*; 
QUIT;

Re: PROC SQL: Correlated Subquery Capability Compared to SQL Server

SASKiwi — Sat, 16 Dec 2017 08:46:55 GMT

In SAS SQL you can do something similar using the UPDATE statement:

http://support.sas.com/kb/25/216.html

The SQL Server example looks like an enhancement that is not ANSI standard, whereas the SAS example is.

Re: PROC SQL: Correlated Subquery Capability Compared to SQL Server

s_lassen — Sat, 16 Dec 2017 11:09:41 GMT

That's right, in SAS SQL you have to do it as

    SELECT    table1.ID,
              table1.FirstName,
              (SELECT City FROM table2 WHERE table2.ID=table1.ID) as Hometown
    FROM      table1;

which I think is more correct SQL anyway.

topic PROC SQL: Correlated Subquery Capability Compared to SQL Server in SAS Procedures

PROC SQL: Correlated Subquery Capability Compared to SQL Server

Re: PROC SQL: Correlated Subquery Capability Compared to SQL Server

Re: PROC SQL: Correlated Subquery Capability Compared to SQL Server