topic Re: min mean of grouped mean-Proc SQL in SAS Programming

min mean of grouped mean-Proc SQL

Aman4SAS — Mon, 22 Sep 2014 10:01:39 GMT

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 ?

Re: min mean of grouped mean-Proc SQL

RW9 — Mon, 22 Sep 2014 10:14:22 GMT

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;

Re: min mean of grouped mean-Proc SQL

Aman4SAS — Mon, 22 Sep 2014 10:29:45 GMT

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

Re: min mean of grouped mean-Proc SQL

RW9 — Mon, 22 Sep 2014 10:41:57 GMT

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;

Re: min mean of grouped mean-Proc SQL

RamKumar — Mon, 22 Sep 2014 10:43:23 GMT

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;

Re: min mean of grouped mean-Proc SQL

RW9 — Mon, 22 Sep 2014 10:51:46 GMT

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)

Re: min mean of grouped mean-Proc SQL

Aman4SAS — Mon, 22 Sep 2014 10:57:26 GMT

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

Re: min mean of grouped mean-Proc SQL

RW9 — Mon, 22 Sep 2014 11:04:17 GMT

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;

Re: min mean of grouped mean-Proc SQL

Aman4SAS — Mon, 22 Sep 2014 11:14:35 GMT

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;