## Sum

Solved
Super Contributor
Posts: 713

# Sum

Hi ,

There are 30 variables in dataset. of which one variable is id and 28 variables have suffix      _spend. Is there any effective way where i can do a sum of each variable without of having to explictly mention each variable grouped at id.

say i have id, var1_spend, var2_spend,var3_spend.....var28_spend.

proc sql;

select id,sum(var1_spend),sum(var2_spend),...sum(var28_spend),

from table1

group by id;

quit;

is there any other way?

Accepted Solutions
Solution
‎01-31-2013 08:13 PM
PROC Star
Posts: 8,164

## Re: Sum

If 'var' is unique, you could just get them by creating a macro variable in an earlier sql step.  E.g.,:

data table1;

input id var1_spend var2_spend;

cards;

1 2 2

1 2 2

1 2 2

2 3 3

2 3 3

2 3 3

;

proc sql noprint;

select 'sum('||strip(name)||') as '||strip(name)||'_sum'

into :vars separated by ','

from dictionary.columns

where libname='WORK' and

memname='TABLE1' and

upcase(name) like 'VAR%'

;

create table want as

select id, &vars.

from table1

group by id

;

quit;

All Replies
Solution
‎01-31-2013 08:13 PM
PROC Star
Posts: 8,164

## Re: Sum

If 'var' is unique, you could just get them by creating a macro variable in an earlier sql step.  E.g.,:

data table1;

input id var1_spend var2_spend;

cards;

1 2 2

1 2 2

1 2 2

2 3 3

2 3 3

2 3 3

;

proc sql noprint;

select 'sum('||strip(name)||') as '||strip(name)||'_sum'

into :vars separated by ','

from dictionary.columns

where libname='WORK' and

memname='TABLE1' and

upcase(name) like 'VAR%'

;

create table want as

select id, &vars.

from table1

group by id

;

quit;

Super Contributor
Posts: 713

## Re: Sum

they are not unique.But they all have a common suffix    _spend.

Super Contributor
Posts: 713

## Re: Sum

And second thing is all the varibale names are huge...i will run to memory issue where the macro varible cannot hold more than 65k

Posts: 5,538

## Re: Sum

Replace upcase(name) like 'VAR%' in Art's query by upcase(name) like '%SPEND'

Variable names are limited to 32 characters. Each clause will thus be less than 100 characters, that's less than 2800 characters total into the macro variable.

PG

PG
Super Contributor
Posts: 713

## Re: Sum

Art..it worked ..thanks a bunch!

🔒 This topic is solved and locked.