topic Re: Creating a view in a sql database using Proc SQL in SAS Data Management

Creating a view in a sql database using Proc SQL

ram_adhikari_jacks_sdstate_edu — Thu, 14 May 2015 13:56:16 GMT

Hi Everybody!

I am connected to a database using a odbc connection and I need to create a view in database. I have written the following code:

libname test odbc noprompt="uid=username;

pwd=password;

dsn=mydatabase;"

stringdates=yes;

Proc Sql;

create view test.table1 as

select * from orders;

Quit;

I got the following error message

ERROR: The CREATE VIEW statement cannot be processed directly by the database.

ERROR: Create View failed.

Could somebody help me create the view in the database.

Your help is highly appreciated.

Ram

Re: Creating a view in a sql database using Proc SQL

RW9 — Thu, 14 May 2015 14:23:45 GMT

Personally I would default back to pass-through:

proc sql;

connect to db (...);

execute by db (create view ...);

disconnect from db;

quit;

(Note syntax is from memory so may need a bit of tweaking).

This would send the SQL through to the DB to process.

Re: Creating a view in a sql database using Proc SQL

skillman — Thu, 14 May 2015 16:26:31 GMT

This explicit pass-through SQL (vs implicit in your original post) works fine for me:

PROC SQL;

connect to oracle (path='' user='' password='');

execute( create view LEAD_ACCOUNT_V as

select *

from "Lead_Account"

) by Oracle;

quit;

Re: Creating a view in a sql database using Proc SQL

ram_adhikari_jacks_sdstate_edu — Thu, 14 May 2015 16:56:54 GMT

Skillman,

There was some issues with my connectivity. Looking at your code I was able to fix it. Now both the approaches (one that I posted earlier ) and you provided work. Thank you and I appreciate your help.

Re: Creating a view in a sql database using Proc SQL

JBailey — Tue, 26 May 2015 17:57:38 GMT

Hi Ram,

If you are using SAS 9.4, you can do this with the the DBIDIRECTEXEC system option. This code uses ACCESS to PostgreSQL. It is worth noting that the explicit pass-thru method gives you more control and may be preferable, but this does work. I ran this with SAS 9.4M2.

libname mypg postgres server=localhost database=mydb user=myuser pw=mypasswd;

proc sql;

create table mypg.cars

as select *

from sashelp.cars;

quit;

options dbidirectexec sastrace=',,,d' sastraceloc=saslog nostsuffix;

/*create the view */

proc sql;

create view mypg.jeeps as

select *

from mypg.cars

where make='Jeep';

quit;

/* see if it worked. It should return 3 */

proc sql;

select count(*) from mypg.jeeps;

quit;