When using a WHERE clause in conjunction with a VAR list to query a SAS® Scalable Performance Data Server table, the variable in the WHERE clause must also be specified in the VAR list.

Consider the following example:

   libname outt sasspds 'newspds' host='intellifire' serv='5190' user='user12' 
     password=XXXXXXXXXXX aclgrp='admingrp' unixdomain=yes netcomp=no;
   proc summary data=SCRATCH.CHECK_9317  missing;
      where (MARKETINGCAMPAIGN= 'Internet - MyLending ')
            AND (put(SUBMISSIONDATE, ? YYMMD7.) = '2008-11');
       var
         M0000255 SOLICITS_SUM LEADS_SUM APPS_SUM SUBS_SUM FUNDINGS_SUM BACKEND_T_SUM 
         BACKEND_T_N TOTAL_COST_SUM TOTAL_SUB_SUM TOTAL_FUND_AMT_SUM
         REVENUE_NONCOMMISSIONABLE_SUM REVENUE_SUM ;
    run;

Note that the variable MARKETINGCAMPAIGN is in the WHERE clause, but not on the VAR statement. Because of this, SAS Scalable Performance Data Server cannot perform the (put(SUBMISSIONDATE,YYMMD7.) = '2008-11' part of the WHERE clause. SAS Scalable Performance Data Server filters the rows it can, using the MARKETINGCAMPAIGN='Internet - MyLending' portion of the WHERE clause. The rows are then returned to SAS, which then re-applies the entire WHERE clause to the returned rows.

The column MARKETINGCAMPAIGN must come back from SAS Scalable Performance Data Server in order for SAS to satisfy the part of the WHERE clause that refers to that column.

In this example, that cannot happen because SAS Scalable Performance Data Server is pushing a KEEP list on the query that contains only the columns on the VAR list, and that list does not include MARKETINGCAMPAIGN. When SAS gets the result back, it has the 32 columns on the VAR list, but is applying the entire WHERE clause to the rows including the MARKETINGCAMPAIGN part. Since the MARKETINGCAMPAIGN column is not in the result set from the query to SAS Scalable Performance Data Server, it returns 0 obervations when in fact it should return >0 observations.

Operating System and Release Information

A fix for this issue for SPD Server 4.4 is available at: