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.
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.
Click image to register for webinar