Help using Base SAS procedures

PROC SQL: Correlated Subquery Capability Compared to SQL Server

Reply
New Contributor
Posts: 3

PROC SQL: Correlated Subquery Capability Compared to SQL Server

[ Edited ]

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;

 

Super User
Posts: 3,918

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

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.

PROC Star
Posts: 265

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

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.

Ask a Question
Discussion stats
  • 2 replies
  • 147 views
  • 2 likes
  • 3 in conversation