CV2ODBC Procedure

Examples

Example 1 - Converting an AS/400 View Descriptor

In this example, PROC CV2ODBC converts the Version 6 AS4ACC.INVOICE view descriptor to an ODBC view, AS4LIB.V8_INVOICE. The PROC SQL statements that are generated by PROC CV2ODBC are both automatically submitted and saved to an external file named AS4_INVOICE.SAS.

libname as4acc 'd:\lib\as4\acc';
libname as4lib 'd:\lib\as4\lib';

proc cv2odbc dbms=as400;
   from view=as4acc.invoice;
   to view=as4lib.V8_invoice;
   user='testuser';
   password='testpass';
   dsn='IBM AS/400 Database';
   save ='as4_invoice.sas';
   replace all;
   submit;
run;

PROC CV2ODBC generates the following PROC SQL statements.

PROC SQL DQUOTE=ANSI;
 CREATE VIEW AS4LIB.V8_INVOICE AS
   SELECT
     "INVOICENUM" AS INVOICE INFORMAT= 11.0
        FORMAT= 11.0 LABEL='INVOICENUM' ,
     "BILLEDTO" AS CUSTOMER INFORMAT= $8.
        FORMAT= $8. LABEL= 'BILLEDTO' ,
     "AMTBILLED" AS AMOUNT INFORMAT= DOLLAR20.2
        FORMAT= DOLLAR20.2 LABEL= 'AMTBILLED' ,
     "BILLEDBY" AS BILLEDBY INFORMAT= 11.0
        FORMAT= 11.0 LABEL= 'BILLEDBY' ,
     "BILLEDON" AS BILLEDON INFORMAT= MMDDYY8.
        FORMAT= MMDDYY8. LABEL= 'BILLEDON' ,
     "PAIDON" AS PAIDON INFORMAT= DATE7.
        FORMAT= DATE7. LABEL= 'PAIDON'
   FROM _CVLIB_."INVOICE"
      (DBCONDITION = "WHERE PAIDON IS NOT NULL "
       DBINDEX = NO
       DBCOLUMNS = 'INVOICENUM, BILLEDTO,
         AMTBILLED, BILLEDBY, BILLEDON,
         PAIDON'
       SASDATEFMT = ("BILLEDON"= MMDDYY8.
         "PAIDON"= DATE7.)
       )
   USING LIBNAME _CVLIB_ ODBC
   AUTOCOMMIT = YES
   NOPROMPT="DSN=IBM AS/400 Database;
     UID=testuser;PWD=testpass;NAM=1;XDYNAMIC=0;
     LIBVIEW=0;DBQ=SASDEMO"
;
QUIT;

Example 2 - Converting an MS SQL Server View Descriptor

In this example, PROC CV2ODBC converts the Version 6 MSQACC.ORDER view descriptor to an ODBC view, MSQLIB.ORDERS. The PROC SQL statements that are generated by PROC CV2ODBC are saved to an external file named MSSQL.SAS, but are not automatically submitted.

libname msqacc 'd:\lib\msq\acc';
libname msqlib 'd:\lib\msq\lib';

proc cv2odbc dbms=mssql;
  from view = msqacc.order (pw=testpass);
  to view = msqlib.orders;
  user = 'testuser';
  pass = 'testpass';
  dsn = 'Microsoft SQL Server Database';
  save = 'mssql.sas';
  replace all;
  run;

PROC CV2ODBC generates the following PROC SQL statements.

PROC SQL DQUOTE=ANSI;
 CREATE VIEW MSQLIB.ORDERS(PW=TESTPASS) AS
   SELECT
     "ordernum" AS ORDERNUM INFORMAT= 11.0
       FORMAT= 11.0 LABEL= 'ordernum' ,
     "fabriccharges" AS AMOUNT INFORMAT= DOLLAR22.2
       FORMAT= DOLLAR22.2 LABEL= 'fabriccharges' ,
     "shipto" AS SHIPTO INFORMAT= $8. FORMAT= $8.
       LABEL= 'shipto' ,
     "dateordered" AS DATEORDE
       INFORMAT= DATETIME21.2
       FORMAT= DATETIME21.2 LABEL= 'dateordered' ,
     "shipped" AS SHIPDATE INFORMAT= MMDDYY10.
       FORMAT= MMDDYY10. LABEL= 'shipped'
,    "takenby" AS TAKENBY INFORMAT= 11.0
       FORMAT= 11.0 LABEL= 'takenby' ,
     "processedby" AS PROCESSE INFORMAT= 11.0
       FORMAT= 11.0 LABEL= 'processedby'
   FROM _CVLIB_."orders"
    (DBCONDITION = "WHERE (takenby=456910 or 
        takenby=234967 or 
        dateordered='1988-11-03' or 
        dateordered='12/23/1988')
        and shipped is not null"
     SASDATEFMT = ("dateordered"=DATETIME21.2
       "shipped"=MMDDYY10.)
    )
   USING LIBNAME _CVLIB_ ODBC
   PRESERVE_TAB_NAMES = YES
   NOPROMPT="DSN=Microsoft SQL Server Database;
    SERVER=dbipc1.pc.sas.com;
    UID=testuser;PWD=testpass;DATABASE=sample";
QUIT;

Chapter Contents
Previous
Next
Top of Page