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(stat:) as sumEQerror /* get error with this logic */
from sample;
quit;
@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
%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;
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;
@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
%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;
It doesn't work for me, but if it works for you that's good
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 stat:);
run;
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.