Quartz | Level 8

## proc sql group by summing several variables

Dear experts,

given the following input data:

data inputs x var1 var2 var3 var4 var5;
datalines;
20 5 2 4 5 4
25 12 56 13 44 4
20 5 2 4 5 4
25 12 56 13 44 4
20 5 2 4 5 4
25 12 56 13 44 4
. 2 5 6 5 4
;

I get the following output:

proc sql; create table output as select
x,
sum(var1) as var1,
sum(var2) as var2,
sum(var3) as var3,
sum(var4) as var3,
sum(var5) as var4
from inputs group by 1 ;quit;

how is possible to get the same result but saying: do the sum from var1 to var_n_?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: proc sql group by summing several variables

Use a variable list in the var statement of proc means/summary:

``var var1-var5;``

If number of variables is stored in a macro variable N, replace var5 with var&N.

4 REPLIES 4
Super User

## Re: proc sql group by summing several variables

Either a macro or proc means.

Proc means is much easier.

Super User

## Re: proc sql group by summing several variables

Use a variable list in the var statement of proc means/summary:

``var var1-var5;``

If number of variables is stored in a macro variable N, replace var5 with var&N.

Quartz | Level 8

## Re: proc sql group by summing several variables

hervorragend! 😉

Obsidian | Level 7

## Re: proc sql group by summing several variables

Or if you are going to stay in proc sql

%macro summing(n= );

proc sql; create table output as select
x, %do i = 1 %to &n; sum(var&i) as var&i

%if &i = &n %then %do; from inputs group by 1; %end;

%else %do; , %end;

quit;

%mend;

%summing(n=5);

Also it would probably make sense to use select distinct rather than just select, and i'm not quite sure why you are using group by 1

rather than something like group by x (as this is the only variable which are leaving in the output table). But that's besides the point. Hope this helps.

Discussion stats
• 4 replies
• 1604 views
• 0 likes
• 4 in conversation