## min mean of grouped mean-Proc SQL

Solved
Super Contributor
Posts: 271

# min mean of grouped mean-Proc SQL

Hi i have a time series monthly basis, i need to find out minimum mean value from annually mean values

step i did: find annually mean

then find out the minimum of them.

My code is

proc sql;

select min(vol_mean) into : mime from (select year(time) as year,mean(volume) as vol_mean from x group by year(time));

select year(time) as year,mean(volume) as vol_mean from x group by calculated year;

select year,vol_mean from b where put(vol_mean,8.2)=put(&mime,8.2) ;

quit;

is there any other compact method to do it using proc sql ?

Accepted Solutions
Solution
‎09-22-2014 07:04 AM
Super User
Posts: 9,599

## Re: min mean of grouped mean-Proc SQL

proc sql;

create table WANT as

select  *

from    ( select  distinct

year(TIME) as YEAR,

mean(VOLUME) as MEAN_VOL

from    A.X

group by year(TIME))

having min(MEAN_VOL)=MEAN_VOL;

quit;

All Replies
Super User
Posts: 9,599

## Re: min mean of grouped mean-Proc SQL

Hi,

Well, at a guess see below.  Best to pop in some test data and required output though so can check it.  Note that I am not sure about your code, what is dataset b in the third select?  No table is created by the second select so where does B come from?  Also, please use spacing as it makes code so much easier to read:

proc sql;

create table WANT as

select  year,

vol_mean

from    (select  year(time) as year,

mean(volume) as vol_mean

from x

group by calculated year)

where   vol_mean=(select min(mean(volume)) as vol_mean from x group by year(time));

quit;

Super Contributor
Posts: 271

## Re: min mean of grouped mean-Proc SQL

i have tried this kind of code but "ERROR: Summary functions nested in this way are not supported." is coming

i tried to run ur code same error is coming

apology for my mistake :

my code is :

proc sql;

select min(vol_mean) into : mime from (select year(time) as year,mean(volume) as vol_mean from x group by year(time));

create table c as select year(time) as year,mean(volume)  as vol_mean from x group by calculated year;

select year,vol_mean from c where put(vol_mean,8.2)=put(&mime,8.2) ;

quit;

and attached is data

desire output is :

year  vol_mean

2005  57053.88

Super User
Posts: 9,599

## Re: min mean of grouped mean-Proc SQL

Something like:

proc sql;

create table WANT as

select  distinct

year(TIME) as YEAR,

mean(VOLUME) as MEAN_VOL

from    A.X

group by year(TIME);

quit;

Super Contributor
Posts: 271

## Re: min mean of grouped mean-Proc SQL

in this case all obs coming, while i require only minimum one

Solution
‎09-22-2014 07:04 AM
Super User
Posts: 9,599

## Re: min mean of grouped mean-Proc SQL

proc sql;

create table WANT as

select  *

from    ( select  distinct

year(TIME) as YEAR,

mean(VOLUME) as MEAN_VOL

from    A.X

group by year(TIME))

having min(MEAN_VOL)=MEAN_VOL;

quit;

Super Contributor
Posts: 271

## Re: min mean of grouped mean-Proc SQL

Thanks,

I was trying this.

proc sql;

select  distinct

year(TIME) as YEAR,

mean(VOLUME) as MEAN_VOL

from    Anuj.test

group by year(TIME)

having min(MEAN_VOL)=MEAN_VOL;

quit;

Regular Contributor
Posts: 168

## Re: min mean of grouped mean-Proc SQL

You've used two 'from' in your code. Not sure it will work.

proc sql;

create table WANT as

select  year,

vol_mean

from (select  year(time) as year,

mean(volume) as vol_mean

from x

group by calculated year)

where   vol_mean=(select min(mean(volume)) as vol_mean from x group by year(time));

quit;

Super User
Posts: 9,599

## Re: min mean of grouped mean-Proc SQL

Hi,

That is called a subquery, I am select from the results of the subquery

(select  year(time) as year,mean(volume) as vol_mean from x group by calculated year)

🔒 This topic is solved and locked.