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?
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;
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;
they are not unique.But they all have a common suffix _spend.
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
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
Art..it worked ..thanks a bunch!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.