BookmarkSubscribeRSS Feed
EvoluZion3
Obsidian | Level 7

Hi all, we have come across this interesting feature in PROC SQL and wondered what your thoughts were. We're currently using EG5.1 and SAS9.3.

 

We are finding that an outer query can see fieldnames from deep inside nested queries, whereas a SELECT should only be able to see the results immediately inside the FROM. For all other SQL platforms I've used (MS SQL, Oracle, Access) this shouldn't be allowed, indeed they error when I replicate the same problem.

 

To see what I mean, please run the below.

 

Example 1A works (or rather errors) as I'd expect, however example 1B shows that just by aliasing the example table's fieldname (even using the same name) the outer query can see "through" the middle query into the inner query. I've tried this with a query nested four times with the same result.

 

 


/* Build an example table */

data WORK.MYDATA;
input MYID;
datalines; 
1
2
3
;




/* Example 1A - this gives a "column not found" error as expected, as the outermost SELECT cannot see "MYID", only "NEWFIELDNAME777" */
PROC SQL;
SELECT MYID FROM (
SELECT MYID AS NEWFIELDNAME777 FROM (
SELECT MYID FROM WORK.MYDATA
)
);
QUIT;




/* Example 1B - just by aliasing the fieldname in the innermost query, this now returns records */
PROC SQL;
SELECT MYID FROM (
SELECT MYID AS NEWFIELDNAME777 FROM (
SELECT MYID AS MYID FROM WORK.MYDATA
)
);
QUIT;




/* Example 2A - if the middle query aggregates the data, the aggregate is returned alongside each record (the outer query can see both the middle query and the innermost query) */
PROC SQL;
SELECT MYID, NEWFIELDNAME777 FROM (
SELECT SUM(MYID) AS NEWFIELDNAME777 FROM (
SELECT MYID AS MYID FROM WORK.MYDATA
)
);
QUIT;




/* Example 2B - if the middle query aggregates the data, but I use UNION to replicate the table, only the last record is returned, alongside the aggregate. This should return the same results as 2A */
PROC SQL;
SELECT MYID, NEWFIELDNAME777 FROM (
SELECT SUM(MYID) AS NEWFIELDNAME777 FROM (
SELECT 1 AS MYID FROM WORK.MYDATA WHERE MYID = 1 UNION
SELECT 2 AS MYID FROM WORK.MYDATA WHERE MYID = 2 UNION
SELECT 3 AS MYID FROM WORK.MYDATA WHERE MYID = 3
)
);
QUIT;
4 REPLIES 4
Quentin
Super User

Well that's surprising to me.  Seems like a bug.  You don't even need the query in the middle to reference MYID.  If you use the _tree option, you can see that the _tree that is shown when the problem occurs, is almost the same as if you just did select * from mydata.  It's as if the compiler is completely skipping the the middle query.

 

I'd suggest sending it in to tech support.

 

data mydata ;
  myid=1 ;
run ;

*errors as expected ;
proc sql _tree ;
  select myid from
    (select 0 as foo from
      (select myid from mydata)
     )
 ;
quit ;

*surprising bug (?). Should error, but returns myid from inner most query ; 
*Note the short tree ;
proc sql _tree ;
  select myid from
    (select 0 as foo from
      (select myid as myid from mydata)
     )
 ;
quit ;

*works as expected ;
proc sql _tree ;
  select * from
    (select 0 as foo from
      (select myid from mydata)
     )
 ;
quit ;

 

 

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
EvoluZion3
Obsidian | Level 7

Thanks Quentin, glad it's not just me. I've submitted a ticket and will update this thread with their response.

EvoluZion3
Obsidian | Level 7

I received this email from SAS Technical Support:

 

Thank you for your email regarding the nested SQL select queries.

 

I've just been running the code and I see what you mean.

 

I can't find any previous mention of this in our knowledge base or Defect system, so what I will do is raise this with my Global Support team as a candidate for a Defect.

 

I'll be in touch as soon as I have more information for you .

EvoluZion3
Obsidian | Level 7

This has been raised by SAS as a software fault, and will be fixed in the future. I'm surprised no-one else has hit this problem before.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 417 views
  • 2 likes
  • 2 in conversation