hi,
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 ;
select Zone,Building_type,Year,mean(mean(Hour1,Hour2,...Hour24))
from table A
group by Zone,Building_type,Year ;
quit ;
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.
One way is to have proc sql create the list by accessing dictionary.columns. e.g.,
data have;
set sashelp.class (rename=(
age=hour1
height=hour2
weight=hour3));
run;
proc sql noprint;
select name into :names
separated by ","
from dictionary.columns
where libname eq "WORK" and
memname eq "HAVE" and
upcase(name) like "HOUR%"
;
create table want as
select name, mean(&names.)
from have
;
quit;
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.
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:
data want;
set sashelp.class( rename=( age=hour1 height=hour2 weight=hour3 ) );
mean=mean(of hour:);
run;
The OP wanted to do it in proc sql!
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.