topic Re: How to generate a new variable which contains the total sum of a column? in New SAS User

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

adrfinance — Thu, 06 Feb 2020 10:04:54 GMT

I have a dataset that looks like:

qy balance

2010 Q1 50

2010 Q2 100

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 Q2 100 1000

2010 Q3 200 1000

How can I do that?

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

ed_sas_member — Thu, 06 Feb 2020 10:11:52 GMT

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,

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

PeterClemmensen — Thu, 06 Feb 2020 11:06:07 GMT

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;

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

adrfinance — Thu, 06 Feb 2020 10:46:06 GMT

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

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

PeterClemmensen — Thu, 06 Feb 2020 11:06:23 GMT

Indeed 🙂 Good catch