BookmarkSubscribeRSS Feed
maheshtalla
Quartz | Level 8

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.

4 REPLIES 4
ChrisNZ
Tourmaline | Level 20

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?

maheshtalla
Quartz | Level 8

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

ChrisNZ
Tourmaline | Level 20

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;

 

maheshtalla
Quartz | Level 8

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1009 views
  • 2 likes
  • 2 in conversation