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

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 ;

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

2 REPLIES 2
PGStats
Opal | Level 21

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
asishgautam
Calcite | Level 5

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 2 replies
  • 817 views
  • 0 likes
  • 2 in conversation