count(*) vs count(1)

[milts]

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!

[Ksharp]

What is your code?

Are you talking about proc sql ?

Ksharp

[milts]

Hi,

yes i'm talking about proc sql.

[art297]

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;

[milts]

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!

[art297 · Accepted solution]

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;

[milts]

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

[Ksharp]

I think you can also use automatic macro &sqlobs.

Ksharp

[FriedEgg]

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

proc sql;

  create table want as

select *

    from sashelp.class;

  %put &sqlobs;

quit;

[art297]

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

[FriedEgg]

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

[Ksharp]

Sorry. Art

I do not understand op' s meaning totally

Ksharp