Date:         Mon, 20 Dec 2010 10:52:03 -0500
Reply-To:     Gerhard Hellriegel <gerhard.hellriegel@T-ONLINE.DE>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Gerhard Hellriegel <gerhard.hellriegel@T-ONLINE.DE>
Subject:      Re: Create View on SQL Server Database with PROC SQL

The problem might be: you want to create a view in DB, but you try to
create one in SAS, but in a ODBC library. If you want to create a view in
SAS, take another library (not a DB-library, but a SAS library like
SASUSER. Other way: use pass-through to your DB with PROC SQL;

A example for pass-through to ODBC (adapted from DB2, but should look
somehow like that):

execute(create view test
          as select * from ....) by odbc;

Maybe that should be preceeded by a CONNECT statement to pass the DB-
options for the connection, like password, userid, ...

Gerhard





On Mon, 20 Dec 2010 10:33:18 -0500, Jack Clark <jclark@HILLTOP.UMBC.EDU>
wrote:

>Hello,
>
>
>
>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
>working fine.
>
>
>
>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;
>
>
>
>proc sql;
>
>   create view wf2.jack_view as
>
>   select *
>
>   from wf2.claims_fy08_dent
>
>   ;
>
>quit;
>
>
>
>369  libname wf2 odbc noprompt=XXXXXXXXXXXXXXXXX bulkload=yes;
>
>NOTE: Libref WF2 was successfully assigned as follows:
>
>      Engine:        ODBC
>
>      Physical Name: wfsql2_clm
>
>370
>
>371  proc sql;
>
>372     create view wf2.jack_view as
>
>373     select *
>
>374     from wf2.claims_fy08_dent
>
>375     ;
>
>ERROR: Create View failed.
>
>376  quit;
>
>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
>
>
>
>Jack Clark
>Senior Research Analyst
>phone: 410-455-6256
>fax: 410-455-6850
>jclark@hilltop.umbc.edu
>
>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.