• Print |
• E-mail  |
• Bookmark

If all of the following conditions occur, SQL may return an incorrect
number of observations:

  1) an alias that is a simple rename,
  2) the aliased variable appears on a SELECT DISTINCT list of
     variables,
  3) the aliased variable is also part of an index,
  4) the underlying table has an index on it involving a subset of these
     variables, and
  5) the internal number of the alias matches the internal number of one
     or more of the index variables.

This problem was first reported in SAS Release 8.1, although it may
also be present in earlier releases.

The following code illustrates the problem:

  data test;
    length id 8 year 8;
    input id year;
  cards;
  1 2000
  2 2000
  3 1999
  4 2000
  5 1999
  6 2000
  7 1999
  ;
  proc datasets nolist lib=work;
    modify test;
    index create id/unique;
  quit;
  proc sql;
    create table test_a as select distinct year
    from work.test;
    create table test_b as select distinct year as newyear
    from work.test;
  quit;

A Technical Support hot fix for Release 8.1 TSLEVEL TS1M0 for this
issue is available at:

http://www.sas.com/techsup/download/hotfix/81_sbcs_prod_list.html#004241


A Technical Support hot fix for Release 8.2 TSLEVEL TS2M0 for this
issue is available at:

http://www.sas.com/techsup/download/hotfix/82_sbcs_prod_list.html#004241

For customers running SAS with Asian Language Support (DBCS), this
hot fix should be downloaded from:

http://www.sas.com/techsup/download/hotfix/82_dbcs_prod_list.html#004241

Operating System and Release Information

A fix for Release 8.1 (TS1M0) for this issue is available at:

A fix for Release 8.2 (TS2M0) for this issue is available at:

A fix for Release 8.2 (TS2M0) with Asian Language Support (DBCS) for this issue is available at: