How to create a new variable which contains sum per quarter

[adrfinance]

I have a dataset that looks like:

qy             balance

2010 Q1   10

2010 Q1   10

2010 Q1   10

2010 Q2   20

2010 Q2   20

2010 Q3   20

2010 Q4   50

2011 Q1   100

2011 Q2   200

2011 Q3   300

and I would like to create a new variable which contains the sum per quarter. Desired output:

qy             balance    sum_balance

2010 Q1   10             30

2010 Q1   10             30

2010 Q1   10             30

2010 Q2   20             60

2010 Q2   20             60

2010 Q3   20             60

2010 Q4   50             50

2011 Q1   100           100

2011 Q2   200           200

2011 Q3   300           300

How can I do that?

[PeterClemmensen · Accepted solution]

One way..

data have;
input qy $ 1-7 balance;
datalines;
2010 Q1 10
2010 Q1 10
2010 Q1 10
2010 Q2 20
2010 Q2 20
2010 Q3 20
2010 Q4 50
2011 Q1 100
2011 Q2 200
2011 Q3 300
;

data want;
    do until (last.qy);
        set have;
        by qy;
        sum_balance + balance;
    end;
    do until (last.qy);
        set have;
        by qy;
        output;
    end;
    sum_balance=0;
run;

[ed_sas_member]

Hi @adrfinance 

Here is one way to do this:

data have;
	input qy $ 1-8 balance;
	datalines;
2010 Q1   10
2010 Q1   10
2010 Q1   10
2010 Q2   20
2010 Q2   20
2010 Q3   20
2010 Q4   50
2011 Q1   100
2011 Q2   200
2011 Q3   300
;
run;

proc sql;
	create table want as
	select qy, balance, sum(balance) as sum_balance
	from have
	group by qy;
quit;