DATA Step, Macro, Functions and more

Sum

Accepted Solution Solved
Reply
Super Contributor
Posts: 673
Accepted Solution

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.

   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?


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

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;

View solution in original post


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

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: 673

Re: Sum

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

Super Contributor
Posts: 673

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

Respected Advisor
Posts: 4,932

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: 673

Re: Sum

Art..it worked ..thanks a bunch!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 188 views
  • 3 likes
  • 3 in conversation