Help using Base SAS procedures

proc sql group by summing several variables

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 127
Accepted Solution

proc sql group by summing several variables

[ Edited ]

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.


Accepted Solutions
Solution
‎08-25-2016 10:13 AM
Super User
Posts: 6,972

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 17,960

Re: proc sql group by summing several variables

Either a macro or proc means. 

Proc means is much easier. 

Solution
‎08-25-2016 10:13 AM
Super User
Posts: 6,972

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 127

Re: proc sql group by summing several variables

hervorragend! ;-)

Frequent Contributor
Posts: 83

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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