Solved
Contributor
Posts: 66

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 ;

select Fzone,Btype,Size,Day_yr,Seasonal_id,

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

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

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,

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

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
Posts: 5,536

Re: How to find a minimum using proc sql

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

All Replies
Solution
‎07-19-2012 07:56 PM
Posts: 5,536

Re: How to find a minimum using proc sql

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.