DATA Step, Macro, Functions and more

min mean of grouped mean-Proc SQL

Accepted Solution Solved
Reply
Super Contributor
Posts: 265
Accepted Solution

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
Super User
Posts: 7,392

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,392

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: 265

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

Attachment
Super User
Super User
Posts: 7,392

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: 265

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
Super User
Posts: 7,392

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: 265

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
Super User
Posts: 7,392

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 256 views
  • 0 likes
  • 3 in conversation