TLDR: Don't use PROC COPY to copy tables from SAS to a case-sensitive ODBC database.
I ran into an issue today which irked me. IMO it's a design bug in either PROC COPY or the ODBC engine, but I won't be offended if you disagree.
Assumption: If SAS is meant to treat SAS/Access tables as though they were (mostly like) SAS datasets, it should do so as consistently as possible with how it treats SAS datasets.
Also, to see the issue, your ODBC database (SQL Server in my case) needs to have a case-sensitive collation, i.e. the case of table (and column) names matter.
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
options msglevel=I;
options fullstimer;
options mprint mrecall;
options compress=binary;
options ls=max;
options nocenter;
libname tmp odbc
NOPROMPT="Driver={SQL Server Native Client 10.0};Server=YOURSERVER;Database=YOURDB;Trusted_Connection=yes;"
schema=tmp
bulkload=yes
dbcommit=100000
direct_exe=delete
preserve_names=no
;
proc datasets lib=tmp nowarn nolist;
delete class;
quit;
proc copy in=sashelp out=tmp;
select class;
run;
data _null_;
file print;
put ' ';
run;
proc sql;
connect using tmp;
select *
from connection to tmp (
SELECT * FROM tmp.class
);
quit;
data work.class;
set tmp.class;
run;
proc print data=tmp.class;
run;
proc datasets lib=tmp nowarn nolist;
delete class;
quit;
I wish either PROC COPY left the case of my table name alone, or else the under-the-cover calls to SQL Server coped with table names in a case-insensitive manner.
For example, the SAS trace shows:
SELECT * FROM tmp.class
I assume SAS is trapping this error since the table doesn't exist, but checking the error status to "do the right thing" - in this case PROC DATASETS just ignores the request to delete the table since it doesn't know it exists.
Why not:
SELECT TOP 1 *
FROM tmp.class
SELECT TOP 1 *
FROM tmp.CLASS
-- OR
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'tmp'
AND TABLE_NAME = 'class' COLLATE Latin1_General_100_CI_AI
i.e. broaden the check for the table existence.
I know the 2nd approach wouldn't be portable across supported databases. So what? Would it be that hard to bake the correct "dictionary table" query into the ODBC engine for the connected database type (assumes all supported ODBC databases support this).
The below two approaches don't have the same problem as PROC COPY.
Your thoughts?
proc datasets lib=tmp nowarn nolist;
delete class;
quit;
proc append data=sashelp.class base=tmp.class;
run;
data _null_;
file print;
put ' ';
run;
proc sql;
connect using tmp;
select *
from connection to tmp (
SELECT * FROM tmp.class
);
quit;
proc datasets lib=tmp nowarn nolist;
delete class;
quit;
data tmp.ClAsS;
set sashelp.class;
run;
proc sql;
connect using tmp;
select *
from connection to tmp (
SELECT * FROM tmp.ClAsS
);
quit;
proc datasets lib=tmp nowarn nolist;
delete class;
quit;
Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
