BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
milts
Pyrite | Level 9

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!

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

11 REPLIES 11
Ksharp
Super User

What is your code?

Are you talking about proc sql ?

Ksharp

milts
Pyrite | Level 9

Hi,

yes i'm talking about proc sql.

art297
Opal | Level 21

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
Pyrite | Level 9

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

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
Pyrite | Level 9

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

Ksharp
Super User

I think you can also use automatic macro &sqlobs.

Ksharp

FriedEgg
SAS Employee

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

proc sql;

  create table want as

select *

    from sashelp.class;

  %put &sqlobs;

quit;

art297
Opal | Level 21

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

FriedEgg
SAS Employee

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

Ksharp
Super User

Sorry. Art

I do not understand op' s meaning totally

Ksharp

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 11 replies
  • 4004 views
  • 0 likes
  • 4 in conversation