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)
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.