Iterating through rows and drop teradata table

Reply
New Contributor
Posts: 3

Iterating through rows and drop teradata table

I have a control that that has one column - Output_tbl_name . It has list of tables that has got processed by our ETL.

I have been asked to loop through that table and drop the tables that have been processed.

I read the following from Teradata database control table and bring into a SAS_Table_Del

 

CREATE TABLE SAS_Table_Del AS
 SELECT * FROM CONNECTION TO TERADATA
(
 SELECT Output_Tbl_Nm , ROW_NUMBER() OVER ( ORDER BY 1) as record_id FROM <TD Database>.<TD Control Table>Smiley Wink

 

 

 

Then I have to loop through this SAS_Table and drop the table from the TD Database

I have used the following 

DATA SAS_Table_Del;

let i = _N_;

proc_sql;

SELECT Output_Tbl_Nm into Smiley Surprisedutput_Tbl_Nm from SAS_Table_Del where record_id = &i;

 

 

PROC SQL NOERRORSTOP;
CONNECT TO TERADATA (
 TDPID="&TdPid."
USER="&TdUid."
 PASS="&TdPass"
 QUERY_BAND="&Queryband."
);
EXECUTE (DROP TABLE ZMKTTO_MCMADHOC.&Output_Tbl_NmSmiley Wink BY TERADATA;
 EXECUTE (COMMIT WORKSmiley Wink BY TERADATA;

 DISCONNECT FROM TERADATA;
 QUIT;

RUN;

 

It is failing for me.

How do I loop through the dataset and get he Output_tbl_nm assigned one value at a time and connect back and drop that in teradata from SAS.

 

 

 

Super User
Posts: 5,257

Re: Iterating through rows and drop teradata table

With the parameters in a data set it would be natural to use call execute from within a data step. You could either call a macro, or insert the SQL into the call execute.
Data never sleeps
Super User
Super User
Posts: 7,407

Re: Iterating through rows and drop teradata table

As @LinusH has said, call execute would be the ideal something like:

data _null_;
  set sas_table_del end=last;
  if _n_=1 then call execute('proc sql; connect to teradata (...));');
  call execute('execute (drop table '||strip(output_tbl_nm)||') by teradata;
  if last then call execute('disconnect from teradata; quit;');
run; 

However I would also ask why you are doing this?  A database should be setup in such a way that its structure rarely changes and only under a controlled manner.  Willy nilly dropping tables left right and center is a good way to corrupt your whole database (unless this is your temporary work area).  And if it is your work area opening that in teradata front end and select all del would be far simpler.

New Contributor
Posts: 3

Re: Iterating through rows and drop teradata table

So if my SAS_Table_Del  which is in SAS (and not teradata) has 4 records, how do I get the identify the first  Output_Tbl_Nm  = Table_A when _N_ = 1, and Table_B whebn _N_ = 2 and so on. 

 

Output table name , Record_id

Table_A                 1

Table-B                  2

Table_C                 3

Table_D                 4

 

My query  - select Output_Tbl_Nm into Smiley Surprisedutput_Tbl_Nm from SAS_Table_Del where record_id = _N_ is throwiing errors.

 

also once I get &Output_Tbl_Nm identified, then I need to connect to teradata and drop that specific table and then loop back again to get the next table using the record_id = 2 (Table_B) and so on until I get to the last record_id = 4 for Table_D

 

Answer to your question - I am dropping all these stage tables that are being created by a Fastload process that and it is writing thme into different source tables and writes and control record with the fastload creatd tablename into a control table.  

My job that is for dropping the staging tables database is to identify all these fast load tables and drop them interatively one and a time in a loop as they have already got processed by downstream ETL.

Super User
Super User
Posts: 7,407

Re: Iterating through rows and drop teradata table

Sorry, yu don't seem to have understood or read my post.  The call execute mechanism will do the whole thing for you from the list you have.  I.e. if you have four records then the call execute will generate a proc sql step with four delete commands.  There is no need to do the whole select into.

 

As for the seond part, if they are temporary tables created as part of a load, then its part of the loads role to cleanup after itself - that is the process which is currently not working correctly, so look into fixing that and this issue becomes moot.

New Contributor
Posts: 3

Re: Iterating through rows and drop teradata table

Thanks, i did understand it the second time. I was able to do exacltly what you had suggested. Please find the code below.

. I am using NOSTOPERROR option, cause i want it to go through the list and drop the table and delete the control record even if the tables does not exists. It does exactly like that, but on the SAS client i still see the error  - Table does not exist..., so how do I ensure that it does not generate any errors when it gets called by my wrapper script.

 

Thanks again for all the guidance. In my case a separate Fastload process was createing the data and my ETL code was moving it into our Datamart and then dropping those tables and corresponding control records.

 

DATA _NULL_;
set SAS_Table_Del end=last;
if _n_ = 1 then call execute ('proc sql NOERRORSTOP; connect to teradata(
TDPID="&TdPid."
USER="&TdUid."
PASS="&TdPass"
QUERY_BAND="&Queryband."
);');

call EXECUTE ('execute(DROP TABLE ZMKTTO_MCMADHOC.'||strip(Output_Tbl_Nm)||'Smiley Wink BY TERADATA;');
call EXECUTE ('EXECUTE (COMMIT WORKSmiley Wink BY TERADATA;');
call execute('execute(DELETE FROM ZMKTTO_MCMADHOC.DB_MNGT_CTL_MCM_NEW where Output_Tbl_Nm = '''||strip(Output_Tbl_Nm)||''' and Prcs_flg = '''||'C'||'''Smiley Wink BY TERADATA;');

call EXECUTE ('EXECUTE (COMMIT WORKSmiley Wink BY TERADATA;');

if last then call execute('disconnect from teradata; quit;');
run;

 

 

Super User
Super User
Posts: 7,407

Re: Iterating through rows and drop teradata table

How can it be running on tables that do not exist?  This bit of code you provided should return a list of only existing  tables:

CREATE TABLE SAS_Table_Del AS
 SELECT * FROM CONNECTION TO TERADATA
(
 SELECT Output_Tbl_Nm , ROW_NUMBER() OVER ( ORDER BY 1) as record_id FROM <TD Database>.<TD Control Table>Smiley Wink

 

Also, please code using some readability format, i.e. same casing, indentations and make sure you post code into a code block in the post (use the {i} or sas symbol above where you post) etc. your code is very hard to look at, example:

data _null_;
  set sas_table_del end=last;
  if _n_=1 then call execute ('proc sql noerrorstop; 
          connect to teradata(tdpid="&tdpid." user="&tduid." pass="&tdpass" query_band="&queryband.");');
  call execute ('EXECUTE(DROP TABLE ZMKTTO_MCMADHOC.'||strip(Output_Tbl_Nm)||') BY TERADATA;');
  call execute ('EXECUTE (COMMIT WORK) BY TERADATA;');
  call execute('EXECUTE(DELETE FROM ZMKTTO_MCMADHOC.DB_MNGT_CTL_MCM_NEW where OUTPUT_TBL_NM='''||strip(Output_Tbl_Nm)||''' and PRCS_FLG='''||'C'||''') BY TERADATA;');
  call execute ('EXECUTE (COMMIT WORK) BY TERADATA;');
  if last then call execute('disconnect from teradata; quit;');
run;
Ask a Question
Discussion stats
  • 6 replies
  • 181 views
  • 0 likes
  • 3 in conversation