Passing huge String without Macro variable in SAS

[R_K_]

Seems there is a limit to the string size that can be passed in a SAS variable and/or SAS macro variable. 

ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0101N  The statement 
       was not processed because a limit such as a memory limit, an SQL limit, 
       or a database limit was reached.  SQLSTATE=54001

To resolve this is the only solution to create a sastemp? Are there alternative ways?

The string criteria limiting the data is a combination of two variables and is huge .

Sample : 

g	p
297	A
297	B
462	CA
462	CB
462	CS
462	CT
462	EA
462	EB
462	ES
462	ET
635	JC
635	JD
635	JK
635	JL
1987	AA
1987	AB
1987	BA
1987	BB

Sample query:

proc sql;
connect to db2(database=xxxxx000 &_log_host );/*connect to the database*/
create table temp as  
select * from connection to db2

( select    a.x, a.y

  from   abc.clm as a

 where

    a.open_dt between '2024-01-01' and '2024-12-31'

  and

     ( a.g in ('297') and a.p in ('A','B'))

or ( a.g in ('462') and a.p in ('CA','CB','CS','CT','EA','EB','ES','ET'))

or ( a.g in ('635') and a.p in ('JC','JD','JK','JL'))

or ( a.g in ('1987') and a.p in ('AA','AB','BA','BB'))

or .... 

)

with ur);  
%put &sqlxmsg;
disconnect from db2;
%put &sqlxmsg;
quit;

[Tom]

Hard to tell what the question is.  And also whether the error message has anything to do with your actual issue.

I think perhaps you are asking how to filter data by a combination of two variables, in this example they are named G and P.

So, yes, the best solution is to create a table in the remote database with the list of values and then ask the remote database to do something like an inner join that will filter the data.

Do you have permission to make temporary tables in the remote database? Or is there a permanent table that you can load values into?

[quickbluefish]

Yeah, creating a #temp table on the SQL side is almost certainly a better way to go about this:

data sqllib.'#GPdata'n;
set GPdata (keep=G P);
run;

...and then in the SQL passthru, just inner join your CLM data to #GPdata on G and P.  

[R_K_]

SAS variable , macro variable does not work and throws error.

I know I could create a temp table and use inner join.

The issue was that criteria on which the data needs to be limited is huge and I was looking for an alternative solution anything other than creating a table? 

[quickbluefish]

What do you mean by "huge"? Billions of records? Do you literally not have enough memory available to create a temp table? You could also create a permanent table (i.e., without the #) in whatever schema for which you have write access and then drop it after you're done if the issue is insufficient memory.

[R_K_]

Not billions but smallest is 5K rows of criteria - combination of g and p . 

Yes, a temp dataset or table can be created but I will have to do this every time I want to run the code. This is due to the fact that the g and p combination values are dynamic and keep changing ; its not possible to stored in a permanent table.

[SASKiwi]

A temporary table is your best option as it avoids limits on lengths of SAS and macro variables and SQL statements.

[ChrisNZ]

I don't see where you use a macro variable.

In any case, the message

---

ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0101N The statement
was not processed because a limit such as a memory limit, an SQL limit,
or a database limit was reached. SQLSTATE=54001

---

indicates that the statement is too long or too complex. 

This is a DB2 limitation: Your SQL clause is too long or complex and DB2 rejects it.
See https://www.ibm.com/docs/en/db2-for-zos/12?topic=codes-101

Talk to the DB2 DBA for recommendations.

As stated, the simplest work-around is to join to an uploaded table.

Another solution is to send a query smaller than the maximum allowed, and apply the rest of the tests in a subsequent phase.

[Patrick]

You can exchange variables with strings up-to 32767 characters between SAS and a DB. You need to set connection option dbmaxtext=32767 because the default is normally 4096 characters.

I can't see in your code where you would use such a variable.

Also not sure how you would use a macro variable. Is your idea to construct some where condition on the SAS side and store it in a macro variable that you then use in your SQL query? I guess that would work as long as the string fits into the SAS macro variable (max 32767 or 65534 characters depending on environment). 

From how it looks like loading your (still small) SAS table first into a DB table (could be a temporary table) for a join is likely the appropriate approach.