The following code example demonstrates how to enable SAS code to read SQL Server data. You can readily control the data they access by placing tables in a separate SQL Server database schema to which the LIBNAME statement refers. With an ODBC DSN and a LIBNAME statement, any SQL Server professional can empower SAS professionals to read and write to a SQL Server data source. When an organization is creating a custom SAS analysis package with its own analysts, it can be very convenient to read SQL Server data with SAS code. Reading and Processing SQL Server Data with Classic SAS Code YourSchemaName - references a particular schema within a SQL Server database.YourDSNName - is the name of the ODBC DSN that points at the SQL Server database.YourLibRefName - serves as a local name for a data source instead of referencing a local SAS data source the name references a remote SQL Server data source, such as a database.LIBNAME YourLibRefName ODBC DSN='YourDSNName' schema=YourSchemaName The following statement illustrates typical features of a LIBNAME statement for connecting to a SQL Server data source. A LIBNAME statement can take several different formats depending on how you are connecting and precisely to what you are connecting. The LIBNAME statement enables SAS code to reference a SQL Server instance, database, or schema as if it were a local SAS data source. Within the SAS application, you need a SAS/ACCESS LIBNAME statement referencing the ODBC DSN. The DSN can be a user, system, or file type. The driver requires an ODBC DSN on the computer running SAS pointing at the SQL Server data source. Because SQL Server is ODBC compliant, you can use the SAS ODBC driver to read from and write to SQL Server data from within a SAS application. The SAS ODBC driver enables SAS code to access and manipulate ODBC compliant data sources. As a SQL Server professional, you may want to consider the SAS/ACCESS for ODBC. SAS/ACCESS comes in numerous different implementations depending on the other software with which you need to interoperate. SAS offers an ODBC driver and an add-on package (SAS/ACCESS) to Base SAS and other collections of SAS software for facilitating SAS interoperability with data values in other software, such as SQL Server. By adding comments to the code for a data transfer, you can simplify future maintenance of a data transfer process. In addition, you or someone else who is a SAS developer can make changes to the data transfer process and be sure the changes will be followed. Code-based techniques operate according to how they are written there is no opportunity for human error. If you have several different types of transfers between SQL Server and SAS to manage that recur at periodic or aperiodic intervals, then it would definitely be useful to have code-based techniques that you could launch to initiate a transfer. Also, the reliance on SAS and SQL Server wizards makes the approach fundamentally manual, so it is difficult to maintain in a systematic way over time. That approach is a good one for beginners for data transfers between SQL Server and SAS, but it is not rich in flexibility. Two previous articles Transferring data from SAS to SQL Server and back and Using Wizards to Export a Dataset from SAS to SQL Server describe and demonstrate a collection of tips for transferring data values back and forth between SQL Server and SAS via comma-separated-value (.csv) files with the help of SQL Server and SAS wizards for writing and reading.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |