How to find a minimum using proc sql

asishgautam — Thu, 19 Jul 2012 22:48:05 GMT

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 ;

Re: How to find a minimum using proc sql

PGStats — Thu, 19 Jul 2012 23:56:31 GMT

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 ;

Re: How to find a minimum using proc sql

asishgautam — Fri, 20 Jul 2012 15:33:11 GMT

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

topic How to find a minimum using proc sql in SAS Procedures

How to find a minimum using proc sql

Re: How to find a minimum using proc sql

Re: How to find a minimum using proc sql