BookmarkSubscribeRSS Feed
daveryBBW
Calcite | Level 5
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!
1 REPLY 1
deleted_user
Not applicable
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 6218 views
  • 0 likes
  • 2 in conversation