topic Re: converting SQL pass through code having with function to normal SQL in SAS Programming

converting SQL pass through code having with function to normal SQL

maheshtalla — Tue, 17 Jul 2018 21:03:40 GMT

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 (

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.

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

ChrisNZ — Tue, 17 Jul 2018 21:26:47 GMT

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?

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

maheshtalla — Tue, 17 Jul 2018 21:30:59 GMT

@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.

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

ChrisNZ — Tue, 17 Jul 2018 21:37:42 GMT

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;

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

maheshtalla — Fri, 27 Jul 2018 19:41:00 GMT

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.