selecting similarly named columns in proc sql

[asishgautam]

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.

[art297]

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;

[asishgautam]

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.

[art297]

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:

http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#p0xwc73ekemkapn1by...

[FriedEgg]

data want;

set sashelp.class( rename=( age=hour1 height=hour2 weight=hour3 ) );

mean=mean(of hour:);

run;

[art297]

The OP wanted to do it in proc sql!

[FriedEgg]

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.