Error : Issue in do loop comparison with character variable

[helannivas88]

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

[Shmuel]

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.