turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- min mean of grouped mean-Proc SQL

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2014 06:01 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2014 07:04 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2014 06:14 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2014 06:29 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2014 06:41 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2014 06:57 AM

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

Solution

09-22-2014
07:04 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2014 07:04 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2014 07:14 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2014 06:43 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2014 06:51 AM

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)