Help using Base SAS procedures

count(*) vs count(1)

Accepted Solution Solved
Reply
Super Contributor
Posts: 318
Accepted Solution

count(*) vs count(1)

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!


Accepted Solutions
Solution
‎09-15-2011 08:23 AM
PROC Star
Posts: 7,363

count(*) vs count(1)

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;

View solution in original post


All Replies
Super User
Posts: 9,682

count(*) vs count(1)

What is your code?

Are you talking about proc sql ?

Ksharp

Super Contributor
Posts: 318

count(*) vs count(1)

Hi,

yes i'm talking about proc sql.

PROC Star
Posts: 7,363

count(*) vs count(1)

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;

Super Contributor
Posts: 318

count(*) vs count(1)

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!

Solution
‎09-15-2011 08:23 AM
PROC Star
Posts: 7,363

count(*) vs count(1)

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;

Super Contributor
Posts: 318

count(*) vs count(1)

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

Super User
Posts: 9,682

count(*) vs count(1)

I think you can also use automatic macro &sqlobs.

Ksharp

Trusted Advisor
Posts: 1,300

count(*) vs count(1)

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

proc sql;

  create table want as

select *

    from sashelp.class;

  %put &sqlobs;

quit;

PROC Star
Posts: 7,363

count(*) vs count(1)

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

Trusted Advisor
Posts: 1,300

count(*) vs count(1)

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

Super User
Posts: 9,682

count(*) vs count(1)

Sorry. Art

I do not understand op' s meaning totally

Ksharp

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 628 views
  • 0 likes
  • 4 in conversation