Sum

[SASPhile]

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?

[art297 · Accepted solution]

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;

[SASPhile]

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

[SASPhile]

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

[PGStats]

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

[SASPhile]

Art..it worked ..thanks a bunch!