Help using Base SAS procedures

Proc Sql Remerging summary statstics

Reply
Regular Contributor
Posts: 155

Proc Sql Remerging summary statstics

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 Star
Posts: 7,363

Proc Sql Remerging summary statstics

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

Regular Contributor
Posts: 155

Proc Sql Remerging summary statstics

Thanks for your quick reply

Super User
Super User
Posts: 6,502

Proc Sql Remerging summary statstics

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.

Regular Contributor
Posts: 155

Proc Sql Remerging summary statstics

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

Super User
Super User
Posts: 6,502

Re: Proc Sql Remerging summary statstics

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.

Regular Contributor
Posts: 155

Re: Proc Sql Remerging summary statstics

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.

Super User
Super User
Posts: 6,502

Re: Proc Sql Remerging summary statstics

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;

Ask a Question
Discussion stats
  • 7 replies
  • 4567 views
  • 0 likes
  • 3 in conversation