SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Creating a view in a sql database using Proc SQL

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Creating a view in a sql database using Proc SQL

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


Accepted Solutions
Solution
‎05-14-2015 12:26 PM
SAS Employee
Posts: 85

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

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,430

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

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.

Solution
‎05-14-2015 12:26 PM
SAS Employee
Posts: 85

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

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;

Occasional Contributor
Posts: 18

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

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.

SAS Employee
Posts: 203

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

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1327 views
  • 7 likes
  • 4 in conversation