Tag Archives: libname

Case-Sensitive Schema in a SAS Libname Statement

This is a minor annoying issue that had been bothering me for a few years (!) and was finally resolved with a ticket to SAS Tech Support. I have several libname statements that I have been using to connect to DB2 databases. In the past, the libname statements all ran successfully, and I was able to query the tables in the databases. However, when I tried to view the tables under my assigned libraries in the SAS Explorer window by clicking on the libname, the libraries appeared empty. This was irritating since I either had to use another tool if I wanted to browse the tables and look at the actual data, or rely on Proc Contents.

The answer to the problem turns out to be that some schemas are case-sensitive. SAS Tech Support suggested trying the following libname statements:

libname CRDM1 db2 dsn=crdm schema=appdm uid=xxxxx pwd=xxxxxx sql_functions=all dbindex=yes;

libname CRDM2 db2 dsn=crdm schema=”appdm” uid=xxxxx pwd=xxxxxx sql_functions=all dbindex=yes;

libname CRDM3 db2 dsn=crdm schema=APPDM uid=xxxxx pwd=xxxxxx sql_functions=all dbindex=yes;

libname CRDM4 db2 dsn=crdm schema=”APPDM” uid=xxxxx pwd=xxxxxx sql_functions=all dbindex=yes;

The first and second libname statements didn’t display the tables, but the last two with the schema in all CAPS both worked.

Creating AutoExec Code in SAS Enterprise Guide 4.3

For some users, metadata is already set up by their system administrator in Enterprise Guide so that libraries are automatically assigned; others are not so lucky. If you get tired of having to copy and paste or re-type your libname statement to manually connect to your libraries in Enterprise Guide all the time, you can create an “ad hoc” library in EG. This makes your life easier since every time you open EG, your ad hoc library is automatically assigned. You can also set system options, macros, or other code to run automatically every time you start the application.

To do this quickly and easily, follow these steps:

  • In SAS EG, go to the toolbar
  • Select Tools >> Options >> SAS Programs (left side of screen)
  • Click the check box for “Submit SAS code when server is connected”
  • Select “Edit” (same line as the check box above)
  • When the Edit box pops up, copy and paste the syntax below to automatically connect to your Oracle library (you must enter your own username, password, and schema):

    • libname mylib oracle user=xxxx orapw=xxxxx schema=xxxx path=pathname.com;
    options sastrace=’,,,d’ sastraceloc=saslog mprint symbolgen fullstimer;

    The next time you open EG, look for your “mylib” in the list of libraries on the left side of the EG screen. The above syntax sets up some system options that you may find useful, but you can modify to add additional libname statements, system options, and any other code that you want to run automatically on startup. See my previous post for more about some of these system options.

    How to View Remote Server Files on Your PC

    Many of us create and store files on remote servers. One way of viewing the files you create is to use Proc Download to export them to your local libraries, but downloading large files can be time-consuming. Fortunately, there is an easier way.

    First, I connect to the server and rsubmit my libname statement with the path to its location on the server; then I submit a second libname statement locally using the same libref. After submitting the following code, I can open Explorer, navigate to Libraries, and the library I specified on the remote server is now available for me to browse.

    rsubmit;
    libname WHP “/projects/hedis/hedis/whp”;
    endrsubmit;
    /*submit locally*/
    libname WHP server=remoteservername; /*view remote server files on local machine*/

    3 Ways to Export Your Data to Excel

    SAS offers many different ways to export data to Excel, including Excel ODS output. The following alternatives to ODS are also available:

    The File Export Wizard

    This is an easy way to export your data without having to write any code. To use this wizard:

    • Select File>>Export Data
    • Choose the library and SAS data set name
    • Select a data source from the dropdown list (in this case, the version of Excel you’d like to export to – other types of data sources are also available here)
    • Browse to the folder where you’d like to create your Excel Workbook and type a name for the file
    • You then have the option to save the Proc Export statements generated by the Wizard to a SAS program file

    Proc Export

    If you chose to use the File Export Wizard to save your Proc Export statements, your code may look something like this:

    PROC EXPORT DATA=sasdatasetname
    OUTFILE=”\\path\filename.xls”
    DBMS=EXCEL REPLACE;
    SHEET=”sheetname”;
    RUN;

    • The data set you’d like to export goes after DATA=
    • The file path goes after OUTFILE=
    • You are exporting to Excel, so DBMS=EXCEL
    • If you wish to replace any existing workbook or worksheet with the same name, use the REPLACE option.
    • The SHEET= statement is optional, but if you wish to create a multi-sheet workbook, it is useful. For your next PROC EXPORT statement, just specify a different file and a new SHEET name.

    The Libname Statement

    This is a newer option for exporting to Excel which does not support the REPLACE option, so if you already have a file of the same name you are trying to create, you will have to delete or rename the older version first. Also, while the libname is assigned, you will not be able to access the Excel document you have created; you will first have to exit out of SAS or clear the libname. It is easy to create multiple sheets using this method, though, as shown below.

    libname myxls \\path\filename.xls;

    DATA myxls.sheetname1;
    SET sasdataset1;
    DATA myxls.sheetname2;
    SET sasdataset2;
    RUN;

    PROC SQL;
    Create table myxls.sheetname3 as
    SELECT *
    from sasdataset3;
    QUIT;