Help using Base SAS procedures

Proc SQL: SUM multiple variables with the same prefix

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Proc SQL: SUM multiple variables with the same prefix

I'm trying to sum multiple variables within proc sql with the same prefix without having to list them all out.  Sample is below.  Looking for code so I don't have to write out "stat1,stat2,stat3" (my real code has stat1-stat30)

data sample;

  stat1 = 10;

  stat2 = 4;

  stat3 = 1;

run;

proc sql;

  select

    SUM(stat1,stat2,stat3) as sumEQ15

    ,SUM(stat1-stat3) as sumEQ9

    ,SUM(stat1--stat3) as sumEQ11

    ,SUM(statSmiley Happy as sumEQerror /* get error with this logic */

  from sample;

quit;


Accepted Solutions
Solution
‎08-23-2017 09:02 AM
Occasional Contributor
Posts: 8

Re: Proc SQL: SUM multiple variables with the same prefix

[ Edited ]

@Reeza wrote:

Unfortunately PROC SQL does not support variable lists.

Since your summation is the values in a row, you can consider a data step instead.

 

 

data want;
set sample;
sumeq15=sum(stat1, stat2, stat3);
sumeq9=sum(of stat1--stat3);
sumeqerror=sum(of stat;
run;

 

ok...thanks.  I worked around it with a macro (see below) so I didn't need to add an extra variable in the data set, but was hoping there was a simpler solution Smiley Happy

 

%macro sum1;
%do n=1 %to 3;
,stat&n
%end;
%mend sum1;
proc sql;
select
     SUM(0 %sum1) as SumByRow
from sample;
select
     SUM(SUM(0 %sum1) as SumAcrossAllRows
  from sample;
quit;

And @PGStats wrote:

As an alternative, you can transpose first and get closer to a mormalized data structure, and then use SQL :

 


data sample;
  id = 1;
  stat1 = 10;
  stat2 = 4;
  stat3 = 1;
run;
 
proc transpose data=sample out=sampleList;
by id;
var stat:; /* Or stat1 - stat3, or stat1 -- stat3 */
run;
 
proc sql;
create table want as
select id, sum(col1) as sumEQ
from sampleList
group by id;
quit;
 
proc print data=want noobs; run;

View solution in original post


All Replies
Super User
Posts: 19,832

Re: Proc SQL: SUM multiple variables with the same prefix

Unfortunately PROC SQL does not support variable lists.

Since your summation is the values in a row, you can consider a data step instead.

data want;

set sample;

sumeq15=sum(stat1, stat2, stat3);

sumeq9=sum(of stat1--stat3);

sumeqerror=sum(of statSmiley Happy;

run;

Solution
‎08-23-2017 09:02 AM
Occasional Contributor
Posts: 8

Re: Proc SQL: SUM multiple variables with the same prefix

[ Edited ]

@Reeza wrote:

Unfortunately PROC SQL does not support variable lists.

Since your summation is the values in a row, you can consider a data step instead.

 

 

data want;
set sample;
sumeq15=sum(stat1, stat2, stat3);
sumeq9=sum(of stat1--stat3);
sumeqerror=sum(of stat;
run;

 

ok...thanks.  I worked around it with a macro (see below) so I didn't need to add an extra variable in the data set, but was hoping there was a simpler solution Smiley Happy

 

%macro sum1;
%do n=1 %to 3;
,stat&n
%end;
%mend sum1;
proc sql;
select
     SUM(0 %sum1) as SumByRow
from sample;
select
     SUM(SUM(0 %sum1) as SumAcrossAllRows
  from sample;
quit;

And @PGStats wrote:

As an alternative, you can transpose first and get closer to a mormalized data structure, and then use SQL :

 


data sample;
  id = 1;
  stat1 = 10;
  stat2 = 4;
  stat3 = 1;
run;
 
proc transpose data=sample out=sampleList;
by id;
var stat:; /* Or stat1 - stat3, or stat1 -- stat3 */
run;
 
proc sql;
create table want as
select id, sum(col1) as sumEQ
from sampleList
group by id;
quit;
 
proc print data=want noobs; run;
Super User
Posts: 19,832

Re: Proc SQL: SUM multiple variables with the same prefix

It doesn't work for me, but if it works for you that's good Smiley Happy

I would recommend testing your code with multiple lines of data to ensure you obtain the expected results:

data sample;

  stat1 = 10;

  stat2 = 4;

  stat3 = 1;

  output;

  stat1 = 20;

  stat2 = 8;

  stat3 = 2;

  output;

run;

data want;

set sample;

sumeq15=sum(stat1, stat2, stat3);

sumeq9=sum(of stat1--stat3);

sumeqerror=sum(of statSmiley Happy;

run;

Occasional Contributor
Posts: 8

Re: Proc SQL: SUM multiple variables with the same prefix

it works with multiple lines of code depending on what you want.

%macro sum1;

%do n=1 %to 3;

,stat&n

%end;

%mend sum1;

proc sql;

select

     SUM(0 %sum1) as SumByRow

from sample;

select

     SUM(SUM(0 %sum1) as SumAcrossAllRows

  from sample;

quit;

Respected Advisor
Posts: 4,927

Re: Proc SQL: SUM multiple variables with the same prefix

As an alternative, you can transpose first and get closer to a mormalized data structure, and then use SQL :

data sample;

  id = 1;

  stat1 = 10;

  stat2 = 4;

  stat3 = 1;

run;

proc transpose data=sample out=sampleList;

by id;

var stat:; /* Or stat1 - stat3, or stat1 -- stat3 */

run;

proc sql;

create table want as

select id, sum(col1) as sumEQ

from sampleList

group by id;

quit;

proc print data=want noobs; run;

PG

PG
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 9376 views
  • 1 like
  • 3 in conversation