topic selecting similarly named columns in proc sql in SAS Procedures

selecting similarly named columns in proc sql

asishgautam — Mon, 26 Sep 2011 17:24:21 GMT

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.

selecting similarly named columns in proc sql

art297 — Mon, 26 Sep 2011 17:47:51 GMT

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;

selecting similarly named columns in proc sql

asishgautam — Mon, 26 Sep 2011 18:49:42 GMT

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.

selecting similarly named columns in proc sql

art297 — Mon, 26 Sep 2011 21:37:42 GMT

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#p0xwc73ekemkapn1byhceqrwrisb.htm

selecting similarly named columns in proc sql

FriedEgg — Mon, 26 Sep 2011 23:01:46 GMT

data want;

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

mean=mean(of hour:);

run;

selecting similarly named columns in proc sql

art297 — Mon, 26 Sep 2011 23:04:15 GMT

The OP wanted to do it in proc sql!

Re: selecting similarly named columns in proc sql

FriedEgg — Mon, 26 Sep 2011 23:16:06 GMT

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.