BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Aman4SAS
Obsidian | Level 7

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 ?

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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)

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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