BookmarkSubscribeRSS Feed

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

27 REPLIES 27
ChrisNZ
Tourmaline | Level 20

where x > 5

will return no records, therefore, count is zero,

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

Haikuo
Onyx | Level 15

SAS version and platform?

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

Haikuo

PGStats
Opal | Level 21

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
PGStats
Opal | Level 21

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

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

PGStats
Opal | Level 21

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

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

PGStats
Opal | Level 21

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

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
Opal | Level 21

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

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
Super User Tom
Super User

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;

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
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 27 replies
  • 1737 views
  • 0 likes
  • 9 in conversation