topic Re: Is there an alternative to macro variable size limitation to use in Teradata query using sas in SAS Programming

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

Vk_2 — Thu, 30 Aug 2018 20:32:42 GMT

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

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

ballardw — Thu, 30 Aug 2018 20:54:12 GMT

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.

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

Vk_2 — Thu, 30 Aug 2018 21:03:23 GMT

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.

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

ballardw — Thu, 30 Aug 2018 21:39:02 GMT

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.

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

Reeza — Thu, 30 Aug 2018 22:00:44 GMT

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.

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

Kurt_Bremser — Fri, 31 Aug 2018 07:35:58 GMT

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