BookmarkSubscribeRSS Feed
Vk_2
Obsidian | Level 7

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

5 REPLIES 5
ballardw
Super User

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
Obsidian | Level 7

 

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
Super User

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
Super User

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
Super User

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 772 views
  • 0 likes
  • 4 in conversation