Using INTO to create a macro variable for pass-through teradata query

[kb011235]

proc sql noprint;



select "'"||strip(hicno)||"'"

into :hicno_list separated by ","

from monmemd;



%tera_sql_con(EDWTDP);



create table new_table as

select * from connection to teradata

(select *

from data_warehouse.MPD_FSGE_CMS_RX_DRG_EVT

where hic_nbr in ( &hicno_list. )

);

quit;

Hello,

The situation is that I have a dataset in my working directory ("monmemd") from which some id's ("hicno") are queried. From there, the hicno's are used as filters in a query on another dataset that is in a teradata db ("data_warehouse.MPD_FSGE_CMS_RX_DRG_EVT"). The teradata connection is of the pass-through type, so I'm not able to join monmemd into the passthrough query. What I tried to do was to create a macro variable of the hicno's that could be used as a parameter in the teradata query, but I get the following error related to the macro variable hicno_list:

"ERROR: The text expression length (65535) exceeds maximum length (65534). The text expression has been truncated to 65534 characters."

I tried

Options mexecsize = 500000
MSYMTABMAX = 500000
MVARSIZE = 500000;

but MVARSIZE must be between 0 and 65534 length. 

The pass-through connection is super efficient compared to the direct connection. I don't want to import the entire MPD_FSGE_CMS_RX_DRG_EVT table and filter in SAS since the alternative would be more efficient.

Does anyone have any other ideas about how I can pass a one column table of ID's into a pass-through teradata query? 

Thank you.

[Reeza]

Rather than a macro variable, consider creating a macro that generates the list. This is old and untested but should still work. I think it was designed for numbers so you'll need to modify it for character values. For you code you would replace &hicno_list with the macro call, %test(t)

data t;                                                                                                                                 
input key;                                                                                                                              
cards;                                                                                                                                  
1                                                                                                                                       
-1                                                                                                                                      
3                                                                                                                                       
;                                                                                                                                       
                                                                                                                                        
%macro test(dsn);                                                                                                                       
%let dsid=%sysfunc(open(&dsn(where=(key > 0))));                                                                                     
%let cnt=%sysfunc(attrn(&dsid,nlobsf));                                                                                                
%let num=%sysfunc(varnum(&dsid,key));                                                                                                  
  %do i = 1 %to &cnt;                                                                                                                   
   %let rc=%sysfunc(fetchobs(&dsid,&i));                                                                                                
   %let val=%sysfunc(getvarn(&dsid,&num));                                                                                              
%if &i ne &cnt %then %do;                                                                                                               
&val,                                                                                                                                   
%end;                                                                                                                                   
%else %do;                                                                                                                              
&val                                                                                                                                    
%end;                                                                                                                                   
%end;                                                                                                                                   
                                                                                                                                        
%let rc=%sysfunc(close(&dsid));                                                                                                         
%put dd;                                                                                                                                
%mend test;                                                                                                                             
                                                                                                                                        
data new;                                                                                                                               
new="%test(t)";                                                                                                                        
run;                                                                                                                                    
                                                                                                                                        
proc print;                                                                                                                             
run;  

---

@kb011235 wrote:

proc sql noprint;



select "'"||strip(hicno)||"'"

into :hicno_list separated by ","

from monmemd;



%tera_sql_con(EDWTDP);



create table new_table as

select * from connection to teradata

(select *

from data_warehouse.MPD_FSGE_CMS_RX_DRG_EVT

where hic_nbr in ( &hicno_list. )

);

quit;

Hello,

The situation is that I have a dataset in my working directory ("monmemd") from which some id's ("hicno") are queried. From there, the hicno's are used as filters in a query on another dataset that is in a teradata db ("data_warehouse.MPD_FSGE_CMS_RX_DRG_EVT"). The teradata connection is of the pass-through type, so I'm not able to join monmemd into the passthrough query. What I tried to do was to create a macro variable of the hicno's that could be used as a parameter in the teradata query, but I get the following error related to the macro variable hicno_list:

"ERROR: The text expression length (65535) exceeds maximum length (65534). The text expression has been truncated to 65534 characters."

I tried

Options mexecsize = 500000
MSYMTABMAX = 500000
MVARSIZE = 500000;

but MVARSIZE must be between 0 and 65534 length. 

The pass-through connection is super efficient compared to the direct connection. I don't want to import the entire MPD_FSGE_CMS_RX_DRG_EVT table and filter in SAS since the alternative would be more efficient.

Does anyone have any other ideas about how I can pass a one column table of ID's into a pass-through teradata query? 

Thank you.

---

[ballardw]

Just for giggles did you consider ?

select distinct "'"||strip(hicno)||"'"

We obviously don't know your data but without the distinct predicate you could have duplicate values making the string longer than needed.

[kb011235]

The list of hicno's are already distinct.

[Tom]

Why not just upload the list into a temporary table in Teradata and use that table in your query?

[Reeza]

Because a large set of SAS installations are set so you can read data but not write anything to the server.

[kb011235]

I don't have that level of permission on our teradata server. 

[Tom]

If you really must generate a really long list of values into macro variables from data here is a method that make a series on macro variables and another separate macro variable that can be used to expand them all.

data _null_;
  length mvarlist value_list $32000 mvar $32;
  retain mvarlist;
  varnum+1;
  do until(length(value_list)>30000 or eof);
    set monmemd end=eof;
    by hicno ;
    if first.hicno then value_list=catx(',',value_list,quote(trim(hicno),"'"));
  end;
  mvar=cats('hicno_list',varnum);
  call symputx(mvar,value_list);
  mvarlist=catx(',',mvarlist,'&'||mvar);
  if eof then call symputx('hicno_list',mvarlist);
run;

proc sql noprint;
%tera_sql_con(EDWTDP);

create table new_table as
  select * from connection to teradata

(select *
 from data_warehouse.MPD_FSGE_CMS_RX_DRG_EVT
 where hic_nbr in ( &hicno_list. )
);

quit;

 So you have HICNO_LIST1,HICNOLIST2, etc will list of values and then HICNO_LIST has &HICNO_LIST1,&HICNO_LIST22, etc.  That way you could have up to 4000*32K bytes in the IN list.

Should work if the Teradata administrators don't cancel the query.

[kb011235]

Thanks for the effort, but your method runs into the same list length issue (capped at 65534 characters).

[Tom]

The code I posted will not make any macro variable longer than about 30K.

Are you saying that Teradata is complaining if the code is too long?

Or is PROC SQL complaining if the statement is too long?

Show your log.

[kb011235]

I ran your code again this morning and didn't get the same error message. Yesterday, I ran your code in the immediate window where I had been testing, so I think that affected the results.

I'm testing the rerun now and I'll let you know of the results.

[kb011235]

Here is the updated code:

options spool nocenter mprint symbolgen obs=max nocenter mautosource mrecall
	sasautos =('!sasroot/sasautos','/sasdata/projects1/underwriting/macros','/sasdata/prod/macros','/sasuser/bailkx4/macros') ;

proc printto log="/sasuser/&sysuserid./programs/scratchwork/test.log"
  print="/sasuser/&sysuserid./programs/scratchwork/test.lst";
  run;

/* Store data in SAS */
DATA monmemd;
   set "/sasuser/&sysuserid./wrk/support/monmemd/monmemd.sas7bdat";
RUN;

proc sort data = monmemd out = monmemd;
by hicno;
run;

data _null_;
  length mvarlist value_list $32000 mvar $32;
  retain mvarlist;
  varnum+1;
  do until(length(value_list)>30000 or eof);
    set monmemd end=eof;
    by hicno ;
    if first.hicno then value_list=catx(',',value_list,quote(trim(hicno),"'"));
  end;
  mvar=cats('hicno_list',varnum);
  call symputx(mvar,value_list);
  mvarlist=catx(',',mvarlist,'&'||mvar);
  if eof then call symputx('hicno_list',mvarlist);
run;

proc sql noprint;
%tera_sql_con(EDWTDP);

create table new_table as
  select * from connection to teradata

(select *
 from data_warehouse.MPD_FSGE_CMS_RX_DRG_EVT
 where hic_nbr in (&hicno_list.)
);

quit;

%xport(work,new_table);

Attached is the log. I deleted the list of ID's, but one thing that's interesting is right before row 71, where it says "Macro variable HICNO_LIST1 resolves to Macro variable HICNO_LIST1 resolves to '
','xxxxx', 'by xxxxx')." (The x's represent hicno's.) I don't understand how "by" was added to the list.

One other thing that I don't understand in the log is where it says that the errors were printed on pages.... I added a printto statement to try and get the errors to print to a specific place, but that didn't work.

Thanks for your help.

[Tom]

You have 4,174,828 values of HICNO.

Let's do a little arithmetic.

How long is the average HICNO? The average would have to be at LEAST 6 characters to support over 4 million distinct values.

Plus 2 for the quotes and 1 for comma.  That is over 32 Million characters just to make the list of values.

That is totally inappropriate for this type of approach.

Upload the list into the database and join in the database.  In fact keep ALL of the data in the database only pull out your summary statistics back to your SAS server.

[kb011235]

@Tom There aren't 4174828 hicno's. The number of records in monmemd is 4174828, which is filtered down to 80825 distinct hicno's.

I didn't notice until now that your code missed the other filters. Here is the updated code:

options spool nocenter mprint symbolgen obs=max nocenter mautosource mrecall
	sasautos =('!sasroot/sasautos','/sasdata/projects1/underwriting/macros','/sasdata/prod/macros','/sasuser/bailkx4/macros') ;

proc printto log="/sasuser/&sysuserid./programs/scratchwork/test.log"
  print="/sasuser/&sysuserid./programs/scratchwork/test.lst";
  run;

/* Store data in SAS */
DATA monmemd;
   set "/sasuser/&sysuserid./wrk/support/monmemd/monmemd.sas7bdat";
RUN;

proc sql;
create table hicno_table as
select DISTINCT strip(hicno) as hicno
from
	(select hicno
	, count(hicno) as rcd_ct
	from monmemd
	where substr(payment_date,1,4) = "2016" and adj_reason = ""
	group by hicno
	)
where rcd_ct = 12
order by hicno;
quit;

data _null_ options noprint;
  length mvarlist value_list $32000 mvar $32;
  retain mvarlist;
  varnum+1;
  do until(length(value_list)>30000 or eof);
    set hicno_table end=eof;
    by hicno ;
    if first.hicno then value_list=catx(',',value_list,quote(trim(hicno),"'"));
  end;
  mvar=cats('hicno_list',varnum);
  call symputx(mvar,value_list);
  mvarlist=catx(',',mvarlist,'&'||mvar);
  if eof then call symputx('hicno_list',mvarlist);
run;

proc sql noprint;
%tera_sql_con(EDWTDP);

create table new_table as
  select * from connection to teradata

(select *
 from data_warehouse.MPD_FSGE_CMS_RX_DRG_EVT
 where hic_nbr in (&hicno_list.)
);

quit;

%xport(work,new_table);

Here is the error message: ERROR: CLI prepare error: [Teradata][ODBC Teradata Driver] SQL request with parameters exceeds maximum allowed length of 1 MB

What about wrapping the proc sql teradata step in a loop and passing the hicno_list in smaller pieces, then reassembling the results? Could that work? 

Thanks

[Tom]

Just upload the data into Teradata. And then use it in your query.

libname td teradata .... DBMSTEMP=YES ;

proc sort nodupkey 
  data="/sasuser/&sysuserid./wrk/support/monmemd/monmemd.sas7bdat"
  out=td.hicno_table (keep=hicno)
;
  where substr(payment_date,1,4) = "2016" and missing(adj_reason);
run;

proc sql noprint;
connect using TD;

create table new_table as
  select * from connection to TD

(select *
 from data_warehouse.MPD_FSGE_CMS_RX_DRG_EVT
 where hic_nbr in (select hicno from hicno_table)
);

quit;