Do loop in PROC SQL

[Ujjawal]

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( )));

[PGStats · Accepted solution]

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;

[Ujjawal]

Thank you so much. It works like charm.