Help using Base SAS procedures

How to find a minimum using proc sql

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

How to find a minimum using proc sql

I am trying to find not only the level of minimum energy demand by season but also when this minimum level occurs.  I expect multiple answers since it is possible for the minimum to be reach several times in one day or for several days to have the same minimum value.

This is my code:

I can get the minimum level but I am not sure how to identify the day and time by season the minimum occurs.  Any help would be appreciated.

proc sql noprint ;

    create table Hrly_load as

    select Fzone,Btype,Size,Day_yr,Seasonal_id,

           Time_id,sum(Load2) as Chp_base

    from ( select Fzone,Btype,Size,Day_yr,put(Mth,Seasonal.) as Seasonal_id,

                  _NAME_ as  Time_id,

                  case /* convert kWh/kBtu to MMBtu*/

                      when Fuel = 'Gas' then (1000/1000000)*Load

                      else 0.003412142*Load

                  end as Load2

            from Ceus_m2_t

            where EndUse in ('Heat','HotWater')

         )

    group by Fzone,Btype,Size,Day_yr,Seasonal_id,Time_id ;

    create table Min_load_summary2 as

    select Fzone,Btype,Size,Seasonal_id,min(Chp_base) as Min_load,

    from Hrly_load

    group by Fzone,Btype,Size,Seasonal_id

    order by Fzone,Btype,Size,Seasonal_id ;

quit ;

EDIT:

The code below "does"what I am looking but if someone has a more efficient way of doing this, please post your suggestion/code (also point out any error etc).  I looked around the net and it appears "incomplete group by" was the way to go. 

proc sql noprint ;

    create table Test5 as

    select Fzone,Btype,Size,Day_yr,Seasonal_id,

           Time_id,Chp_base

    from ( /* add up 2 end-use over time - get hourly load for each day */

            select Fzone,Btype,Size,Day_yr,Seasonal_id,

                   Time_id,sum(Load2) as Chp_base

            from ( /* convert kBtu to MMBtu*/

                    select Fzone,Btype,Size,Day_yr,put(Mth,Seasonal.) as Seasonal_id,

                          _NAME_ as  Time_id,

                          case

                              when Fuel = 'Gas' then (1000/1000000)*Load

                              else 0.003412142*Load

                          end as Load2

                    from Ceus_m2_t

                    where EndUse in ('Heat','HotWater')

                 )

            group by Fzone,Btype,Size,Day_yr,Seasonal_id,Time_id

         )

    group by Fzone,Btype,Size,Seasonal_id

    having Chp_base = min(Chp_base) ;

quit ;


Accepted Solutions
Solution
‎07-19-2012 07:56 PM
Respected Advisor
Posts: 4,923

Re: How to find a minimum using proc sql

Posted in reply to asishgautam

Your logic seems sound. I can propose the following slight simplification (untested) :

proc sql;

    create table Test5 as

    select Fzone, Btype, Size, Day_yr, Seasonal_id, Time_id, Chp_base

    from  /* convert kBtu to MMBtu - add up 2 end-use over time - get hourly load for each day */

          (select Fzone, Btype, Size, Day_yr, put(Mth, Seasonal.) as Seasonal_id,

               _NAME_ as Time_id,

               sum((case when Fuel = 'Gas' then 1000/1000000 else 0.003412142 end)*Load) as Chp_base

          from Ceus_m2_t

          where EndUse in ('Heat', 'HotWater')

          group by Fzone, Btype, Size, Day_yr, calculated Seasonal_id, Time_id

         )

     group by Fzone, Btype, Size, Seasonal_id

     having Chp_base = min(Chp_base);

quit ;

PG

PG

View solution in original post


All Replies
Solution
‎07-19-2012 07:56 PM
Respected Advisor
Posts: 4,923

Re: How to find a minimum using proc sql

Posted in reply to asishgautam

Your logic seems sound. I can propose the following slight simplification (untested) :

proc sql;

    create table Test5 as

    select Fzone, Btype, Size, Day_yr, Seasonal_id, Time_id, Chp_base

    from  /* convert kBtu to MMBtu - add up 2 end-use over time - get hourly load for each day */

          (select Fzone, Btype, Size, Day_yr, put(Mth, Seasonal.) as Seasonal_id,

               _NAME_ as Time_id,

               sum((case when Fuel = 'Gas' then 1000/1000000 else 0.003412142 end)*Load) as Chp_base

          from Ceus_m2_t

          where EndUse in ('Heat', 'HotWater')

          group by Fzone, Btype, Size, Day_yr, calculated Seasonal_id, Time_id

         )

     group by Fzone, Btype, Size, Seasonal_id

     having Chp_base = min(Chp_base);

quit ;

PG

PG
Contributor
Posts: 66

Re: How to find a minimum using proc sql

Yep - it works.  I didn't realize you could sum over a case statement - good to know.  I wonder what other tricks are there...

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 175 views
  • 0 likes
  • 2 in conversation