Hi,
When i am using Proc SQL for counts i am getting this note
NOTE: The query requires remerging summary statistics back with the original data.
Does this create any performance issues.Is it ok to neglect this.
Not necessarily. Take a look at: http://support.sas.com/kb/4/308.html
Thanks for your quick reply
If that is what you want it to produce then it does not introduce any performance issues.
It depends on whether you only wanted the summary statistics or if you also wanted the original detailed rows.
Consider these two queries.
proc sql;
create table one as select mean(age) as meanage from sashelp.class;
create table two as select name,mean(age) as meanage from sashelp.class;
quit;
The first one just creates one row with the mean age. The second will create 19 rows that all have the same value for the new meanage variable. That is what it means by remerging.
Hey Tom,
Thanks for you quick reply.I want the detail rows
In my scenario i am using it like this
proc sql;
create table temp as
select name,code,region,count(code)
from sashelp.class
group by (name);
quit;
I want the count of codes in each name
i want to display like this
name code count
x y 1
x z 2
so i am using only name in my group by
I am getting the correct counts but just i am getting the Note
That is because you included variables that are neither summary variables nor part of the group by list of variables.
From your output you want this query.
select name,code,count(*) as count
from sashelp.class
group by name,code
;
Why did you include region in your query? It is not listed in your sample output. Even if each value of NAME only has one value of REGION you should include it in the group by list.
Hi Tom,
Sorry for late reply.forgot to include the region in the output.I agree with your solution.
i want to know wether is there any other bettr way than the way i am doing
i want to print the counts state wise counts and region wise counts like
state:x
region codes count Items
x 20 Abc
y 10 Zab
state count:30
In the data set temp i have
state region codes items
a x 123 Abc
a x 345 Abc
b y 567 Zab
Items variable was not used for calculation it is used for printing.
when i am calculating region wise counts in order to avoid remerging what i am doing is
proc sql;
create table test as
select state,region,count(codes) as region counts
from temp
group by provider,region;
quit;
Then i am doing inner join
proc sql ;
create table test2 as
select tbl1.*,tbl2.regioncounts
from temp as tbl1 and temp as tbl2;
quit;
So it takes two steps to do that as Items variable was in the data set and i need items for printing but not for calculation
Again for state counts i am doing the same way.
Is it the only or i can do it in a single step.
i appreciate your patience and support.
Your code is just doing the "remerging" explicitely. Not sure why you care about the message if that is the result that you want.
Are you asking how to recode your SQL statement to get the same results without the message?
Here is one method using a corralated sub-query.
data have ;
input state $ region $ codes items $ ;
cards;
a x 123 Abc
a x 345 Abc
b y 567 Zab
run;
proc sql noprint ;
create table method1 as
select distinct a.*,count(codes) as codecount
from have a
group by a.state,a.region
;
create table method2 as
select distinct a.*
,(select distinct count(b.codes)
from have b
where a.state=b.state and a.region=b.region
) as codecount
from have a
order by a.state,a.region
;
quit;
proc compare data=method1 compare=method2; run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.