Why won't SAS leave my RDBMS case-sensitive table names alone?

ScottBass — Wed, 12 Jun 2019 03:24:14 GMT

SAS 9.3

ODBC engine

I run this code, where the TMP library is an ODBC connection to a case-sensitive SQL Server database:

%kill(lib=tmp,data=apdc_sample_df apdc_sample_hf);
proc copy in=work out=tmp;
   select apdc_sample_df apdc_sample_hf;
run;

https://github.com/scottbass/SAS/blob/master/Macro/kill.sas is just a wrapper around PROC DATASETS; DELETE ...

Here is the SAS log for run #1, where the target tables don't exist in the database:

27         %kill(lib=tmp,data=apdc_sample_df apdc_sample_hf);


MPRINT(KILL):   proc sql noprint;
MPRINT(KILL):   select readonly into :readonly separated by " " from dictionary.libnames where upcase(libname) = "TMP" ;
MPRINT(KILL):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              165.09k
      OS Memory           19748.00k
      Timestamp           12/06/2019 11:52:38 AM
      

ODBC: Called SQLTables with schema of tmp
MPRINT(KILL):   proc datasets lib=TMP memtype=(DATA VIEW) nolist nowarn;
MPRINT(KILL):  delete apdc_sample_df apdc_sample_hf;
MPRINT(KILL):   quit;

2 The SAS System                                                                            18:48 Tuesday, June 11, 2019

NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.01 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              89.53k
      OS Memory           19748.00k
      Timestamp           12/06/2019 11:52:38 AM
      

28         proc copy in=work out=tmp;
29            select apdc_sample_df apdc_sample_hf;
30         run;

NOTE: Copying WORK.APDC_SAMPLE_DF to TMP.APDC_SAMPLE_DF (memtype=DATA).
 
ODBC_310: Prepared: on connection 6
SELECT * FROM "tmp"."APDC_SAMPLE_DF" WHERE 0=1 
 
ODBC: AUTOCOMMIT is NO for connection 7
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
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_311: Executed: on connection 7
CREATE TABLE "tmp"."APDC_SAMPLE_DF" ("source_id" varchar(4),"cherel_id" varchar(11),"recid_pf" varchar(2),"recid_s" 
varchar(10),"ppn" varchar(14),"pfn" varchar(14),"code1" varchar(3),"value1" varchar(14),"desc1" varchar(200),"code2" 
varchar(3),"value2" varchar(14),"desc2" varchar(200),"code3" varchar(3),"value3" varchar(14),"desc3" 
varchar(200),"endflag" varchar(1),"facility_identifier" varchar(4),"stay_number_e" 
varchar(40),"episode_sequence_number" int)
 
ODBC: COMMIT performed on connection 7.
NOTE: There were 100000 observations read from the data set WORK.APDC_SAMPLE_DF.
NOTE: The data set TMP.APDC_SAMPLE_DF has 100000 observations and 19 variables.
NOTE: Copying WORK.APDC_SAMPLE_HF to TMP.APDC_SAMPLE_HF (memtype=DATA).
 
ODBC_312: Prepared: on connection 6
SELECT * FROM "tmp"."APDC_SAMPLE_HF" WHERE 0=1 
 
ODBC: AUTOCOMMIT is NO for connection 7
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_313: Executed: on connection 7
CREATE TABLE "tmp"."APDC_SAMPLE_HF" ("source_id" varchar(4),"file_creation_date_hrlo" varchar(8),"moh_project_id" 
varchar(10),"project_name" varchar(64),"moh_service_id" varchar(4),"moh_batch_id" varchar(2),"total_moh_src_codes" 
varchar(2),"total_src_codes" varchar(2),"hrlo_code" varchar(1),"hrlo_service_id" varchar(11),"total_recs" 
float,"unique_ppns" float,"unique_pfns" float,"moh_mlk_service_id" float)
 
3 The SAS System                                                                            18:48 Tuesday, June 11, 2019

ODBC: COMMIT performed on connection 7.
NOTE: There were 1 observations read from the data set WORK.APDC_SAMPLE_HF.
NOTE: The data set TMP.APDC_SAMPLE_HF has 1 observations and 14 variables.
NOTE: PROCEDURE COPY used (Total process time):
      real time           1.15 seconds
      user cpu time       0.88 seconds
      system cpu time     0.01 seconds
      memory              319.71k
      OS Memory           19748.00k
      Timestamp           12/06/2019 11:52:39 AM

It is at this point I note that I have the tables tmp.APDC_SAMPLE_DF and tmp.APDC_SAMPLE_HF in my database.

If I wanted capitalized table names, I would have coded it that way. I want the table names as lower case. My downstream code is using explicit passthrough, where the case-sensitivity of the table names matters.

Here is the SAS log for run #2, where the above target tables now exist in the database:

27         %kill(lib=tmp,data=apdc_sample_df apdc_sample_hf);
MPRINT(KILL):   proc sql noprint;
MPRINT(KILL):   select readonly into :readonly separated by " " from dictionary.libnames where upcase(libname) = "TMP" ;
MPRINT(KILL):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              165.18k
      OS Memory           19748.00k
      Timestamp           12/06/2019 11:55:48 AM
      

ODBC: Called SQLTables with schema of tmp
MPRINT(KILL):   proc datasets lib=TMP memtype=(DATA VIEW) nolist nowarn;
MPRINT(KILL):  delete apdc_sample_df apdc_sample_hf;
MPRINT(KILL):   quit;

NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.00 seconds
2 The SAS System                                                                            18:48 Tuesday, June 11, 2019

      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              89.59k
      OS Memory           19748.00k
      Timestamp           12/06/2019 11:55:48 AM
      

28         proc copy in=work out=tmp;
29            select apdc_sample_df apdc_sample_hf;
30         run;

NOTE: Copying WORK.APDC_SAMPLE_DF to TMP.APDC_SAMPLE_DF (memtype=DATA).
 
ODBC_316: Prepared: on connection 6
SELECT * FROM "tmp"."APDC_SAMPLE_DF" WHERE 0=1 
 
ERROR: The ODBC table APDC_SAMPLE_DF has been opened for OUTPUT. This table already exists, or there is a name conflict 
       with an existing object. This table will not be replaced. This engine does not support the REPLACE option.
ERROR: File TMP.APDC_SAMPLE_DF.DATA has not been saved because copy could not be completed.
NOTE: Copying WORK.APDC_SAMPLE_HF to TMP.APDC_SAMPLE_HF (memtype=DATA).
 
ODBC_317: Prepared: on connection 6
SELECT * FROM "tmp"."APDC_SAMPLE_HF" WHERE 0=1 
 
ERROR: The ODBC table APDC_SAMPLE_HF has been opened for OUTPUT. This table already exists, or there is a name conflict 
       with an existing object. This table will not be replaced. This engine does not support the REPLACE option.
ERROR: File TMP.APDC_SAMPLE_HF.DATA has not been saved because copy could not be completed.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE COPY used (Total process time):
      real time           0.01 seconds
      user cpu time       0.00 seconds
      system cpu time     0.01 seconds
      memory              220.00k
      OS Memory           19748.00k
      Timestamp           12/06/2019 11:55:48 AM

Trying one more run:

27         %kill(lib=tmp,data=APDC_SAMPLE_DF APDC_SAMPLE_HF);


MPRINT(KILL):   proc sql noprint;
MPRINT(KILL):   select readonly into :readonly separated by " " from dictionary.libnames where upcase(libname) = "TMP" ;
MPRINT(KILL):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              167.34k
      OS Memory           19748.00k
      Timestamp           12/06/2019 12:06:06 PM
      

ODBC: Called SQLTables with schema of tmp
MPRINT(KILL):   proc datasets lib=TMP memtype=(DATA VIEW) nolist nowarn;
MPRINT(KILL):  delete APDC_SAMPLE_DF APDC_SAMPLE_HF;
MPRINT(KILL):   quit;

2 The SAS System                                                                            18:48 Tuesday, June 11, 2019

NOTE: Deleting TMP.APDC_SAMPLE_DF (memtype=DATA).
 
ODBC_318: Prepared: on connection 6
SELECT * FROM "tmp"."APDC_SAMPLE_DF" WHERE 0=1 
 
 
ODBC_319: Executed: on connection 6
DROP  TABLE "tmp"."APDC_SAMPLE_DF"
 
NOTE: Deleting TMP.APDC_SAMPLE_HF (memtype=DATA).
 
ODBC_320: Prepared: on connection 6
SELECT * FROM "tmp"."APDC_SAMPLE_HF" WHERE 0=1 
 
 
ODBC_321: Executed: on connection 6
DROP  TABLE "tmp"."APDC_SAMPLE_HF"
 
NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.04 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              138.25k
      OS Memory           19748.00k
      Timestamp           12/06/2019 12:06:06 PM
      

28         proc copy in=work out=tmp;
29            select apdc_sample_df apdc_sample_hf;
30         run;

NOTE: Copying WORK.APDC_SAMPLE_DF to TMP.APDC_SAMPLE_DF (memtype=DATA).
 
ODBC_322: Prepared: on connection 6
SELECT * FROM "tmp"."APDC_SAMPLE_DF" WHERE 0=1 
 
ODBC: AUTOCOMMIT is NO for connection 7
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
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_323: Executed: on connection 7
CREATE TABLE "tmp"."APDC_SAMPLE_DF" ("source_id" varchar(4),"cherel_id" varchar(11),"recid_pf" varchar(2),"recid_s" 
varchar(10),"ppn" varchar(14),"pfn" varchar(14),"code1" varchar(3),"value1" varchar(14),"desc1" varchar(200),"code2" 
varchar(3),"value2" varchar(14),"desc2" varchar(200),"code3" varchar(3),"value3" varchar(14),"desc3" 
varchar(200),"endflag" varchar(1),"facility_identifier" varchar(4),"stay_number_e" 
varchar(40),"episode_sequence_number" int)
 
3 The SAS System                                                                            18:48 Tuesday, June 11, 2019

ODBC: COMMIT performed on connection 7.
NOTE: There were 100000 observations read from the data set WORK.APDC_SAMPLE_DF.
NOTE: The data set TMP.APDC_SAMPLE_DF has 100000 observations and 19 variables.
NOTE: Copying WORK.APDC_SAMPLE_HF to TMP.APDC_SAMPLE_HF (memtype=DATA).
 
ODBC_324: Prepared: on connection 6
SELECT * FROM "tmp"."APDC_SAMPLE_HF" WHERE 0=1 
 
ODBC: AUTOCOMMIT is NO for connection 7
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_325: Executed: on connection 7
CREATE TABLE "tmp"."APDC_SAMPLE_HF" ("source_id" varchar(4),"file_creation_date_hrlo" varchar(8),"moh_project_id" 
varchar(10),"project_name" varchar(64),"moh_service_id" varchar(4),"moh_batch_id" varchar(2),"total_moh_src_codes" 
varchar(2),"total_src_codes" varchar(2),"hrlo_code" varchar(1),"hrlo_service_id" varchar(11),"total_recs" 
float,"unique_ppns" float,"unique_pfns" float,"moh_mlk_service_id" float)
 
ODBC: COMMIT performed on connection 7.
NOTE: There were 1 observations read from the data set WORK.APDC_SAMPLE_HF.
NOTE: The data set TMP.APDC_SAMPLE_HF has 1 observations and 14 variables.
NOTE: PROCEDURE COPY used (Total process time):
      real time           1.24 seconds
      user cpu time       0.98 seconds
      system cpu time     0.01 seconds
      memory              317.62k
      OS Memory           19748.00k
      Timestamp           12/06/2019 12:06:07 PM

Ok, so that works, although I still have the problem of capitalized table names.

So whatever SAS is doing under the covers:

PROC DATASETS doesn't detect the lowercase table names, and thus doesn't delete them. It's as though it doesn't implicitly capitalize the table names in the way PROC COPY did? Not that I wanted the capitalized table names in the first place.
So PROC COPY fails since the ODBC engine doesn't support REPLACEing a table.

I also tried:

%kill(lib=tmp,data=apdc_sample_df apdc_sample_hf);
proc copy in=work out=tmp;
   select 'apdc_sample_df'n 'apdc_sample_hf'n;
run;

but no joy.

Why won't SAS just leave my code alone? Is this an issue with the ODBC driver, rather than SAS? SAS and ODBC have been around a long time, why is this still an issue?

I'll eventually find a workaround, but the current functionality is a PITA.

Re: Why won't SAS leave my RDBMS case-sensitive table names alone?

ScottBass — Wed, 12 Jun 2019 02:21:26 GMT

Ok, so the workaround is to not use PROC COPY:

%kill(lib=tmp,data=apdc_sample_df apdc_sample_hf);
data tmp.apdc_sample_df;
   set work.apdc_sample_df;
run;
data tmp.apdc_sample_hf;
   set work.apdc_sample_hf;
run;

Second run:

MPRINT(KILL):   proc sql noprint;
MPRINT(KILL):   select readonly into :readonly separated by " " from dictionary.libnames where upcase(libname) = "TMP" ;
MPRINT(KILL):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              165.59k
      OS Memory           19748.00k
      Timestamp           12/06/2019 12:18:28 PM
      

ODBC: Called SQLTables with schema of tmp
MPRINT(KILL):   proc datasets lib=TMP memtype=(DATA VIEW) nolist nowarn;
5 The SAS System                                                                            18:48 Tuesday, June 11, 2019

MPRINT(KILL):  delete apdc_sample_df apdc_sample_hf;
MPRINT(KILL):   quit;

NOTE: Deleting TMP.apdc_sample_df (memtype=DATA).
 
ODBC_392: Prepared: on connection 6
SELECT * FROM "tmp"."apdc_sample_df" WHERE 0=1 
 
 
ODBC_393: Executed: on connection 6
DROP  TABLE "tmp"."apdc_sample_df"
 
NOTE: Deleting TMP.apdc_sample_hf (memtype=DATA).
 
ODBC_394: Prepared: on connection 6
SELECT * FROM "tmp"."apdc_sample_hf" WHERE 0=1 
 
 
ODBC_395: Executed: on connection 6
DROP  TABLE "tmp"."apdc_sample_hf"
 
NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.10 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              138.87k
      OS Memory           19748.00k
      Timestamp           12/06/2019 12:18:28 PM
      

MPRINT(CODE):  ;
MPRINT(CODE):   data tmp.apdc_sample_df;
MPRINT(CODE):   set work.apdc_sample_df;
MPRINT(CODE):   run;
 
ODBC_396: Prepared: on connection 6
SELECT * FROM "tmp"."apdc_sample_df" WHERE 0=1 
 
ODBC: AUTOCOMMIT is NO for connection 7
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
 
ODBC_397: Executed: on connection 7
CREATE TABLE "tmp"."apdc_sample_df" ("source_id" varchar(4),"cherel_id" varchar(11),"recid_pf" varchar(2),"recid_s" 
varchar(10),"ppn" varchar(14),"pfn" varchar(14),"code1" varchar(3),"value1" varchar(14),"desc1" varchar(200),"code2" 
varchar(3),"value2" varchar(14),"desc2" varchar(200),"code3" varchar(3),"value3" varchar(14),"desc3" 
varchar(200),"endflag" varchar(1),"facility_identifier" varchar(4),"stay_number_e" 
varchar(40),"episode_sequence_number" int)
 
6 The SAS System                                                                            18:48 Tuesday, June 11, 2019

ODBC: COMMIT performed on connection 7.
NOTE: There were 100000 observations read from the data set WORK.APDC_SAMPLE_DF.
NOTE: The data set TMP.apdc_sample_df has 100000 observations and 19 variables.
NOTE: DATA statement used (Total process time):
      real time           0.93 seconds
      user cpu time       0.84 seconds
      system cpu time     0.04 seconds
      memory              383.06k
      OS Memory           19748.00k
      Timestamp           12/06/2019 12:18:29 PM
      

MPRINT(CODE):   data tmp.apdc_sample_hf;
MPRINT(CODE):   set work.apdc_sample_hf;
MPRINT(CODE):   run;

 
ODBC_398: Prepared: on connection 6
SELECT * FROM "tmp"."apdc_sample_hf" WHERE 0=1 
 
ODBC: AUTOCOMMIT is NO for connection 7
 
ODBC_399: Executed: on connection 7
CREATE TABLE "tmp"."apdc_sample_hf" ("source_id" varchar(4),"file_creation_date_hrlo" varchar(8),"moh_project_id" 
varchar(10),"project_name" varchar(64),"moh_service_id" varchar(4),"moh_batch_id" varchar(2),"total_moh_src_codes" 
varchar(2),"total_src_codes" varchar(2),"hrlo_code" varchar(1),"hrlo_service_id" varchar(11),"total_recs" 
float,"unique_ppns" float,"unique_pfns" float,"moh_mlk_service_id" float)
 
ODBC: COMMIT performed on connection 7.
NOTE: There were 1 observations read from the data set WORK.APDC_SAMPLE_HF.
NOTE: The data set TMP.apdc_sample_hf has 1 observations and 14 variables.
NOTE: DATA statement used (Total process time):
      real time           0.06 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              310.59k
      OS Memory           19748.00k
      Timestamp           12/06/2019 12:18:29 PM

topic Re: Why won't SAS leave my RDBMS case-sensitive table names alone? in SAS Programming

Why won't SAS leave my RDBMS case-sensitive table names alone?

Re: Why won't SAS leave my RDBMS case-sensitive table names alone?