Date:         Mon, 29 Sep 2008 12:41:03 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>"
              <schreier.junk.mail@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>"
              <schreier.junk.mail@GMAIL.COM>
Subject:      Re: proc sql blank column

On Mon, 29 Sep 2008 07:42:08 -0400, Rowland Hale <haler@TISCALI.CO.UK> wrote:

>Hi,
>
>I have a problem with the following sql:
>
>proc sql;
>    create table test1 as
>    select 'AE' as dataset length=20, count(distinct subject) as sbjn, count
>(*) as nobs
>    from coded.AE
>    ;
>    create table test2 as
>    select 'AEC' as dataset length=20, count(distinct subject) as sbjn,
>count(*) as nobs
>    from coded.AEC
>    ;
>quit;
>
>The first query works fine, but with the second coded.AEC has zero
>observations and 'dataset' ends up blank, although both count fields
>correctly contain 0. Why is this? How can I get the query to work for empty
>datasets?
>
>Thanks,
>Rowland

Looks like a bug. When I run such code in SAS 9.2 I get the expected results
(literal AEC appears in the DATASET column).

Here's a workaround for earlier versions. Replace

   'AEC'

with

   case when 1 then 'AEC'
        else put(count(*),5.)
        end

The ELSE branch is never used, but the presence of the COUNT function seems
to make SQL treat the column as a summary result rather than just a literal,
which makes the difference.

Test data:

 data AE AEC;
 set sashelp.class;
 rename name= subject;
 output AE;
 run;