Returning the Libname String for OLE DB and ODBC
Often it is the case that it is unknown what attributes to specify when creating a libname statement when connecting to a database.
When connecting via OLE DB it is possible to be prompted by a Data Link Wizard for certain attributes by entering the statement libname oledb;
When connecting via ODBC it is also possible to be prompted by a Select Data Source Wizard for certain attributes to connect to a File Data Source or a Machine Data Source which has been configured in Data Sources(ODBC) by entering the statement libname odbc prompt;
These methods will automatically set up the Library connection, unfortunately this will not notify the user with the parameters SAS entered into the libname statement.
The parameters created by the Data Link and Data Source Wizards for each connection can be output to the log window by the use of %put %superq(sysdbmsg) statement after the libname statement is called.
The %SUPERQ function is required to mask special characters and any unbalanced parentheses or quotation marks might be created using the connection wizards which are stored in the SYSDBMSG macro.
Below is the code needed for this example:
libname mylib oledb; %put %superq(sysdbmsg);
libname mylib2 odbc prompt; %put %superq(sysdbmsg);
1 libname mylib oledb; NOTE: Libref MYLIB was successfully assigned as follows: Engine: OLEDB Physical Name: 2 %put %superq(sysdbmsg); OLEDB: Provider=SQLOLEDB.1;Integrated Security=SSPI; Persist Security Info=True;Initial Catalog=;Data Source= 3 libname mylib2 odbc prompt; NOTE: Libref MYLIB2 was successfully assigned as follows: Engine: ODBC Physical Name: 4 %put %superq(sysdbmsg); ODBC: DSN=;Description=; Trusted_Connection=Yes;WSID=
The OLEDB and ODBC statement from the log window can then be copied and placed into a libname statement which enables the user to use in their code.
libname Amadeus OLEDB init_string="Provider=SQLOLEDB.1; Integrated Security=SSPI;Persist Security Info=True; Initial Catalog=;Data Source=";
libname Amadeus2 ODBC noprompt="DSN=; Description=;Trusted_Connection=Yes; WSID=";
5 libname Amadeus OLEDB 6 ! init_string=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 7 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX; NOTE: Libref AMADEUS was successfully assigned as follows: Engine: OLEDB Physical Name: 8 libname Amadeus2 ODBC noprompt=XXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 9 ! ; NOTE: Libref AMADEUS2 was successfully assigned as follows: Engine: ODBC Physical Name:
This tip is based on an example in "SAS/ACCESS(R) 9.2 for Relational Databases: Reference, Fourth Edition", SAS Institute.