The CV2VIEW Procedure

Example 1: Converting an Individual View Descriptor

In this example, PROC CV2VIEW converts the MYVIEW view descriptor to the SQL view NEWVIEW. When you use ALTER, READ, and WRITE, the MYVIEW view descriptor is protected again alteration, reading, and writing. The PROC SQL statements that PROC CV2VIEW generates are submitted and saved to an external file named SQL.SAS.

libname input '/username/descriptors/';
libname output '/username/sqlviews/';

proc cv2view dbms=oracle;
from_view = input.myview (alter=apwd);  
to_view = output.newview;
saveas = '/username/vsql/sql.sas';
submit;
replace file;
run;

PROC CV2VIEW generates these PROC SQL statements.

/* SOURCE DESCRIPTOR: MYVIEW */
PROC SQL DQUOTE=ANSI;
  CREATE VIEW OUTPUT.NEWVIEW
  ( 
/* READ=  */
/* WRITE= */
/* ALTER= */
  LABEL=EMPLINFO
  )
  AS SELECT 
     "EMPLOYEE  " AS EMPLOYEE INFORMAT= 5.0 FORMAT= 5.0 
                  LABEL= 'EMPLOYEE  ' ,
     "LASTNAME  " AS LASTNAME INFORMAT= $10. FORMAT= $10. 
                  LABEL= 'LASTNAME  ' ,
     "SEX       " AS SEX INFORMAT= $6. FORMAT= $6. 
                  LABEL= 'SEX       ' ,
     "STATUS    " AS STATUS INFORMAT= $9. FORMAT= $9. 
                  LABEL= 'STATUS    ' ,
     "DEPARTMENT" AS DEPARTME INFORMAT= 7.0 FORMAT= 7.0 
                  LABEL= 'DEPARTMENT' ,
     "CITYSTATE " AS CITYSTAT INFORMAT= $15. FORMAT= $15. 
                  LABEL= 'CITYSTATE ' 
  FROM _CVLIB_."EMPLINFO" 
  USING LIBNAME _CVLIB_
  Oracle
/* PW=  */
  USER=ordevxx PATH=OracleV8 PRESERVE_TAB_NAMES=YES;
  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. You can then use CV2VIEW to access those tables with 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 re-create the view. [cautionend]

Top of Page