topic count(*) vs count(1) in SAS Procedures

count(*) vs count(1)

milts — Thu, 15 Sep 2011 07:26:14 GMT

Hi,

I've been curious with this one since it has been a debate on some forums. They say that count(1) is faster.

I'm wondering if which is which when it comes to SAS.

Thanks!

count(*) vs count(1)

Ksharp — Thu, 15 Sep 2011 07:56:40 GMT

What is your code?

Are you talking about proc sql ?

Ksharp

count(*) vs count(1)

milts — Thu, 15 Sep 2011 10:09:35 GMT

Hi,

yes i'm talking about proc sql.

count(*) vs count(1)

art297 — Thu, 15 Sep 2011 12:12:37 GMT

In my very unscientific test, count(*) won every trial. I ran about ten trials, selecting which one to run first at random for each trial. On XP with 9.2 count(*) won every single trial by a lot.

/*create test data;*/

data test;

set sashelp.class;

do i=1 to 100000;

output;

end;

run;

proc sql noprint;

select count(*)

from test

;

quit;

proc sql noprint;

select count(1)

from test

;

quit;

count(*) vs count(1)

milts — Thu, 15 Sep 2011 12:17:10 GMT

Hi Art,

What if there is a group by? Would count(*) still be faster?

I also tried this on 913. And yeah based on the results it is count(*) which is faster and even has less memory used.

Many Thanks!

count(*) vs count(1)

art297 — Thu, 15 Sep 2011 12:23:28 GMT

You could have run that yourself. I didn't run 10 trials, but the results appeared to be the same for 2 trials. Count(*) won each time.

/*create test data;*/

data test;

set sashelp.class;

do i=1 to 100000;

output;

end;

run;

proc sql noprint;

select count(*)

from test

group by sex

;

quit;

proc sql noprint;

select count(1)

from test

group by sex

;

quit;

count(*) vs count(1)

milts — Thu, 15 Sep 2011 13:23:45 GMT

Just want to make sure if all is the same in newer versions. Thank you very much again for the great help!

count(*) vs count(1)

Ksharp — Fri, 16 Sep 2011 02:00:30 GMT

I think you can also use automatic macro &sqlobs.

Ksharp

count(*) vs count(1)

FriedEgg — Fri, 16 Sep 2011 17:18:24 GMT

&sqlobs is the output count of a executed sql task.

proc sql;

create table want as

select *

from sashelp.class;

%put &sqlobs;

quit;

count(*) vs count(1)

art297 — Fri, 16 Sep 2011 22:32:30 GMT

I'm confused about why you both suggested &sqlobs. Can that be used to get different numbers based on a group by statement?

count(*) vs count(1)

FriedEgg — Fri, 16 Sep 2011 23:29:32 GMT

I do not recommend. It cannot be used for that purpose. I should have been more clear in my post.

count(*) vs count(1)

Ksharp — Sat, 17 Sep 2011 03:38:47 GMT

Sorry. Art

I do not understand op' s meaning totally

Ksharp