BookmarkSubscribeRSS Feed
JasonNC
Quartz | Level 8

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.

7 REPLIES 7
art297
Opal | Level 21

Not necessarily.  Take a look at: http://support.sas.com/kb/4/308.html

JasonNC
Quartz | Level 8

Thanks for your quick reply

Tom
Super User Tom
Super User

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.

JasonNC
Quartz | Level 8

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

Tom
Super User Tom
Super User

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.

JasonNC
Quartz | Level 8

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.

Tom
Super User Tom
Super User

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;

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
  • 7 replies
  • 8274 views
  • 1 like
  • 3 in conversation