topic Re: Is this a design bug with PROC COPY and/or the ODBC engine? in SAS Programming

Is this a design bug with PROC COPY and/or the ODBC engine?

ScottBass — Thu, 19 Sep 2019 05:21:43 GMT

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  /* change to dbo if you wish */ 
   bulkload=yes 
   dbcommit=100000 
   direct_exe=delete 
   preserve_names=no
;

* drop previous tables ;
proc datasets lib=tmp nowarn nolist;
   delete class;
quit;

* PROC COPY irritatingly capitalizes the selected table. ;
* Why?  SAS doesn't require a capitalized dataset name. ;
* Why can't it just leave my table name alone? ;
proc copy in=sashelp out=tmp;
   select class;
run;

* clear output (I'm using EG) ;
data _null_;
   file print;
   put ' ';
run;

* explicit passthrough fails since tmp.CLASS exists, not tmp.class ;
proc sql;
   connect using tmp;
   select *
   from connection to tmp (
SELECT * FROM tmp.class
   );
quit;

* implicit passthrough also fails ;
data work.class;
   set tmp.class;
run;

* as does this ;
proc print data=tmp.class;
run;

* and PROC DATASETS doesn't even know that tmp.CLASS exists ;
proc datasets lib=tmp nowarn nolist;
   delete class;
/*   delete CLASS;*/  * uncomment to clean up ;
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?

* drop previous tables ;
proc datasets lib=tmp nowarn nolist;
   delete class;
quit;

* PROC APPEND works.  Thanks for creating the output table as I've typed it ;
proc append data=sashelp.class base=tmp.class;
run;

* clear output (I'm using EG) ;
data _null_;
   file print;
   put ' ';
run;

* this works ;
proc sql;
   connect using tmp;
   select *
   from connection to tmp (
SELECT * FROM tmp.class
   );
quit;

* and this works ;
proc datasets lib=tmp nowarn nolist;
   delete class;
quit;

* and the data step works ;
data tmp.ClAsS;
   set sashelp.class;
run;

* this works ;
proc sql;
   connect using tmp;
   select *
   from connection to tmp (
SELECT * FROM tmp.ClAsS
   );
quit;

* but this doesn't work.  Should it??? ;
proc datasets lib=tmp nowarn nolist;
   delete class;
/*   delete ClAsS;*/
quit;

Re: Is this a design bug with PROC COPY and/or the ODBC engine?

JosvanderVelden — Thu, 19 Sep 2019 09:18:50 GMT

The PRESERVE_TAB_NAMES= and PRESERVE_COL_NAMES= options determine how this interface handles case sensitivity, spaces, and special characters. See: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=acreldb&docsetTarget=p1edsaol4dh5uyn1iayu071hqomf.htm&locale=en

Re: Is this a design bug with PROC COPY and/or the ODBC engine?

ScottBass — Fri, 20 Sep 2019 01:45:51 GMT

@JosvanderVelden wrote:
The PRESERVE_TAB_NAMES= and PRESERVE_COL_NAMES= options determine how this interface handles case sensitivity, spaces, and special characters. See: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=acreldb&docsetTarget=p1edsaol4dh5uyn1iayu071hqomf.htm&locale=en

Hi @JosvanderVelden

I submitted this:

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=SVDCMHDVSDWSQD1;Database=RLDXHosp;Trusted_Connection=yes;"
   schema=tmp  /* change to dbo if you wish */ 
   bulkload=yes 
   dbcommit=100000 
   direct_exe=delete 
   preserve_names=yes
;

* drop previous tables ;
proc datasets lib=tmp nowarn nolist;
   delete class;
quit;

* PROC COPY irritatingly capitalizes the selected table. ;
* Why?  SAS doesn't require a capitalized dataset name. ;
* Why can't it just leave my table name alone? ;
proc copy in=sashelp out=tmp;
   select class;
run;

* clear output (I'm using EG) ;
data _null_;
   file print;
   put ' ';
run;

* explicit passthrough fails since tmp.CLASS exists, not tmp.class ;
proc sql;
   connect using tmp;
   select *
   from connection to tmp (
SELECT * FROM tmp.class
   );
quit;

* implicit passthrough also fails ;
data work.class;
   set tmp.class;
run;

* as does this ;
proc print data=tmp.class;
run;

* and PROC DATASETS doesn't even know that tmp.CLASS exists ;
proc datasets lib=tmp nowarn nolist;
   delete class;
/*   delete CLASS;  * uncomment to clean up ;*/
quit;

And here is the log:

27         options sastrace=',,,d' sastraceloc=saslog nostsuffix;
28         options msglevel=I;
29         options fullstimer;
30         options mprint mrecall;
31         options compress=binary;
32         options ls=max;
33         options nocenter;
34         
35         libname tmp odbc
36            NOPROMPT="Driver={SQL Server Native Client 10.0};Server=SVDCMHDVSDWSQD1;Database=RLDXHosp;Trusted_Connection=yes;"
37            schema=tmp  /* change to dbo if you wish */
ODBC: AUTOCOMMIT is NO for connection 1
38            bulkload=yes
39            dbcommit=100000
40            direct_exe=delete
41            preserve_names=yes
42         ;
NOTE: Libref TMP was successfully assigned as follows: 
      Engine:        ODBC 
      Physical Name: 
2 The SAS System                                                                                                                                                                                                                09:15 Friday, September 20, 2019

43         
44         * drop previous tables ;
ODBC: AUTOCOMMIT is NO for connection 2
ODBC: Called SQLTables with schema of tmp
45         proc datasets lib=tmp nowarn nolist;
46            delete class;
47         quit;

NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.03 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              89.43k
      OS Memory           12836.00k
      Timestamp           20/09/2019 11:36:17 AM
      

48         
49         * PROC COPY irritatingly capitalizes the selected table. ;
50         * Why?  SAS doesn't require a capitalized dataset name. ;
51         * Why can't it just leave my table name alone? ;
52         proc copy in=sashelp out=tmp;
53            select class;
54         run;

NOTE: Copying SASHELP.CLASS to TMP.CLASS (memtype=DATA).
 
ODBC_22: Prepared: on connection 2
SELECT * FROM "tmp"."CLASS" WHERE 0=1 
 
ODBC: AUTOCOMMIT is NO for connection 3
INFO: Data set block I/O cannot be used because:
INFO:   -  The data sets use different engines, have different variables or have attributes that may differ.
 
ODBC_23: Executed: on connection 3
CREATE TABLE "tmp"."CLASS" ("Name" varchar(8),"Sex" varchar(1),"Age" float,"Height" float,"Weight" float)
 
ODBC: COMMIT performed on connection 3.
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set TMP.CLASS has 19 observations and 5 variables.
NOTE: PROCEDURE COPY used (Total process time):
      real time           0.09 seconds
      user cpu time       0.00 seconds
      system cpu time     0.01 seconds
      memory              317.71k
      OS Memory           12836.00k
      Timestamp           20/09/2019 11:36:17 AM
      
3 The SAS System                                                                                                                                                                                                                09:15 Friday, September 20, 2019


55         
56         * clear output (I'm using EG) ;
57         data _null_;
58            file print;
59            put ' ';
60         run;

NOTE: 0 lines were written to file PRINT.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              279.31k
      OS Memory           12836.00k
      Timestamp           20/09/2019 11:36:17 AM
      

61         
62         * explicit passthrough fails since tmp.CLASS exists, not tmp.class ;
63         proc sql;
64            connect using tmp;
ODBC: AUTOCOMMIT is YES for connection 3
65            select *
66            from connection to tmp (
67         SELECT * FROM tmp.class
68            );
 
ODBC_24: Prepared: on connection 3
SELECT * FROM tmp.class
 
ODBC: ROLLBACK performed on connection 3.
ERROR: CLI describe error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'tmp.class'. : [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
69         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              125.18k
      OS Memory           12836.00k
      Timestamp           20/09/2019 11:36:17 AM
      
70         
71         * implicit passthrough also fails ;


4 The SAS System                                                                                                                                                                                                                09:15 Friday, September 20, 2019

ODBC: AUTOCOMMIT is NO for connection 3
 
ODBC_25: Prepared: on connection 3
SELECT * FROM "tmp"."class"
 
ODBC: ROLLBACK performed on connection 3.
72         data work.class;
73            set tmp.class;
ERROR: File TMP.class.DATA does not exist.
74         run;

NOTE: Compression was disabled for data set WORK.CLASS because compression overhead would increase the size of the data set.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.CLASS may be incomplete.  When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.CLASS was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              169.53k
      OS Memory           12836.00k
      Timestamp           20/09/2019 11:36:17 AM
      

75         
76         * as does this ;
ODBC: AUTOCOMMIT is NO for connection 3
 
ODBC_26: Prepared: on connection 3
SELECT * FROM "tmp"."class"
 
ODBC: ROLLBACK performed on connection 3.
77         proc print data=tmp.class;
ERROR: File TMP.class.DATA does not exist.
78         run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.03 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              101.87k
      OS Memory           12836.00k
      Timestamp           20/09/2019 11:36:17 AM
      
79         
80         * and PROC DATASETS doesn't even know that tmp.CLASS exists ;

5 The SAS System                                                                                                                                                                                                                09:15 Friday, September 20, 2019


ODBC: Called SQLTables with schema of tmp
81         proc datasets lib=tmp nowarn nolist;
82            delete class;
83         /*   delete CLASS;  * uncomment to clean up ;*/
84         quit;

NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              93.37k
      OS Memory           12836.00k
      Timestamp           20/09/2019 11:36:17 AM

I draw attention to:

ODBC_22: Prepared: on connection 2
SELECT * FROM "tmp"."CLASS" WHERE 0=1 
 
ODBC: AUTOCOMMIT is NO for connection 3
INFO: Data set block I/O cannot be used because:
INFO:   -  The data sets use different engines, have different variables or have attributes that may differ.
 
ODBC_23: Executed: on connection 3
CREATE TABLE "tmp"."CLASS" ("Name" varchar(8),"Sex" varchar(1),"Age" float,"Height" float,"Weight" float)

I didn't specify "tmp"."CLASS", I specified "tmp"."class". Because of the above, my downstream code fails.

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001342346.htm says that PRESERVE_NAMES (which is not an Intellisense choice in EG) activates both PRESERVE_TAB_NAMES and PRESERVE_COL_NAMES = YES.

Am I doing something wrong? Otherwise, is this a design bug in PROC COPY and/or the ODBC engine? I have low confidence that this would be addressed, but I still feel it's a design bug re: the interaction of PROC COPY, ODBC engine, and a case-sensitive database.

Similar approaches using PROC APPEND or data step (per my OP) work as expected/coded.