Help using Base SAS procedures

Proc SQL: SUM multiple variables with the same prefix

Reply
Occasional Contributor
Posts: 8

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;

Super User
Posts: 17,829

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;

Occasional Contributor
Posts: 8

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

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

data sample;

  stat1 = 10;

  stat2 = 4;

  stat3 = 1;

run;

%macro sum1; %do n=1 %to 3; ,stat&n %end; %mend sum1;

proc sql;

  select

     SUM(0 %sum1) as sumEQ15

    ,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;

Super User
Posts: 17,829

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,649

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
Ask a Question
Discussion stats
  • 5 replies
  • 6368 views
  • 1 like
  • 3 in conversation