Is there an alternative to macro variable size limitation to use in Teradata query using sas

[Vk_2]

I have a query like this :

PROC SQL ;
connect to teradata as tera1 (server='XXXXX' user=&userid pwd=&pwd database ="XXXX" tpt=yes fastexport=yes);
create table TRANS as
select  * from connection to tera1
(
SELECT CUST_ID,FIELD2, FIELD3,field4  from table1 where CUST_ID IN
(select CUST_ID 
from table2 group by 1) 
group by 1,2,3,4);
disconnect from tera1;
QUIT;

The fields that I am interested is in teradata. I get the list in Excel,I  use values of cust_id in a macro variable and use it in the above code .But  Since the CUST_ID I am interested in is exceeding the macro variable capacity of 65540. The only work around that I am doing is to create a table(table2) in teradata and using it.  Is there any alternative to do this task?

using sas eg 7.12

[ballardw]

Since your code does not show any obvious use of macro variables it is hard to tell how you may be attempting to use one.

Most of the approaches that I have seen people exceed the default sizes of the macro variables are putting data into variables in a sub-optimal manner.

If you have hundreds of values it is often better to place the values in a data set/table and use an appropriate join.

Such as

data have;
  input name $;
datalines;
Alfred
James
Judy
Thomas
;
run;

proc sql;
   create table want as
   select b.* 
   from have as a
        left join
        sashelp.class as b
        on a.name=b.name
   ;
quit;

Where the HAVE set would contain you customer id's if I am understanding the situation.

[Vk_2]

PROC SQL NOPRINT;

SELECT quote (CUST_ID,"'")

INTO : CUST_ID separated by ', '

FROM work.'DATA Some CHARTS_0000'N;

QUIT;

%put macro variable CUST_ID:& CUST_ID;





/*with in code I use to use like this- CUST_ID IN (&CUST_ID)*/

The sas dataset is not recognised in terdata using sas which is the main problem. 

[ballardw]

If you are using passthrough SQL you need to move the data set to the data base.

If the data set only contains the ID values you need and there are not millions of them the upload should not take much time.

[Reeza]

You can use a macro that will place the values at run time, instead of a macro variable list.

See an example here:

http://support.sas.com/kb/39/605.html

I feel like there was a better reference but I can't find it at the moment.

[Kurt_Bremser]

---

@Vk_2 wrote:

PROC SQL NOPRINT;

SELECT quote (CUST_ID,"'")

INTO : CUST_ID separated by ', '

FROM work.'DATA Some CHARTS_0000'N;

QUIT;

%put macro variable CUST_ID:& CUST_ID;





/*with in code I use to use like this- CUST_ID IN (&CUST_ID)*/

The sas dataset is not recognised in terdata using sas which is the main problem. 

---

A clear abuse of the macro facility. Extract the CUST_ID into a dataset, and use that for further coding:

• create a format from it
• read it into a hash table
• do a join
• use it for a sub-select

If you need it on the teradata side, write it to a temporary table there and use that in SQL for joining or a sub-select.