SQL generation on a concatenated library error

[DennisLangley]

I've been getting a strange error when trying to pull in some variables while referencing a concatenated library. I'll try to provide a working example in describing the issue. 

My office has some standard libraries where we set, e.g., 

libname sqllib1  [library info]

libname sqllib2  [library info]

libname sqllib (sqllib1 sqllib2)

Later in our code, we simply reference sqllib. Sometimes, though, I'll get the following error when using proc sql to create a table from sqllib: 

ERROR: SQL generation can not be performed on a concatenated library. In-database processing will be skipped.

In the most recent example, it occurs when I try to bring in 

put(district, z2.) 

instead of 

district

What's strange is that, despite the error, it will still create the table I need. I can 'fix' this error by simply using sqllib1. So if I do 

proc sql; create table foo1 as select put(district, z2.) from sqllib.data; quit;

proc sql; create table foo2 as select put(district, z2.) from sqllib1.data; quit;

foo1 and foo2 both get created and are identical, but foo1 will give me this error. So what's going on here? If it's actually an error, shouldn't SAS not create the foo1 table? "data" isn't found in sqllib2 either, so SAS isn't getting 'confused' about where to pull things from. It's an easy issue to circumvent, but the error itself has me mystified. 

If I need to provide more information please let me know!

[Tom]

You left out some key information in your description of the two librefs that you are trying to concatenate.

From the message if looks like those two librefs are pointing to an external database (or two of them), like ORACLE, TERADATA, etc.

SAS will normally try to push execution into the database to reduce the amount of data that needs to move from the external database to SAS.  To do this well it needs to know what type of database it is connected to and how to translate SAS syntax into the syntax of that database.  In particular for any non-vanilla code, like the PUT() function.

It appears that by using the concatenated library you have made it too hard for SAS to make this conversion.  How does the code generation step know which of the two (or more) databases the particular table being queries lives in?

[DennisLangley]

In this case, the "data" table only lives in sqllib2. The two sqllib libraries contain entirely separate tables; there are no tables in both libraries.

If it helps, sqllib1 and sqllib2 point to two different schemas in the same catalog. (I'm not entirely clear on the finer points of data warehouse architecture but this is my current understanding).