|Date: ||Mon, 20 Dec 2010 11:10:43 -0500|
|Reply-To: ||Mike Rhoads <RHOADSM1@WESTAT.COM>|
|Sender: ||"SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>|
|From: ||Mike Rhoads <RHOADSM1@WESTAT.COM>|
|Subject: ||Re: Create View on SQL Server Database with PROC SQL|
|Content-Type: ||text/plain; charset="us-ascii"|
Since object references within SQL Server normally reference the database schema as well as the name of the object, I would try something like the following, using SQL Passthrough:
create view dbo.jack_view as
select * from dbo.claims_fy08_dent;
) by odbc;
replacing "dbo" with your actual schema name.
I was successfully able to run an example like this, although with OLEDB rather than ODBC.
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Jack Clark
Sent: Monday, December 20, 2010 10:33 AM
Subject: Create View on SQL Server Database with PROC SQL
I am trying to help a colleague with this task. We have a SQL Server
database that is updated with a SAS program. The SAS program uses the
LIBNAME statement with ODBC to link to the database. This has been
Now, we want to create a VIEW on the SQL Server database (not in SAS)
using SAS code. I tried the syntax below, but get an ERROR which
doesn't tell me much. My colleague has also tried the SQL Passthrough
approach, but says the view gets created in the WORK area of the
database, not as a permanent view.
Can someone suggest how this can be corrected? Thank you.
libname wf2 odbc noprompt="dsn=wfsql2_clm;" bulkload=yes;
create view wf2.jack_view as
369 libname wf2 odbc noprompt=XXXXXXXXXXXXXXXXX bulkload=yes;
NOTE: Libref WF2 was successfully assigned as follows:
Physical Name: wfsql2_clm
371 proc sql;
372 create view wf2.jack_view as
373 select *
374 from wf2.claims_fy08_dent
ERROR: Create View failed.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
Senior Research Analyst
University of Maryland, Baltimore County
Sondheim Hall, 3rd Floor
1000 Hilltop Circle
Baltimore, MD 21250
Please consider the environment before printing this e-mail and/or any attachments.
Confidentiality Notice: This e-mail may contain information that is legally privileged and that is intended only for the use of the addressee(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying of this e-mail, distribution, or action taken in reliance on the contents of this e-mail and/or documents attributed to this e-mail is strictly prohibited. If you have received this information in error, please notify the sender immediately by phone and delete this entire e-mail. Thank you.