CV2VIEW Procedure

Example 2: Converting a Library of View Descriptors for a Single DBMS

Features:

Statements:: PROC CV2VIEW; FROM_LIBREF=; TO_LIBREF=; SAVEAS=; SUBMIT

In this example PROC CV2VIEW converts all Oracle view descriptors in the input library into 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 PROC CV2VIEW generates are both submitted and saved to an external file named SQL.SAS.

The SAVEAS= statement causes all generated SQL for all Oracle view descriptors to be stored in the MANYVIEW.SAS file.

If the underlying DBMS is Oracle or DB2, the CV2VIEW procedure adds the PRESERVE_TAB_NAMES= option to the embedded LIBNAME statement. You can then use CV2VIEW to access those tables with mixed-case or embedded-blank table names.

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 these 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
  /* PW= */
  USER=ordevxx PATH=OracleV8 PRESERVE_TAB_NAMES=YES;
  QUIT;