Monday, June 13, 2011

Unusual SAS Error Message

Today I encountered the following error messages in SAS after creating a table using SQL based on a SASHelp table:

ERROR: Floating Point Zero Divide.
ERROR: Termination due to Floating Point Exception

This strange set of errors is not well documented and, as I eventually found out, has nothing to do with my original query:

proc sql;
create table _test_ as
select * from sashelp.vtable
where upcase(libname)="SPECIFIC_LIBNAME"
;
quit;

Where "SPECIFIC_LIBNAME" was a macro variable containing the libname I wanted information about.

The solution to this problem was to clear a particular library that consisted only of views. This library's members were generated prior to the above step, and may have somehow contributed to the problem within the SASHelp metadata. In general, it may be a good practice to review the libraries and their sizes when encountering problems with the SASHelp library, which contains a great deal of metadata on SAS libraries.

For this set of errors, however, this solution will probably not always work. It would appear that the problem is related to extensions of SAS that are dependent on external sources. In this case, it may have been the SQL procedure. This does seem strange, since SQL is a widely-used standard. Clearing the previously-created library may have cleared up an exception caused by a large amount of data in the SASHelp table that was not accounted for in the manner in which SAS implemented the SQL standard or the proprietary extensions of it.

If you encounter this error, please let me know! Especially if you know how to fix it!

Articles and Discussions Regarding these Errors:

4 comments:

  1. Chris, Hi, I have also encountered the error.

    I have just moved to SAS 9.3 64bit on Windows 7.

    If I run the following:

    Filename settings catalog 'c:\temp\test.txt';
    proc sql noprint;
    create table work.vextfl as
    select * from dictionary.extfiles;
    quit;

    Then I get the same error as you above.
    I realise the text file isn't actually a catalog, I'm just using this as proof of concept. It seems to be the addition of catalog that causes the error.

    The code works fine on SAS 9.2 and 9.1.3 on XP 32bit.

    I have submitted the problem to SAS so let's see what transpires!

    Best regards,
    Chris Gibby.

    ReplyDelete
  2. I tried that and did not find any problem like this. did you install the SAS PC files Server 9.31?

    ReplyDelete
    Replies
    1. I think the issue was particular to the libraries I had where I found the problem. It may have been a corrupt or broken SQL view that led to the problem.

      Delete

Note: Only a member of this blog may post a comment.