How to create a macro variable which exceeds length of 65535 to query DB with PassThru?

[buddha_d]

Hi all, 

            I have hashed account numbers that I am querying against a database. I cannot accommodate the length of a macro. Could you please help me with that? 

I have used the following code to generate a sample dataset. This is dummy data. I am attaching error that I am getting while running the code. 

data ValList (drop=ValueList);
do ValueList = 100 to 1000000 by 100;
valuenum=put(ValueList,6.);
output;
end;
run;

/*method1*/

proc sql noprint;
select "'"||strip(Valuenum)||"'" into :SearchMacro separated by ', ' from ValList;
quit;

/* method2*/

%macro test;                                                                                                                            
  %let dsid=%sysfunc(open(ValList));                                                                                                         
  %let cnt=%sysfunc(attrn(&dsid,nobs));                                                                                                  
    %do i=1 %to &cnt;                                                                                                                     
      %let rc=%sysfunc(fetchobs(&dsid,&i));                                                                                               
      %cmpres(%sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,Valuenum)))))                                                                       
    %end;                                                                                                                                 
  %let rc=%sysfunc(close(&dsid));                                                                                                        
%mend test;                                                                                                                             
       
                                                                                                                                
%put %test;

 The second method was copied from 
https://support.sas.com/kb/39/605.html 

It works well for the shorter numbers (for a macro), but I don't know how to give quotes and commas to separate from one number to the other. Any help with that code is much appreciated. 

Thanks in advance,

Buddha.

[PaigeMiller]

I think you are referring to a macro variable that is too long, and not a macro. If so, please change the subject line in your original message to reflect that change.

Please do not attach files. Many of us will not download attached files. I cannot see the error you have in an attachment.

Please do not show us the log in non-monospaced fonts. Instead, copy the log as text and then paste it into the window that appears when you click on the </> icon.

In general, if you are getting a macro variable that is too long, you can get around this via creating code using CALL EXECUTE or perhaps via the method in your link.

[ballardw]

Why do you want to put that much text into a macro variable?

That limit to the size of macro variables is in the documentation. Not really going to get around it much. If you say what you are actually attempting to do there are likely much better ways to accomplish the task.

[Kurt_Bremser]

If you want to add quotes and commas, do this:

%if &i. > 1 %then ,;
"%cmpres(%sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,Valuenum)))))"

[Tom]

There are ways to generate code that is longer then 65k bytes, but that might just hit a different limit imposed by the database you are trying to query.  The best answer is to upload the list to the database and do the query there.  Check with the DBA for the database how you can create temporary tables in the database you can use to store the list of values.

Here are ways to generate longer strings. 

1) Nested macro variable references.  Set a series of shorter macro variables, let's call them MVAR1, MVAR2, ... and then set SEARCHMACRO to the string &mvar1,&mvar2,.... so that when you evaluate &SEARCHMACRO all of the nested macro variables are evaluated.

data _null_;
  length str $32767 ;
  set vallist end=eof;
  retain n str;
  str = catx(',',str,quote(trim(valuenum),"'"));
  if (eof or (length(str) > 32500)) then do;
    n+1;
    call symputx(cats('mvar',n),str);
    str=' ';
  end;
  if eof then do;
    do n=1 to n;
      str=catx(',',str,cats('&mvar',n));
    end;
    call symputx('searchmacro',str);
  end;
run;

2) Write the code to a FILE.

filename code temp;
data _null_;
   file code ;
  set vallist;
  if _n_=1 then put put 'select * from mytable where id in ('  @;
  else put ',' @;
  str = quote(trim(valuenum),"'");
  put str ;
  if eof then put ');';
run;
proc sql;
%include code ;
quit;

3) Variation on (2) only using CALL EXECUTE() instead of actually writing a file.

PS  Do not use STRIP() before quoting the value of a character variable.  That will remove leading spaces and result in quoted strings that will NOT match the original values.  Just use TRIM() as the trailing spaces are ignored by SAS string comparisons.

[acordes]

@Tom , but the first option you present still falls into the length-restriction-trap, right?

[Tom]

---

@acordes wrote:

@Tom , but the first option you present still falls into the length-restriction-trap, right?

---

No.  Each macro variable will be shorter than 32K bytes.  And unless you have millions of items in the list the macro variable with the list of references to the other macro variables will be very short.  So if you needed 320K bytes to expand the whole list of codes then you would make 11 macro variables. 10 that are 32K each and one that is at most 100 bytes long.

Demo:

844  data _null_;
845    call symputx('mvar1','a,b,c');
846    call symputx('mvar2','d,e,f');
847    call symputx('list','&mvar1,&mvar2');
848  run;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


849  %put LIST = %superq(list);
LIST = &mvar1,&mvar2
850  %put LIST = &list;
LIST = a,b,c,d,e,f

[acordes]

Ok, understood. Nice

Your second code for me to work requires some ammendment. There is a typo error with 2 put statements and I think end=eof is missing as well. 

data ValList (drop=ValueList);
do ValueList = 100 to 1000000 by 100;
valuenum=put(ValueList,6.);
id=valuenum;
output;
end;
run;

filename code temp;
data _null_;
  file code ;
  set vallist end=eof;
  length str $32767 ;
  if _n_=1 then put 'create table oks as select * from vallist where id in ('  @;
  else put ' ' @;
  str = quote(trim(valuenum));
  put str ;
  if eof then put ');';
run;

proc sql;
%include code ;
quit;

[Tom]

You will need to flesh out the full SQL statement (or more) that you want to generate.

If you want to see that code you generated you can do some combination of:
1) Just use FILE LOG instead of making an actual file to debug the code generation.

2) Read the file back in and dump it to the log.

data _null_;
  infile code;
  input;
  put _infile_;
run;

3) Add the / SOURCE2 option to the %INCLUDE statement so the code is included in the log when it runs.

[buddha_d]

Tom, the code works, but I have couple of questions
1. How could I use this to query against a DB? Incase of regular macro variable, I could use 

Where Valuenum (column name) in (&Valuenum.). 

2. When I use 

data _null_;
infile code;
input;
put _infile_;
run;

the above I get the values in double quotes, but leading spaces are there and commas are missing as shown below.  Thanks for helping me 

[Tom]

The leading spaces are there because you put them into your test data by using the PUT() function without either using the LEFT() function to remove them or the -L format modifier to left align the generated strings.  To fix this make sure the set of values you have in the dataset match the values you want to find in the remote database.  You could possibly remove them in the process of generating the code, but that would just mask the original mistake of including them in the first place in the dataset with the list of values to find.

If you used the code I provided that passed the QUOTE() function the second argument then the values would be quoted with single quotes instead of double quotes.  Try it yourself:

data test;
  set sashelp.class (obs=3);
  length str1 str2 $20 ;
  str1=quote(trim(name));
  str2=quote(trim(name),"'");
run;

proc print;
 var name str1 str2;
run;

Obs     Name        str1         str2

 1     Alfred     "Alfred"     'Alfred'
 2     Alice      "Alice"      'Alice'
 3     Barbara    "Barbara"    'Barbara'

If you want to generate a pass thru SQL statement then do that.  Sounds like you might want to generate this type of statement you could run in PROC SQL.  Here ORACLE is the name of the connection you created with the CONNECT statement in the PROC SQL step.  WANT is the name of the SAS dataset you want to create.   MYSCHEMA.MYDBTABLE is the name of the table in the remote database you are querying.  MYVAR is the name of the variable in that table that you are using to filter on.

create table want as 
select * from connection to oracle 
( select * from myschema.mydbtable where myvar in 
('001'
,'002'
,....
)
);

[buddha_d]

Thank Tom for that explanation. 

How do I give myvar (column name) for macro variable with your code for DB?

Could you please comment on that? 

create table want as
select * from connection to oracle
( select * from myschema.mydbtable where myvar in
('001'
,'002'
,....
)
);

[Tom]

You type it into the code you are using to generate the code.  Note that you could have the code generate a reference to a macro variable ('... from &tablename .... ') and set the value into the macro variable (%let tablename=myschema.mydbtable;) before including the generated code.

What ever method works for you. 

If the values of the variable never have actual quotes you can skip the QUOTE function and just write the opening an closing quotes as part of the PUT statement.  Make sure to backup over the trailing space that PUT will add after it writes a variable's value.

data _null_;
   file code ;
   set list end=eof;
   if _n_=1 then put
 'create table want as'
/'select * from connection to oracle'
/'( select * from myschema.mydbtable where myvar in'
/'(' @
   ;
   else put ',' @;
   put "'" value +(-1) "'" ;
  if eof then put 
 ')'
/');'
  ;
run;

[buddha_d]

Hi Tom, Sorry for the delayed response. The code is running without errors with your sample code, but it is not generating any table to look at the data. Could you help me with that please? 

I ran the below code, but I didn't get any want table created. 

data _null_;
file code ;
set list end=eof;
if _n_=1 then put
'create table want as'
/'select * from connection to oracle'
/'( select * from myschema.mydbtable where myvar in'
/'(' @
;
else put ',' @;
put "'" value +(-1) "'" ;
if eof then put
')'
/');'
;
run;

Thanks,

Buddha.

[Kurt_Bremser]

You create a file with code in it. To execute the code, you need to %INCLUDE the file in an appropriate location (as you create SQL code, it must be included in a PROC SQL).