SQL output as input to 2nd SQL

[GDy1]

Hello,

I'm new to SAS and I can't find a solution to what I'm trying to do.  I want the output from my 1st SQL from one database to be an input to my 2nd SQL from another database, i.e., 2 different database connections.

The 1st SQL's output are numeric values stored in varchar2 datatype and the 2nd SQL field for the IN clause is also a varchar2 datatype.

I'm trying to use a macro variable called resultdata.  The 1st SQL returned the numeric values separated by comma but they are not in single quotes.   The 2nd SQL returned an ERROR: ORACLE execute error: ORA-01722: invalid number because it was expecting the INPUT to be in single quotes.   Please help.   Thanks in advance.

Below is a sample of my SAS code.   

===========================

proc sql noprint;
connect to oracle (user=XXXXXXXXXXXXXXXXXX pw=XXXXXXXXXXXXXXXXXX 
path="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=sv1)))");
select * into: resultdata separated by ','
from connection to oracle
(
SELECT '852' AS DATA FROM DUAL
union
SELECT '229' AS DATA FROM DUAL
);
disconnect from oracle;
%put &resultdata;
quit;

proc sql;
connect to oracle (user=XXXXXXXXXXXXXXXXXX pw=XXXXXXXXXXXXXXXXXX 
path="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname2)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=sv2)))");
select *
from connection to oracle
(SELECT * FROM table
WHERE ID IN (&resultdata)
);
disconnect from oracle;
quit;

[Tom]

If the values are strings then you want to include quotes around them. Use the optional second argument to the QUOTE() function to have it use the single quotes that your remote DB requires.

select quote(trim(data),"'") into :resultdata separated by ','
from connection to oracle
(...);

[GDy1]

Thanks.  The quote(trim(data),"'") works.   However, the 1st query returned more than 1,000 records and I got the ERROR: ORACLE prepare error: ORA-01795: maximum number of expressions in a list is 1000.

How do I go around this or is there another way or approach to handle what I'm trying to achieve?

[Tom]

The right solution depends on the sizes of the various tables/values.

If the total size of the second table is small enough then perhaps you can just let SAS pull the whole table over and do the subsetting on the SAS side.

In general you want to minimize the amount of data you need to pull out of the remote database into SAS by pushing the subsetting criteria into the remote database.  So if the list of values is too large to include in the query statement you should look into whether you have permission to upload the list of values into a table (probably a temporary table) and then use that table in a query to limit the values returned.

So check with the owner of the second database about how you can upload the values into a temporary table to perform the subsetting.

[GDy1]

The output of the 1st query would be on the average about over thousand records but less than 5K for sure.   Unfortunately, we are not allowed to create temporary tables on the remote database.   

Any other suggestions or ideas to achieve this?   Some examples would be appreciated.  Thanks.

[Tom]

You could try limiting the IN () list you generate to less than 1000 values, but you migth then run into some other limit.

You could run separated queries of 1000 or less values each and then append the results.

To separate the list of values into group you could use something like:

data list2;
  set list;
  group + mod(_n_,1000)=1;
run;

[GDy1]

I'm sorry but I did not understand the suggested solution of grouping.   How can I group the 1st query results by chunks of 1000 records or less?   Let's say the 1st query returned 1,500 records.  The idea is ...

1st chunk:  '1','2','3', ... '1000'

2nd chunk: '1001', '1002', '1003', ... '1500'

Below is what I currently have coded.

proc sql noprint;
connect to oracle (user=XXXXXXXXXXXXXXXXXX pw=XXXXXXXXXXXXXXXXXX 
path="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=sv1)))");
 select quote(trim(data),"'") into: resultdata separated by ','
from connection to oracle
(
SELECT * FROM TABLE <--  this 1st SQL will return more than 1000 records
);
disconnect from oracle;
%put &resultdata;
quit;

proc sql;
connect to oracle (user=XXXXXXXXXXXXXXXXXX pw=XXXXXXXXXXXXXXXXXX 
path="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname2)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=sv2)))");
select *
from connection to oracle
(

SELECT * FROM table
WHERE ID IN (&resultdata) <-- INPUT from the results from 1st query
);
disconnect from oracle;
quit;

[Tom · Accepted solution]

Create a SAS dataset with the list of codes.

Break them into groups.

Use one group to pull some of the data. Repeat for each group.

So change your first SELECT ... INTO to a CREATE TABLE as SELECT .... instead.  Then assign the groups.

proc sql noprint;
connect to oracle (... your connection details here ... ;
create table list as select * from connection to oracle
(select distinct data from sometable)
;
quit;
data list;
  group+1;
  do _n_=1 to 1000 until(eof);
     set list end=eof;
     output;
  end;
  if eof then call symputx('ngroups',group);
run;

Then generate a separate SELECT for each group.  You might for example create a macro that generates code to pull one group and then combine. So perhaps something like this: 

%macro pull(ngroups);
%local i group resultdata;
proc sql;
connect to oracle  (... your connection details here ... );
%do i=1 %to &ngroups;
select quote(trim(data),"'") into: resultdata separated by ','
from list
where group=&group
;
create table group&group as
select *
from connection to oracle
(
SELECT * FROM table
WHERE ID IN (&resultdata) 
);
%end;
quit;
data want;
  set group1-group&ngroups;
run;
%mend;

Which you can then call passing in the calculated number of groups from the first step.

%pull(&ngroups);

[GDy1]

I'm getting the following error:

NOTE: Line generated by the invoked macro "PULL".
98 select quote(trim(data),"'") into: resultdata separated by ',' from list where group=&group ;
_
22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER.

[Tom]

Turn on the MPRINT option and check the SAS code that the macro generated.  Try running the code directly without generating via the macro  until you get the syntax right. Then fix the macro so that it generates code that works.

If you want help debugging you need to show the full statement that the macro generated that SAS is complaining about. Make sure to post the copied text using the INSERT CODE button on the forum editor so that the formatting is preserved since the underscores in the error message should indicate what part of the code SAS got confused by.

[GDy1]

Thanks.  It worked when I changed the lines with &group to &i

%macro pull(ngroups);
%local i group resultdata;
proc sql;
connect to oracle (... your connection details here ... );
%do i=1 %to &ngroups;
select quote(trim(data),"'") into: resultdata separated by ','
from list
where group=&i
;
create table group&i as
select *
from connection to oracle
(
SELECT * FROM table
WHERE ID IN (&resultdata)
);
%end;
quit;
data want;
set group1-group&ngroups;
run;
%mend;