topic Re: a question about proc sql in SAS Programming

a question about proc sql

maomaochong96888_hotmail_com — Wed, 05 Jun 2013 19:45:58 GMT

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

Re: a question about proc sql

ChrisNZ — Wed, 05 Jun 2013 22:02:51 GMT

where x > 5

will return no records, therefore, count is zero,

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

Re: a question about proc sql

Haikuo — Wed, 05 Jun 2013 22:10:27 GMT

SAS version and platform?

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

Haikuo

Re: a question about proc sql

PGStats — Wed, 05 Jun 2013 23:49:42 GMT

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;

Re: a question about proc sql

maomaochong96888_hotmail_com — Thu, 06 Jun 2013 00:50:39 GMT

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

Re: a question about proc sql

Tom — Thu, 06 Jun 2013 01:20:54 GMT

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;

Re: a question about proc sql

PGStats — Thu, 06 Jun 2013 02:29:18 GMT

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

Re: a question about proc sql

maomaochong96888_hotmail_com — Thu, 06 Jun 2013 13:04:07 GMT

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

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

Re: a question about proc sql

maomaochong96888_hotmail_com — Thu, 06 Jun 2013 13:06:47 GMT

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

Re: a question about proc sql

anushakalyani — Thu, 06 Jun 2013 13:38:32 GMT

Sure output returns with 0 because there are no records in x greater then 5.

Re: a question about proc sql

maomaochong96888_hotmail_com — Thu, 06 Jun 2013 14:32:45 GMT

thanks anushakalyani

Yes, i see why there is 0 because no records in x > 5. but i don't know why the variable comment is missing if the count =0. i think it should be "count bigger than 5"

but is count not missing, comment is always not missing...

and my point is not why count = 0. my point is why comment is missing if count =0. but if count>0 comment is always no missing. Maybe my poor english doesn't make me clear...

Re: a question about proc sql

PGStats — Thu, 06 Jun 2013 14:37:32 GMT

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.

Re: a question about proc sql

Tom — Thu, 06 Jun 2013 16:09:16 GMT

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.

Re: a question about proc sql

maomaochong96888_hotmail_com — Thu, 06 Jun 2013 17:07:43 GMT

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

Re: a question about proc sql

maomaochong96888_hotmail_com — Thu, 06 Jun 2013 17:13:48 GMT

hi! Tom

below is the log... no error at all... and I copied your code and run them in my home's computer and it is OK... my company's sas is 9.1 on xp...

and this is a old problem for me and i cannot solve it. so every time i just avoid to use proc sql although i love it...

thanks again

106 data t;

107

108 input a $ x @@;

109

110 cards;

NOTE: SAS went to a new line when INPUT statement reached past the end of a line.

NOTE: The data set WORK.T has 5 observations and 2 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

114 run;

115

116 proc sql ;

117

118 create table nocount as

119

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

121

122 ;

NOTE: Table WORK.NOCOUNT created, with 0 rows and 1 columns.

123

124 create table count as

125

126 select count(*) as count

127

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

NOTE: Table WORK.COUNT created, with 1 rows and 2 columns.

129

130 create table wrongcount as

131

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

133

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

135

136 ;

NOTE: Table WORK.WRONGCOUNT created, with 0 rows and 2 columns.

137

138 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

Re: a question about proc sql

Doc_Duke — Thu, 06 Jun 2013 17:19:12 GMT

Your English is fine. You may need to do as Tom said and provide your log, SAS version, and OS as well as your output. Cut-and-paste will sometimes reveals things that our eyes miss in re-typing.

I ran your sample code with 32 bit SAS 9.3 TS1M2 on a Win 7 Pro 64 bit OS, and the sample reported 0 as well as the comment.

Doc Muhlbaier

Duke

Re: a question about proc sql

maomaochong96888_hotmail_com — Thu, 06 Jun 2013 17:22:48 GMT

thanks for all of your help!! this is what i got... my sas is 9.1.3 on xp...

Re: a question about proc sql

maomaochong96888_hotmail_com — Thu, 06 Jun 2013 17:26:00 GMT

sorry the picture is not clear...(don't know how to make it clear)

it(count dataset) looks like this:

count comment

Re: a question about proc sql

PGStats — Thu, 06 Jun 2013 17:49:47 GMT

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 GROUP BY clause produces different results thanVersion 6 . - PG

Re: a question about proc sql

maomaochong96888_hotmail_com — Thu, 06 Jun 2013 18:10:25 GMT

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