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;
... View more