• Print |
• E-mail  |
• Bookmark

A new procedure, PROC CV2VIEW, has been added to the SAS System to allow
users to convert their existing SAS/Access view descriptors to PROC SQL
libname views.

BASIC SYNTAX:
PROC CV2VIEW
 1. DBMS =  supported dbms;
 2. FROM_VIEW | FROM_LIBREF =  name of individual view or library;
 3. TO_VIEW | TO_LIBREF = name of output view | name of output library ;
 4. SAVEAS = external file name;
 5. SUBMIT
 6. REPLACE <ALL> | VIEW | FILE ;
RUN;

NOTES:
1. Use "ALL" to convert ALL view descriptors from "ALL" dbms's.
2. FROM_VIEW - Specify the name of an existing view descriptor
   ---------   (libref.memname).  This option can not be used in
               conjunction with DBMS = ALL, TO_LIB or FROM_LIB.  It
               must be used in conjuction with TO_VIEW.
   FROM_LIB  - Specify the name of an assigned library (libref) that
   --------    contains the view descriptors that you want to convert.
               If DBMS = ALL is specified all the view descriptors
               created by any supported DBMS will be converted. If
               DBMS = dbms only those view descriptors created by that
               DBMS will be converted. This option is NOT valid with
               FROM_VIEW or TO_VIEW and MUST be paired with TO_LIB.
3. TO_VIEW   - specify any legal PROC SQLl view name (libref.memname).
   -------     This will be the name of the converted view.
   TO_LIB    - Specify the name of an assigned library (libref) where
   ------      you want to store your converted views.   When FROM_LIB
               and TO_LIB are selected, the output view names will be
               generated using the names of the input view descriptors
               as the names for the output views. If you want to
               individually name your views you must use
               FROM_VIEW/TO_VIEW. If the name of your output view
               already exists you must use the REPLACE VIEW option to
               overwrite or the program will not convert that view.
4. SAVEAS    - The option allows you to save the generated PROC SQL
   ------      statements in a file. You may check or modify the
               generated statements as needed. You may also want to save
               these statements and submit them on other platforms.
               Please note that certain sql statements will not be
               dumped to the file and will be replaced with comments.
               This applies to any statement that would reveal a
               password that is stored with the view descriptor.
5. SUBMIT    - If SAVEAS= is given, PROC CV2VIEW will not automatically
   ------      submit the generated statements.  However, you may
               specify the SUBMIT option to force it.  If SAVE AS= is
               not given, you do not have to specify the option.
6. REPLACE   - If the TO VIEW= view file already existed, you may
   -------     specify REPLACE VIEW to override the view file.  If
               REPLACE FILE is specified, the SAVEAS= file will be
               overwritten with new generated statements. If REPLACE
               FILE is not specified, the new generated statements will
               be appended to the file.  REPLACE or REPLACE ALL turns on
               both REPLACE FILE and REPLACE VIEW.

   EXAMPLE:
   libname input '/u/jaonei/pp/m800/dev/ora/vBin/';
   libname output '/u/jaonei/pp/m800/dev/ora/vOut/';
   Proc cv2view dbms=oracle;
   from_view = input.myview;/* source view is password protected */
   to_view = output.newview;
    saveas = '/u/jaonei/pp/m800/dev/ora/vSql/sql.sas';
   submit;
   replace file;
   run;

   Generated SQL syntax
   /* 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='ORACLEV7' PRESERVE_TAB_NAMES=YES;


A Technical Support hot fix for Release 8.2 TSLEVEL TS2M0 for this
issue is available at:

http://www.sas.com/techsup/download/hotfix/82_sbcs_prod_list.html#005868

For customers running SAS with Asian Language Support (DBCS), this
hot fix should be downloaded from:

http://www.sas.com/techsup/download/hotfix/82_dbcs_prod_list.html#005868

Operating System and Release Information

A fix for Release 8.2 (TS2M0) for this issue is available at:

A fix for Release 8.2 (TS2M0) with Asian Language Support (DBCS) for this issue is available at: