The CV2VIEW Procedure Example 2: Converting a Library of View Descriptors for a Single DBMS 283
QUIT;
The REPLACE FILE statement causes an existing file named SQL.SAS to be
overwritten. Without this statement the text would be appended to SQL.SAS if the user
has the appropriate privileges.
The LABEL value of
EMPLINFO
is the name of the underlying database table that is
referenced by the view descriptor.
If the underlying DBMS is Oracle or DB2, the CV2VIEW procedure adds the
PRESERVE_TAB_NAMES= option to the embedded LIBNAME statement to enable you
to access those tables that have mixed-case or embedded-blank table names.
Note: This SQL syntax fails if you try to submit it because the PW field of the
LIBNAME statement is replaced with a comment in order to protect the password. The
ALTER, READ, and WRITE protection is commented out for the same reason. You can
add the passwords to the code and then submit the SQL to recreate the view.
Example 2: Converting a Library of View Descriptors for a Single DBMS
In this example, PROC CV2VIEW converts all of the Oracle view descriptors in the
input library into PROC SQL views. If an error occurs during the conversion of a view
descriptor, the procedure moves to the next view. The PROC SQL statements that are
generated by PROC CV2VIEW are both submitted and saved to an external file named
SQL.SAS.
libname input ’/username/descriptors/’;
libname output ’/username/sqlviews/’;
proc cv2view dbms=oracle;
from_libref = input;
to_libref = output;
saveas = ’/username/vsql/manyview.sas’;
submit;
run;
PROC CV2VIEW generates the following PROC SQL statements for one of the views.
/* SOURCE DESCRIPTOR: PPCV2R */
PROC SQL DQUOTE=ANSI;
CREATE VIEW OUTPUT.PPCV2R
(
LABEL=EMPLOYEES
)
AS SELECT
"EMPID " AS EMPID INFORMAT= BEST22. FORMAT= BEST22.
LABEL= ’EMPID ’ ,
"HIREDATE " AS HIREDATE INFORMAT= DATETIME16. FORMAT= DATETIME16.
LABEL= ’HIREDATE ’ ,
"JOBCODE " AS JOBCODE INFORMAT= BEST22. FORMAT= BEST22.
LABEL= ’JOBCODE ’ ,
"SEX " AS SEX INFORMAT= $1. FORMAT= $1.
LABEL= ’SEX ’
FROM _CVLIB_."EMPLOYEES" (
SASDATEFMT = ( "HIREDATE"= DATETIME16. ) )
USING LIBNAME _CVLIB_
Oracle
