topic Re: Rename Tables in SAS Programming

Rename Tables

Tumtum — Wed, 22 Aug 2012 12:16:55 GMT

Hi,

I want to rename tables which are in DB2 - however I keep getting errors.

proc datasets library=myfiles;

change stock_apr=stock_apr12;

run;

/*error*/ SQL0108N The name "stock_apr12" has the wrong number of qualifiers.

No luck with proc sql either:

proc sql;

connect to DB2 (datasrc=EDW user=tumtum password=123)

select * from connection to DB2

(rename table myfiles.stock_apr to myfiles.stock_apr12);

quit;

/*error*/ SQL0108N The name "stock_apr12" has the wrong number of qualifiers.

I normally manage to find solutions when I get stuck, but not this time around. I'd really appreciate some ideas from.

Re: Rename Tables

art297 — Wed, 22 Aug 2012 12:47:07 GMT

Take a look at: http://support.sas.com/kb/44/089.html

Re: Rename Tables

Tumtum — Wed, 22 Aug 2012 13:28:37 GMT

Hi Arthur,

Thank you for the feedback.

I did have a look at that previosly. That led me to use the proq sql to generate an explicit pass-through. Unless I am completely missing something o_O

Re: Rename Tables

DGBK — Wed, 19 Feb 2020 23:33:31 GMT

Hi. I am also looking for an answer to this question.

When using Toad, I simply type:

rename table libname.TEST_TRR_FINAL_BU5 
to TEST_TRR_FINAL_BU4;

However, when I try to use the following code with SAS EG:

proc sql;
	rename table libname.TEST_TRR_FINAL_BU4 
	to TEST_TRR_FINAL_BU5;
Quit;

I get a complaint that the 'rename' statement is not valid.

I have also tried following a convention I found online:

proc datasets lib=libname;
change Test_TRR_FINAL_BU5=Test_TRR_Final_BU4;
quit;

This gives a complaint about the wrong number of qualifiers. I believe this is due to the error pointed out above. When I try to specify the libname, like so:

proc datasets;
change libname.Test_TRR_FINAL_BU5=Test_TRR_Final_BU4;
quit;

SAS takes so long to complete the task that I force it to quit. I have been searching for a while for this answer, but this unanswered topic is the closest I have come to a solution.

Thanks

Re: Rename Tables

Tom — Wed, 19 Feb 2020 23:59:43 GMT

I assume that you already created a libref named LIBNAME pointing the LIBNAME schema in your remote database.

So use PROC SQL to send your remote database's rename statement to the remote database to execute.

proc sql;
connect using LIBNAME ;
execute by LIBNAME (
rename table libname.TEST_TRR_FINAL_BU5 
to TEST_TRR_FINAL_BU4
);
quit;

Re: Rename Tables

DGBK — Thu, 20 Feb 2020 14:01:50 GMT

Hi Tom,

Thanks for the quick response. I think maybe we are getting close, but I am still getting an error. For full clarity, I am not actually using "libname" as my libname. I just put that in to hide the code a bit, which in hindsight was a stupid way to rename my code. Also, I'm sure no one cares about the libnames I use.

I first define libnames as follows:

Libname act db2  Schema=Actuarial Datasrc=wedp15  authdomain="DefaultAuth";

The code that I attempted from your suggestion was then:

proc sql;
	connect using act;
	execute by act (rename table ACT.TEST_TRR_FINAL_BU4 
	to TEST_TRR_FINAL_BU5);
Quit;

This returns the following error:

ERROR: CLI execute error: [IBM][CLI Driver][DB2/AIX64] SQL0204N "ACT.TEST_TRR_FINAL_BU4" is an undefined name. SQLSTATE=42704

I know that I have access to the table because

proc sql;
Create table test as
Select count(*) as Count
from act.Test_TRR_Final_BU4;

Do you have any other suggestions?

Thanks

Re: Rename Tables

Tom — Thu, 20 Feb 2020 14:08:00 GMT

Looks like an error message from the remote database. Are you sure the syntax is correct? Does that command work when connected to the database with another tools, like TOAD? Perhaps you need to include some other command to change your session state so it is closer to the state we get when connecting with TOAD?

Re: Rename Tables

DGBK — Thu, 20 Feb 2020 17:54:05 GMT

Thanks for the help. Through numerous trials and errors, I successfully used the following code:

proc sql;
	connect using act;
	execute by act (rename table actuarial.TEST_TRR_FINAL_BU4 
	to TEST_TRR_FINAL_BU5;);
Quit;

This means that from start to finish, the code went like this:

Libname act db2  Schema=Actuarial Datasrc=wedp15  authdomain="DefaultAuth";

Proc Sql;
	connect using act;
	execute by act (rename table actuarial.TEST_TRR_FINAL_BU4 
	to TEST_TRR_FINAL_BU5;);
Quit;