a question about proc sql

[maomaochong96888_hotmail_com]

hi!

I already spent a lot of time on this question and search a lot but cannot find answer...

I have a example

data t;
input a $ x;
datalines;
a 1
b 2
c 3
d 4
e 5
;
run;

proc sql noprint;
create table t1 as select count(distinct a) as count, "bigger than 5" as comment from t where x > 5;
quit;

why in the output comment is missing and count is zero... and if i change the subset condition to x >= 5 then the comment is not missing(is bigger than 5) and count is 1...

how to solve this problem?

thanks a lot

jennifer

[ChrisNZ]

where x > 5

will return no records, therefore, count is zero,

and comment is not missing when the code above is run.

[Haikuo]

SAS version and platform?

It works for me: SAS 9.3 M2 on Win7 64bit.

Haikuo

[PGStats]

What if you try :

proc sql;

create table t1 as

select

     count(distinct a) as count,

     "bigger than 5" as comment

from t

where x > 5

group by comment;

select * from t1;

quit;

PG

[PGStats]

I know it's not a reference, but MS-Access 2007 also produces an empty table when no observation matches the condition and you group by a constant. It does, however output a zero count when the GROUP BY clause is omitted. - PG

[maomaochong96888_hotmail_com]

thanks PG so you mean this is software's problem? :smileyshocked:

[PGStats]

Not really a bug. I suspect that the SQL standard doesn't cover that case in detail, or that the expected behavior evolved with the standard versions. I would like to know about other SQL implementations. Did you try adding GROUP BY comment, as I suggested above? I only have SAS 9.3 installed.

[maomaochong96888_hotmail_com]

Hi! PG yes i tried group by comment but the same thing here...

[PGStats]

As I suspected, there is ambiguity in what SQL should return for such a query. It is not a new problem. Look at this note dating back April 2001 : 4414 - Creating a table and selecting from an empty table, or empty result setusing PROC SQL and a G... . - PG

[maomaochong96888_hotmail_com]

thanks PG

so this means no way to solve this kind of problem so far except upgrade SAS or no use of proc sql... right:smileyconfused:

Jennifer

[PGStats]

Hi Jennifer, you could use :

proc sql;

create table t1 as

select

     sum(b5) as count,

     "bigger than 5" as comment

from (select distinct a, x > 5 as b5 from t);

select * from t1;

quit;

PG

[maomaochong96888_hotmail_com]

thanks for all your reply

it is so wierd... it works in my home's computer(sas9.2 on xp) but it really doesn't work on my computer in company(sas9,1 on xp)...

really don't why... it almost makes me crazy...

thanks again

[Tom]

Make sure that you don't have a typo. 

If you do not have the COUNT() aggregate function (or some other aggregate function) then you will get 0 records returned instead of one record with a 0 for the count.

Try these three queries:

data t;

input a $ x @@;

cards;

a 1 b 2 c 3 d 4 e 5

run;

proc sql ;

  create table nocount as

  select "no count bigger than 5" as comment from t where x > 5

  ;

  create table count as

  select  count(*) as count

       ,  "count bigger than 5" as comment from t where x > 5

  ;

  create table wrongcount as

  select  count('1','2') as count

       ,  "count bigger than 5" as comment from t where x > 5

  ;

quit;

[maomaochong96888_hotmail_com]

thanks TOM

I understand what you mean.

And I try your code in my company's computer.

for this part

create table count as

  select  count(*) as count

       ,  "count bigger than 5" as comment from t where x > 5

  ;

I think the result should be

count          comment

0                count bigger than 5

right?

but in my company's computer, it is always as below,

count          comment

0            

I just wonder why the comment is missing....  this is my problem...

[Tom]

I still think it is most likely a coding error that you are not detecting.

You should post the SAS log if you can recreate the problem. Also the version of SAS and operating system.