cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
GDy1
‎02-02-2022
  • 6 Posts
  • 0 Likes Given
  • 0 Solutions
  • 0 Likes Received

Re: SQL output as input to 2nd SQL

by Calcite | Level 5 in New SAS User
‎01-24-2022 07:37 PM
‎01-24-2022 07:37 PM
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; ... View more

Re: SQL output as input to 2nd SQL

by Calcite | Level 5 in New SAS User
‎01-24-2022 04:32 PM
‎01-24-2022 04:32 PM
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.   ... View more

Re: SQL output as input to 2nd SQL

by Calcite | Level 5 in New SAS User
‎01-24-2022 03:20 PM
‎01-24-2022 03:20 PM
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; ... View more

Re: SQL output as input to 2nd SQL

by Calcite | Level 5 in New SAS User
‎01-24-2022 11:22 AM
‎01-24-2022 11:22 AM
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. ... View more

Re: SQL output as input to 2nd SQL

by Calcite | Level 5 in New SAS User
‎01-24-2022 11:02 AM
‎01-24-2022 11:02 AM
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? ... View more

SQL output as input to 2nd SQL

by Calcite | Level 5 in New SAS User
‎01-20-2022 10:31 PM
‎01-20-2022 10:31 PM
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; ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎02-02-2022 11:29 PM
Quick links