When you reference a libref that is a concatenation of multiple libraries, if members of the same name exist in more than one library, the first occurrence of the member is used for input and update processes. However, the SQL procedure (PROC SQL) skips a data set of that duplicated name in order to use an SQL view that is defined in a later library in the concatenation. For example, in the code that is provided on the Full Code tab of this SAS note, the DATA step uses the data set lib1.test as input while the PROC SQL step incorrectly uses the SQL view lib2.test as input.

There is no workaround for this issue.

Operating System and Release Information

   libname lib1 '      ';      /* will contain a SAS data set called test */
   libname lib2 '      ';      /* will contain an SQL view called test    */
   libname concat (lib1 lib2);

   data lib1.test;
   set sashelp.class;
   where sex='M';
   run;

   proc sql;
   create view lib2.test as
   select *
   from sashelp.class
   where sex='F';
   quit;

   data first;
   set concat.test;
   run;

   proc sql;
   create table second as
   select *
   from concat.test;
   quit;

   proc print data=first;
   title 'output will be from the SAS data set in the library lib1';
   quit;

   proc print data=second;
   title 'output will be from sql view in library lib2';
   run;

                output will be from the SAS data set in the library lib1 - sex=M               1

                        Obs    Name       Sex    Age    Height    Weight

                          1    Alfred      M      14     69.0      112.5
                          2    Henry       M      14     63.5      102.5
                          3    James       M      12     57.3       83.0
                          4    Jeffrey     M      13     62.5       84.0
                          5    John        M      12     59.0       99.5
                          6    Philip      M      16     72.0      150.0
                          7    Robert      M      12     64.8      128.0
                          8    Ronald      M      15     67.0      133.0
                          9    Thomas      M      11     57.5       85.0
                         10    William     M      15     66.5      112.0


                      output will be from sql view in library lib2 - sex=F                      2

                        Obs    Name       Sex    Age    Height    Weight

                         1     Alice       F      13     56.5       84.0
                         2     Barbara     F      14     65.3       98.0
                         3     Carol       F      14     62.8      102.5
                         4     Jane        F      12     59.8       84.5
                         5     Janet       F      15     62.5      112.5
                         6     Joyce       F      11     51.3       50.5
                         7     Judy        F      14     64.3       90.0
                         8     Louise      F      12     56.3       77.0
                         9     Mary        F      15     66.5      112.0