topic How to create a new variable which contains sum per quarter in New SAS User

How to create a new variable which contains sum per quarter

adrfinance — Thu, 06 Feb 2020 09:37:41 GMT

I have a dataset that looks like:

qy balance

2010 Q1 10

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 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?

Re: How to create a new variable which contains sum per quarter

PeterClemmensen — Thu, 06 Feb 2020 09:42:09 GMT

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;

Re: How to create a new variable which contains sum per quarter

ed_sas_member — Thu, 06 Feb 2020 09:42:22 GMT

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;