290 Example 2: Converting an Microsoft SQL Server View Descriptor Chapter 17
replace all;
submit;
run;
PROC CV2ODBC generates the following PROC SQL statements.
PROC SQL DQUOTE=ANSI;
CREATE VIEW AS4LIB.V9_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 Microsoft 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’;
