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!
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;
What is your code?
Are you talking about proc sql ?
Ksharp
Hi,
yes i'm talking about proc sql.
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;
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!
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;
Just want to make sure if all is the same in newer versions. Thank you very much again for the great help!
I think you can also use automatic macro &sqlobs.
Ksharp
&sqlobs is the output count of a executed sql task.
proc sql;
create table want as
select *
from sashelp.class;
%put &sqlobs;
quit;
I'm confused about why you both suggested &sqlobs. Can that be used to get different numbers based on a group by statement?
I do not recommend. It cannot be used for that purpose. I should have been more clear in my post.
Sorry. Art
I do not understand op' s meaning totally
Ksharp
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.
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.