topic Proc Sql Remerging summary statstics in SAS Procedures

Proc Sql Remerging summary statstics

JasonNC — Wed, 11 Jan 2012 22:28:51 GMT

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.

Proc Sql Remerging summary statstics

art297 — Wed, 11 Jan 2012 22:32:33 GMT

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

Proc Sql Remerging summary statstics

Tom — Wed, 11 Jan 2012 22:39:55 GMT

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.

Proc Sql Remerging summary statstics

JasonNC — Wed, 11 Jan 2012 22:44:11 GMT

Thanks for your quick reply

Proc Sql Remerging summary statstics

JasonNC — Wed, 11 Jan 2012 22:51:31 GMT

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

Re: Proc Sql Remerging summary statstics

Tom — Wed, 11 Jan 2012 22:59:35 GMT

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.

Re: Proc Sql Remerging summary statstics

JasonNC — Mon, 16 Jan 2012 16:11:05 GMT

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.

Re: Proc Sql Remerging summary statstics

Tom — Mon, 16 Jan 2012 17:27:55 GMT

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;