proc Sql generating tables in a new WC000001 library/schema

[qaguy1982]

This community has usually helped me by searching through past posts but I now have a question for which I cannot find any posts.

I am working in BASE SAS with version SAS 9.4M5.

This problem started with a more complex proc sql but I have refined it down to the minimum necessary to demonstrate the issue.

I am running a proc sql command to create a table and instead of posting the table in the WORK library as normal default, SAS created a new library called WC000001 and posted the results there.  However, none of the logs let me know that was happening.

Here is a simple sample that demonstrates the issue using the SASHELP library as you probably have this available:

proc sql;
create table "results_some_stuff" as
(select * from sashelp.class where sex = 'M' );
quit;

The resulting log looks like this:

NOTE: This SAS session is using a registry in WORK. All changes will be lost at the end of this
session.
10 proc sql;
11 create table "results_some_stuff" as
12 (select * from sashelp.class where sex = 'M' );
NOTE: Table results_some_stuff created, with 10 rows and 5 columns.

13 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

NOTE: This SAS session is using a registry in WORK. All changes will be lost at the end of this
session.

However when I look for the dataset/table  work.results_some_stuff  it doesn't exist in my WORK library but is instead found in a new library WC000001.   

I have determined that what is causing this seems to be the enclosing double quotes on the table name.  

If I take the double quotes off, the table does get created in the WORK library.

14 proc sql;
15 create table results_some_stuff as
16 (select * from sashelp.class where sex = 'M' );
NOTE: Table WORK.RESULTS_SOME_STUFF created, with 10 rows and 5 columns.

17 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

The reason I put double quotes around the table name was that I wanted to make sure that the macro variables I was using in my original example were correctly resolved.  And I was not aware of this side effect I did not expect the result I got.

There are no notes or warnings complaining about the double quotes so I am wondering, is there some documentation on this behavior?  Is it to be expected or is it some kind of defect?

I do note that the double quoted name shows the file name as results_some_stuff but the non-quoted version states the name as WORK.RESULTS_SOME_STUFF.    I always thought that if you don't prefix with a library name, the default is to create the table in WORK which is considered a temporary location.

Any advice is appeciated.

Carl Richardson

[ballardw]

Interesting.

I am one of those SAS users that actually use the USER library for single level names.

When I run

proc sql;
create table "results_some_stuff" as
(select * from sashelp.class where sex = 'M' );
quit;

instead of going to the USER library the set is created in the WORK library.

Unless you are using name literals such as "Not normal set name"n for data sets or libraries you should not use quotes around library or data set names even if they contain macro variables.

[maggiem_sas]

When you use quotation marks around a data set name, SAS thinks you are specifying a physical name (path name). If you omit the full path and just use the data set name, then SAS thinks you want to store the file wherever SAS is running. That location depends on the SAS interface and your deployment. Take a look at the SAS Companion book for your OS. For example: the UNIX topic about path names.

You could specify Work.tablename if you want to be sure a table is stored in Work.

The Macro Language: Reference contains information about quoting.