How to SQL Pass-through in SAS, create view or table

Question

I am trying to create a View on the database through SAS, SQL passthrough the following way

PROC sql; 
CONNECT TO odbc AS myuser(dsn=galaxy);              
EXECUTE(
CREATE VIEW DB.SCHEM.table AS
    SELECT * FROM connection to myuser;
    (SELECT * FROM DB.SCHEM.table2)
)by myuser;
QUIT;

Howeever, I get the following errors

ERROR: CLI prepare error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'select'. : [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ')'.

What are you trying to do ? Why are you doing your second select statement ? — Arnaud Peralta
I am trying to create a view in the database by pass through., even if I have it like code execute( create View DB.SCHEM.table as select * from DB.SCHEMA.table2 code it gives an error that it doesnt allow specifying the database name as a prefix. — Anand
What is the simple way to create a table or View from another table on the database via Pass-through? — Anand

Richard Richard · Accepted Answer · 2019-04-10T11:40:42

You have an extraneous SELECT * FROM connection to myuser; inside the EXECUTE. The code inside EXECUTE is submitted directly to SQL server, and your extraneous code is invalid.

Here is the SAS pass-through corrected. I would advise against using table as part of view name. Note: You will see an error message if your SQL Server credentials do not have the data base grants needed to create a view.

EXECUTE(
  CREATE VIEW DB.SCHEM.table AS
  SELECT * FROM DB.SCHEM.table2
) by myuser ;

From SQL Server docs for CREATE VIEW

CREATE [ OR ALTER ] VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]   
[ WITH <view_attribute> [ ,...n ] ]   
AS select_statement   
[ WITH CHECK OPTION ]   
[ ; ]

How to SQL Pass-through in SAS, create view or table

1 Answers