I want the following PROC SQL code to be run for multiple variables. Till now, it is designed for a single variable i.e. Balance. I want this to be run for multiple variables in a loop. The code calculates ratio and total sum of balance for a time period.
Data Sets used in the code -
data example1;
input ID Months Revenue Balance;
cards;
101 1 3 90
101 2 33 68
101 3 22 51
101 4 3 90
101 5 33 65
101 6 22 54
101 7 3 92
101 8 33 65
101 9 22 55
101 10 3 96
101 11 33 65
101 12 22 54
102 1 100 18
102 2 58 62
102 3 95 97
102 4 100 18
102 5 58 65
102 6 95 92
102 7 100 11
102 8 58 62
102 9 95 92
102 10 100 15
102 11 58 60
102 12 95 91
;
data trend;
input x y;
cards;
1 3
4 6
1 6
7 9
1 9
10 12
1 12
;
run;
proc sql noprint;
select cat("sum(case when Months =",y," then Balance else . end)", "/ sum(case when Months =",x," then Balance else . end)
as Balance_",y,"_",x,"_ratio")
into :ratio separated by ","
from trend;
select cat("sum(case when ", x,"<= Months <=",y, " then Balance else . end) as Balance_",x,"_",y,"_Sum")
into :loop separated by ","
from trend;
create table Output_trend as
select ID, &ratio., &loop.,
from example1
group by ID;
quit;
For example : Variables are specified in the following "vars" macro paramer and then run the above PROC SQL code for the variables mentioned in the macro variable.
%let vars = Balance Revenue;
%let n=%sysfunc(countw(&vars,%str( )));
You can do this without macro programming. Transpose your dataset to a long table format and summarize your data in a single query.
data example1;
input ID Month Revenue Balance;
cards;
101 1 3 90
101 2 33 68
101 3 22 51
101 4 3 90
101 5 33 65
101 6 22 54
101 7 3 92
101 8 33 65
101 9 22 55
101 10 3 96
101 11 33 65
101 12 22 54
102 1 100 18
102 2 58 62
102 3 95 97
102 4 100 18
102 5 58 65
102 6 95 92
102 7 100 11
102 8 58 62
102 9 95 92
102 10 100 15
102 11 58 60
102 12 95 91
;
data trends;
input m1 m2;
cards;
1 3
4 6
1 6
7 9
1 9
10 12
1 12
;
proc transpose data=example1 out=ex1 name=variable prefix=x;
by id month;
run;
proc sql;
create table trendList as
select variable, id, m1, m2
from trends cross join (select unique id, variable from ex1);
create table exTrends as
select catx("_", t.variable, t.m2, t.m1, "Ratio") as label length=32,
id, variable, m2, m1,
(select sum(x1) from ex1
where t.m1=month and t.id=id and t.variable=variable) /
(select sum(x1) from ex1
where t.m2=month and t.id=id and t.variable=variable) as value
from trendList as t
union all
select catx("_", t.variable, t.m2, t.m1, "Sum") as label length=32,
id, variable, m2, m1,
(select sum(x1) from ex1
where month between t.m1 and t.m2 and t.id=id and t.variable=variable) as value
from trendList as t
order by id, variable, m2, m1, label;
quit;
proc transpose data=exTrends out=exTable(drop=_name_);
by id;
id label;
var value;
run;
proc print data=exTable noobs; run;
You can do this without macro programming. Transpose your dataset to a long table format and summarize your data in a single query.
data example1;
input ID Month Revenue Balance;
cards;
101 1 3 90
101 2 33 68
101 3 22 51
101 4 3 90
101 5 33 65
101 6 22 54
101 7 3 92
101 8 33 65
101 9 22 55
101 10 3 96
101 11 33 65
101 12 22 54
102 1 100 18
102 2 58 62
102 3 95 97
102 4 100 18
102 5 58 65
102 6 95 92
102 7 100 11
102 8 58 62
102 9 95 92
102 10 100 15
102 11 58 60
102 12 95 91
;
data trends;
input m1 m2;
cards;
1 3
4 6
1 6
7 9
1 9
10 12
1 12
;
proc transpose data=example1 out=ex1 name=variable prefix=x;
by id month;
run;
proc sql;
create table trendList as
select variable, id, m1, m2
from trends cross join (select unique id, variable from ex1);
create table exTrends as
select catx("_", t.variable, t.m2, t.m1, "Ratio") as label length=32,
id, variable, m2, m1,
(select sum(x1) from ex1
where t.m1=month and t.id=id and t.variable=variable) /
(select sum(x1) from ex1
where t.m2=month and t.id=id and t.variable=variable) as value
from trendList as t
union all
select catx("_", t.variable, t.m2, t.m1, "Sum") as label length=32,
id, variable, m2, m1,
(select sum(x1) from ex1
where month between t.m1 and t.m2 and t.id=id and t.variable=variable) as value
from trendList as t
order by id, variable, m2, m1, label;
quit;
proc transpose data=exTrends out=exTable(drop=_name_);
by id;
id label;
var value;
run;
proc print data=exTable noobs; run;
Thank you so much. It works like charm.
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.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.