BookmarkSubscribeRSS Feed
twhite
Calcite | Level 5

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;

 

2 REPLIES 2
SASKiwi
PROC Star

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.

s_lassen
Meteorite | Level 14

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.

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1000 views
  • 2 likes
  • 3 in conversation