topic Re: How to create a macro variable which exceeds length of 65535 to query DB with PassThru? in SAS Programming

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

buddha_d — Wed, 07 Sep 2022 18:01:07 GMT

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.

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

PaigeMiller — Wed, 07 Sep 2022 17:16:11 GMT

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.

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

ballardw — Wed, 07 Sep 2022 17:18:17 GMT

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.

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

Kurt_Bremser — Wed, 07 Sep 2022 17:47:42 GMT

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

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

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

Tom — Wed, 07 Sep 2022 18:43:35 GMT

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.

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

acordes — Thu, 08 Sep 2022 13:33:35 GMT

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

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

Tom — Thu, 08 Sep 2022 13:48:27 GMT

@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

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

acordes — Thu, 08 Sep 2022 13:53:37 GMT

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;

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

Tom — Thu, 08 Sep 2022 14:29:20 GMT

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.

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

buddha_d — Thu, 08 Sep 2022 17:55:20 GMT

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

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

Tom — Thu, 08 Sep 2022 18:13:19 GMT

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'
,....
)
);

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

Reeza — Thu, 08 Sep 2022 19:56:18 GMT

The premise of Tom's solution is that you can generate the full query to a text file and then %include that code.

Here's a fully worked example.

*list of ages to choose from;
data ageSelected;
input Age;
cards;
10
13
15
;;;;
run;

*create macro variable;
proc sql;
select age into :ageList separated by ", " from AgeSelected;
quit;

*query with macro variable pass to the WHERE statement;
proc sql;
create table want as
select * from sashelp.class
where age in (&ageList);
quit;


data _null_;

*input data set is the subset list;
*EOF tells SAS which is the last record of the file;
set ageSelected end = eof ;

*create text file to store code;
file '/home/fkhurshed/Demo1/query.sas';

*put main portion of the query, excluding list of values;
if _n_ =1 then do;
put  'proc sql; create table want as select * from sashelp.class where age in(';
end;

*ouput list of values;
put age;
*output comma except for last record;
if not eof then put ', ';

*if last record end the query;
if eof then put '); quit;';

run;

*run the query - probably a good idea to open first and verify the query is correct;
%include '/home/fkhurshed/Demo1/query.sas';

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

buddha_d — Thu, 08 Sep 2022 19:58:48 GMT

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'
,....
)
);

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

Tom — Thu, 08 Sep 2022 20:11:51 GMT

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;

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

Reeza — Thu, 08 Sep 2022 20:40:36 GMT

data _null_;

*input data set is the subset list;
*EOF tells SAS which is the last record of the file;
set valueList end = eof ;

*put main portion of the query, excluding list of values;
if _n_ =1 then do;
call execute(  'proc sql; create table want as
select * from connection to oracle
( select * from myschema.mydbtable where myvar in
(');
end;

*ouput list of values;
call execute(quote(put(ValueList, 6. -l)));
*output comma except for last record;
if not eof then call execute(', ');

*if last record end the query;
if eof then call execute( ')); quit;');

run;

Another option above - may need to check the brackets at the end.

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

Patrick — Sun, 11 Sep 2022 04:03:38 GMT

The "traditional" way for doing this once your filter table reaches a certain size is:

1. upload your filter table into the database (eventually as a temporary table)

2. inner join that executes fully in-database

3. load result-set back to SAS

Above required of course that you are allowed to write to the database as well as create and drop tables in at least the temp space.

If you need to stick with the macro variable then what I've seen done in the past is the use of some "sqlpump" macro that splits-up your query into junks (one junk below 32KB) and then combines the result sets in SAS (Proc Append). This requires of course some SAS macro coding.

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

buddha_d — Sun, 11 Sep 2022 23:43:04 GMT

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.

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

Kurt_Bremser — Sun, 11 Sep 2022 23:48:34 GMT

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

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

buddha_d — Sun, 11 Sep 2022 23:56:47 GMT

Hi Patrick,

Thanks for the suggestions. The environment that I am using doesn't allow me to create temporary table to query against the table.

Thanks

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

buddha_d — Mon, 12 Sep 2022 00:53:53 GMT

Hi Kurt,

When I am using pass-thru and where statement with field_name in (%include code;) , SAS is throwing error and that statement cannot be valid.

thanks