topic Re: Sum in SAS Programming

Sum

SASPhile — Fri, 01 Feb 2013 00:46:12 GMT

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.

so instead of writing

proc sql;

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

from table1

group by id;

quit;

is there any other way?

Re: Sum

art297 — Fri, 01 Feb 2013 01:13:21 GMT

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

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;

Re: Sum

SASPhile — Fri, 01 Feb 2013 02:02:43 GMT

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

Re: Sum

SASPhile — Fri, 01 Feb 2013 02:03:53 GMT

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

Re: Sum

PGStats — Fri, 01 Feb 2013 02:21:08 GMT

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.

Re: Sum

SASPhile — Fri, 01 Feb 2013 03:34:57 GMT

Art..it worked ..thanks a bunch!