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