topic Re: proc sql group by summing several variables in SAS Procedures

proc sql group by summing several variables

Sir_Highbury — Thu, 25 Aug 2016 13:06:41 GMT

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_?

Thank in advance, SH.

Re: proc sql group by summing several variables

Reeza — Thu, 25 Aug 2016 13:29:22 GMT

Either a macro or proc means.

Proc means is much easier.

Re: proc sql group by summing several variables

Kurt_Bremser — Thu, 25 Aug 2016 13:36:34 GMT

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.

Re: proc sql group by summing several variables

Sir_Highbury — Thu, 25 Aug 2016 14:14:44 GMT

hervorragend! 😉

Re: proc sql group by summing several variables

overmar — Thu, 25 Aug 2016 18:47:23 GMT

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.