Help using Base SAS procedures

PROC SQL delete from DB2 table that has a qualifier ?

Reply
Occasional Contributor
Posts: 16

PROC SQL delete from DB2 table that has a qualifier ?

Hi I want to delete rows from a db2 table as shown in below code. the table has a qualifier name followed by table name. ADRPMMT - is the table qualifier and INV_COMM_CONV_RATE is the table name. But it is taking ADRPMMT as SAS libname. please let me know what change is required to the code.
22 PROC SQL;
23 CONNECT TO DB2(DB2SSID=DB39);
24 DELETE FROM ADRPMMT.INV_COMM_CONV_RATE
25 WHERE ROW_LOAD_DATE = &CHKDATE ;
ERROR: Libname ADRPMMT is not assigned.
ERROR: Libname ADRPMMT is not assigned.
ERROR: Libname ADRPMMT is not assigned.
ERROR: Libname ADRPMMT is not assigned.
ERROR: Libname ADRPMMT is not assigned.
ERROR: Libname ADRPMMT is not assigned.
Super User
Posts: 5,424

Re: PROC SQL delete from DB2 table that has a qualifier ?

If you are using DELETE FROM in "open" proc sql code, it will assume that twp level table names will contain a SAS libname. In fact, it requires that you first assign a libname to your DB2 database.

But by using CONNECT TO, you are using SQL pass-thru. To use pass-thru to delete records enclose your DB2 specific SQL using execute:

EXCUTE( ... ) BY DB2;

/Linus
Data never sleeps
Occasional Contributor
Posts: 16

Re: PROC SQL delete from DB2 table that has a qualifier ?

Thanks Linus , it worked !
Ask a Question
Discussion stats
  • 2 replies
  • 234 views
  • 0 likes
  • 2 in conversation