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!
... View more