- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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