Date: Mon, 29 Sep 2008 12:41:03 -0400
Reply-To: "Howard Schreier <hs AT dc-sug DOT org>"
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Howard Schreier <hs AT dc-sug DOT org>"
Subject: Re: proc sql blank column
On Mon, 29 Sep 2008 07:42:08 -0400, Rowland Hale <haler@TISCALI.CO.UK> wrote:
>I have a problem with the following 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
>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
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
case when 1 then 'AEC'
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.
data AE AEC;
rename name= subject;