Hi,
I have a requirement to truncate all the tables in DB2 environment from SAS. I have written a below query in a loop and I'm facing few issues.
proc sql;
connect to DB2(datasrc=XXXXX authdomain="XXXXX");
select * into :tablename from connection to db2 (
select tabname
from
SYSCAT.TABLES
where TABSCHEMA = %str(%')%upcase(TEST)%str(%') and
TYPE = %str(%')%upcase(T)%str(%')
);
disconnect from DB2;
quit;
%let tablename1 = &tablename.;
%macro truncate_table;
%do i = 1 %to &tablename1.;
proc sql;
connect to DB2(datasrc=XXXXX authdomain="XXXXX");
exec ( truncate table TEST.&tablename1. immediate );
disconnect from DB2;
quit;
%end;
%mend;
%truncate_table;
Below is my error message
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &tablename1.
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro TRUNCATE_TABLE will stop executing.
Please let me know where I went wrong. Thanks
Few remarks to your code:
1) Try replace %str(%')%upcase(TEST)%str(%') by %quote(%upacse(TEST))
2) I suppose that tabname is a string entered into the macro variable tablename
and then assigned to tabelname1 - then what does next line mean?
%do i = 1 %to &tablename1.;
I guess that tablename contains a list of tables' names and you want to execute
%truncate_table with each of the names. If so then you need to:
select * into :tablename separated by " "
from connection to db2( ... etc ...
and count the number of names:
do i=1 %to %sysfunc(countw(&tablename));
%let tablename1 = %scan(&tablename,&i);
... etc. ...
I hope the other code relating to DB2 is OK.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.