DATA Step, Macro, Functions and more

a question about proc sql

Reply
Occasional Contributor
Posts: 16

a question about proc sql

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

PROC Star
Posts: 1,760

Re: a question about proc sql

Posted in reply to maomaochong96888_hotmail_com

where x > 5

will return no records, therefore, count is zero,

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

Respected Advisor
Posts: 3,156

Re: a question about proc sql

Posted in reply to maomaochong96888_hotmail_com

SAS version and platform?

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

Haikuo

Respected Advisor
Posts: 4,923

Re: a question about proc sql

Posted in reply to maomaochong96888_hotmail_com

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

PG
Respected Advisor
Posts: 4,923

Re: a question about proc sql

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

PG
Occasional Contributor
Posts: 16

Re: a question about proc sql

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

Respected Advisor
Posts: 4,923

Re: a question about proc sql

Posted in reply to maomaochong96888_hotmail_com

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.

PG
Occasional Contributor
Posts: 16

Re: a question about proc sql

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

Respected Advisor
Posts: 4,923

Re: a question about proc sql

Posted in reply to maomaochong96888_hotmail_com

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

PG
Occasional Contributor
Posts: 16

Re: a question about proc sql

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

Respected Advisor
Posts: 4,923

Re: a question about proc sql

Posted in reply to maomaochong96888_hotmail_com

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

PG
Occasional Contributor
Posts: 16

Re: a question about proc sql

Posted in reply to 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

Super User
Super User
Posts: 7,050

Re: a question about proc sql

Posted in reply to maomaochong96888_hotmail_com

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;

Occasional Contributor
Posts: 16

Re: a question about proc sql

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...

Super User
Super User
Posts: 7,050

Re: a question about proc sql

Posted in reply to maomaochong96888_hotmail_com

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.

Ask a Question
Discussion stats
  • 27 replies
  • 671 views
  • 0 likes
  • 9 in conversation