DATA Step, Macro, Functions and more

converting SQL pass through code having with function to normal SQL

Reply
Contributor
Posts: 74

converting SQL pass through code having with function to normal SQL

Hi All,

 

We have sql pass-through code as below and get data using ODBC connection. can some help and suggest how to use with on pre-assigned libraries.

 

proc sql;
 connect to odbc as mydb
 (datasrc=dwprdsvc user=xxxxxxxxxxxx password=xxxxxxxxxxxxxxxxx);
create table Var_tmp as select * from connection to mydb (
 SET QUOTED_IDENTIFIER OFF
 execute executeoraclequery @query = 
%bquote(")
/* all the latest scenario forecasts */
with LATEST as (
select query...
),

DATA as (
select query...
),

 

now i have pre-assigned library and i'm not able to convert it to required format...

when i converted as below

 

proc sql;

     create table Oxford_Eco_Var_tmp as select * from  (

   with LATEST as (

select query...

 ),

DATA as (
select query...
),

 

i'm getting error as below.

proc sql;

27              create table Var_tmp as select * from  (

28                         with LATEST as (

                          __

                          22

                          201

ERROR 22-322: Syntax error, expecting one of the following: ',', ANSIMISS, CROSS, FULL, INNER, JOIN, LEFT, NATURAL, NOMISS, RIGHT. 

 

ERROR 201-322: The option is not recognized and will be ignored.

 

Please suggest and let me know.

 

Thanks in advance.

Super User
Posts: 2,519

Re: converting SQL pass through code having with function to normal SQL

Posted in reply to maheshtalla

Your question makes no sense to me.

1. WITH creates a sort of a code shortcut, a view in a sense. It has nothing to do with libnames

2. WITH is not supported in SAS SQL

What are you trying to achieve?

Contributor
Posts: 74

Re: converting SQL pass through code having with function to normal SQL

@ChrisNZ

 

the initial query work well with SQL-passthrough with odbc connection details with in that...but i have setup libraries to the database and pre-assigned them. now I modified the code accordingly and its not working.

Super User
Posts: 2,519

Re: converting SQL pass through code having with function to normal SQL

[ Edited ]
Posted in reply to maheshtalla

You code should then look like this:

 

 

libname MYLIB odbc datasrc=dwprdsvc user=xxxxxxxx password=xxxxxxxxx;

proc sql;
  connect using MYLIB;
  create table Oxford_Eco_Var_tmp as select * from connection to MYLIB (
    with LATEST as ( ... )
    etc
  );
 quit;

 

Contributor
Posts: 74

Re: converting SQL pass through code having with function to normal SQL

Posted in reply to maheshtalla

Hi all,

 

i'm able to resolve this issue, Basically the SQL-pass through i mentioned was using the execute query as below which submits the code to database which is having stored procedures and return back the results. All-though its nothing to do with pre-assigned libraries or SQL-pass thru it was probelm with stored procs, So we had same stored procs copied and it worked.

 

execute executeoraclequery @query = 

 

Thanks.

Ask a Question
Discussion stats
  • 4 replies
  • 81 views
  • 2 likes
  • 2 in conversation