min mean of grouped mean-Proc SQL

[Aman4SAS]

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 ?

[RW9]

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;

[Aman4SAS]

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

[RW9]

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;

[Aman4SAS]

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

[RW9 · Accepted solution]

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;

[Aman4SAS]

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;

[RamKumar]

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;

[RW9]

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)