BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
UDcc123
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
UDcc123
Fluorite | Level 6

@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

5 REPLIES 5
Reeza
Super User

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;

UDcc123
Fluorite | Level 6

@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;
Reeza
Super User

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 stat:);

run;

UDcc123
Fluorite | Level 6

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;

PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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