Creating a view in a sql database using Proc SQL

[ram_adhikari_jacks_sdstate_edu]

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

[RW9]

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.

[skillman · Accepted solution]

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;

[ram_adhikari_jacks_sdstate_edu]

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.

[JBailey]

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;