The CV2ODBC Procedure Example 2: Converting an Microsoft SQL Server View Descriptor 291
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;
