BookmarkSubscribeRSS Feed
jatinr
Calcite | Level 5

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>;)

 

 

 

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 :Output_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_Nm;) BY TERADATA;
 EXECUTE (COMMIT WORK;) 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.

 

 

 

6 REPLIES 6
LinusH
Tourmaline | Level 20
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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

jatinr
Calcite | Level 5

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 :Output_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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

jatinr
Calcite | Level 5

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)||';) 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;

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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>;)

 

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2605 views
  • 0 likes
  • 3 in conversation