How to generate a new variable which contains the total sum of a column?

[adrfinance]

I have a dataset that looks like:

qy                balance

2010 Q1      50

2010 Q1      50

2010 Q2      100

2010 Q2      100

2010 Q2      100

2010 Q3       200

2010 Q3       200

2010 Q3       200

and I would like to create a new column which contains the total sum i.e. desired output:

qy                balance         total_balance

2010 Q1      50                  1000

2010 Q1      50                  1000

2010 Q2      100                1000

2010 Q2      100                1000

2010 Q2      100                1000

2010 Q3       200               1000

2010 Q3       200               1000

2010 Q3       200               1000

How can I do that?

[ed_sas_member · Accepted solution]

Hi @adrfinance 

Same logic as previously. You just need to remove the 'group by qy' clause:

data have;
	input qy $ 1-8 balance;
	datalines;
2010 Q1      50
2010 Q1      50
2010 Q2      100
2010 Q2      100
2010 Q2      100
2010 Q3       200
2010 Q3       200
2010 Q3       200
;
run;

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

Best,

[PeterClemmensen]

In the data step approach..

data want;
    do until (lr1);
        set have end=lr1;
        sum_balance + balance;
    end;
    do until (lr2);
        set have end=lr2;
        output;
    end;
run;

[adrfinance]

you have a typo in your code with the "haveend" or not?

[PeterClemmensen]

Indeed 🙂 Good catch