I am a relative beginner to SAS. I am working with previously written code that creates a comma separated macro list, and then uses this in a passthrough Oracle SQL query like so (this is a simplified example): proc sql;
select id into :idlist separated by ',' from currentcustomerpool;
connect to oracle (&credentials.);
create table customerinfo as
select * from connection to oracle (
select * from custtables.customerAccountInfo
where customer in (&idlist.) and age > 21
);
disconnect from oracle;
quit; Using the macrovar idlist imposes a 1000 id limit because of an inherent SQL limitation on IN clauses. I would like to get rid of the idlist macrovar by using a temporary SQL table that I could join to or use as part of a subquery inside the SQL passthrough block. In the actual codebase, I have to keep the passthrough blocks; in other words, I cannot use a solution that moves away from passthrough. I have attempted to come up with a sample piece of code to accomplish this like so (after a bit of googling): libname tmpdat oracle connection=global dbmstemp=yes dbcommit=0 &credentials.;
proc sql;
connect to oracle (&credentials.);
execute (
CREATE GLOBAL TEMPORARY TABLE tmpcustids (
ID NUMBER
) ON COMMIT PRESERVE ROWS
) by oracle;
insert into tmpdat.tmpcustids select id from currentcustomerpool;
create table customerinfo as
select * from connection to oracle (
select * from custtables.customerAccountInfo
where customer in (select * from tmpcustids) and age > 21
);
disconnect from oracle;
quit; This seems to "work" with no errors, but the resulting query gives a table with 0 rows, as if no actual data was inserted into the tmpcustids table on the SQL side of things. If I explicitly insert values using passthrough into tmpcustids, the query works and gives rows back. However, I would really prefer to avoid having to make a macro that creates a dynamic insert statement in another execute block. Is there a simple solution to this problem? Thanks in advance for the help.
... View more
