09-26-2011 01:24 PM
i have a data set with the following layout:
Zone Building_type Year Day_yr End_use Hour1 Hour2.......Hour24
columns Hour1 Hour2..Hour24 contain electricity use at the given hour.
i would like to calculate the average electricity use. do i have to "nest" the summary function in sql?
proc sql ;
from table A
group by Zone,Building_type,Year ;
also, is there a way to specify like named columns in sql? i think in the data step, one could use: mean(of Hour1-Hour24) to calculate the mean for the day.
09-26-2011 01:47 PM
One way is to have proc sql create the list by accessing dictionary.columns. e.g.,
set sashelp.class (rename=(
proc sql noprint;
select name into :names
separated by ","
where libname eq "WORK" and
memname eq "HAVE" and
upcase(name) like "HOUR%"
create table want as
select name, mean(&names.)
09-26-2011 02:49 PM
thanks art! this is a nice work around but it sure would be nice to have a way to reference like named columns directly - similar to in a data step. on another note, does anyone know of a "list" of data step functions that can not be used in sql? i recall that lag is one function and I believe you can not use arrays or any of the "do loop" constructs.
09-26-2011 05:37 PM
I don't think I have ever seen such a list. The documentation states that you can use "most" of the functions but they are not treated as sql aggregate functions. Take a look at:
09-26-2011 07:16 PM
oh, hah, I somehow missed the idea that it had to be in sql :smileyblush:
variable lists do not work in sql, the best way I know to accomplish the desired outcome it to follow Art's original description.