PROC SQL ... GROUP BY question

daveryBBW — Mon, 30 Mar 2009 14:46:16 GMT

Does anyone know if there is any shortcut in a PROC SQL ... GROUP BY statement so that if you have more than just a few columns that you need to group by, that you can do something like this:

PROC SQL
CREATE TABLE x AS
SELECT col1, col2 ... col10, sum(numcol) as sumNumCol
FROM y
GROUP BY 1-10
;
QUIT;

I know that you can write the GROUP BY out (col1, col2, col3 ... etc.) as well as write it as GROUP BY 1, 2, 3, 4, 5 ... but if you have a whole slew of columns that you have to group by, is there a shortcut similar to what you can do in a DATA step with setting up an ARRAY but with the numeric representation instead of the column names.

I'm sure that there is probably a more SAS friendly way (i.e. non PROC SQL) to do what I'm trying to do however PROC SQL is really the only way to do COUNT(DISTINCT column) in a semi-efficient manner.

My guess is that I'm probably just going to have to continue typing everything out, but I figured I'd ask the question. If you never ask, you'll never truely know. 🙂

Thanks!

Re: PROC SQL ... GROUP BY question

deleted_user — Mon, 30 Mar 2009 15:09:02 GMT

Not sure about the sql shortcut but you could write a little bit of macro to write the code for you. Something like:

%do i=1 %to 9;
col&i.,
%end;
col10

topic PROC SQL ... GROUP BY question in SAS Procedures

PROC SQL ... GROUP BY question

Re: PROC SQL ... GROUP BY question