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 ?
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;
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;
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
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;
in this case all obs coming, while i require only minimum one
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;
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;
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;
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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.