BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASPhile
Quartz | Level 8


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?

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

5 REPLIES 5
art297
Opal | Level 21

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
Quartz | Level 8

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

SASPhile
Quartz | Level 8

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
Opal | Level 21

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
SASPhile
Quartz | Level 8

Art..it worked ..thanks a bunch!

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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