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

[buddha_d]

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 

[Tom]

TEST. DEBUG. REPEAT.

Make sure you know what code you want to generate.  Get a simple query to run without any code generation.

Perhaps something like:

proc sql;
connect to oracle .... ;
select * from connection to oracle
(select count(*) as nobs from myschema.mytable)
;
quit;

Then try one that actually tries to filter on the variable you want to use.

proc sql;
connect to oracle .... ;
select * from connection to oracle
(select count(*) as nobs from myschema.mytable
 where myvar in ('value1','value2')
;
quit;

Once you understand what query you want to run then you can work on the code to generate that query.  Run the code to generate it.  Look at it.  Does it follow the pattern of the working code?  Is it valid syntax?  Does it have missing parentheses, missing quotes, missing commas, extra commas, etc.

[Reeza]

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.

---

[Reeza]

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';

[buddha_d]

Hi Reeza,

           I checked my query to match your query. I can't resolve "STR" variable (macro variable) which would have all the values. If this resolves then I will be good. 

Your query with put age gets resolved properly, but mine with DB query is not working. 

*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;';

thanks,

[Reeza]

---

@buddha_d wrote:

Hi Reeza,

           I checked my query to match your query. I can't resolve "STR" variable (macro variable) which would have all the values. If this resolves then I will be good. 

Your query with put age gets resolved properly, but mine with DB query is not working. 

Without the query or the error all I can say is you're doing something wrong. 

Since you did some type conversion above, you may need some additional quotes in the statement but as @Tom indicates, test it with a small amount first and then build it out fully so you can see the code. FYI - the log should have the code generated for you to check as well. 

[buddha_d]

Hi Acordes 

       Thanks for responding. I tried your code, I don't get STR value from PUT statement. I just didn't notice that before. Anyway, we can get it and let me remind you that my macro variable string is over 66000 bytes and can't accommodate in a regular string. 

Thanks

[Patrick]

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.

[buddha_d]

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

[SASKiwi]

Most databases have a limit on the size of WHERE statements. What you are building may not work for a long time if it keeps growing. If would be worth telling management that the lack of temporary table permissions is preventing you building a long term, robust solution.