Addition of Values for Current and Previous Values.

[KYW]

Hi all. I have a complicated problem regarding the sums of relevant values in different periods that needs to be solved but not sure how.

The simplified situation is like this:-
I have a table A with following fields:
Year, Month, Stock, Price.
Let's say from table A I have data from Year 2008 to 2011 with each year having 100 of different stocks with different prices each month.

With above data fields, I need to create a dataset B with the following fields:
Year, Month, Stock, Current_MTD_Price_Sum, Previous_MTD_Price_Sum, Current_YTD_Price_Sum, Previous_YTD_Price_Sum.

The Current_MTD_Price_Sum is the sum of prices of each stock from that month itself while Previous_MTD_Price_Sum refers to the price sum of the same stock in a year before.
Eg. If the year is 2009 and the month is April, the Current_MTD_Price_Sum would refer to the sum of prices of that stock Z in April 2009 while Previous_MTD_Price_Sum would show the sum of prices for April 2008 for the same stock Z.

Meanwhile, Current_YTD_Price_Sum would show the total sum for the stock from January to April 2010 if the current month is April and Current Year is 2010. Previous_YTD_Price_Sum would be the sum of prices for the stock from January to April 2009.

I have tried to use retain and sum statements but they can only add to a certain period. The tables may look simple but the process to get the correct values has been a great challenge for me so far.

Thanks for your time.

KYW

[ASASProgrammer]

Maybe not trying to generate B in one single data step make the task much easier.
Similar like this, but need some polishing:

proc sql;
create table B1 as
select stock, month, year, sum(price) as Current_MTD_Price_Sum
from A
group by stock, month, year;
quit;

proc sort data=b1;
by stock year month;
run;

data B;
set B1;
retain Previous_MTD_Price_Sum
Previous_YTD_Price_Sum
Current_YTD_Price_Sum
;
by stock year month;
if first.stock then Previous_YTD_Price_Sum=0;
if first.year then do;
Current_YTD_Price_Sum=Current_MTD_Price_Sum;
end;
Current_YTD_Price_Sum+Current_MTD_Price_Sum;
output;
Previous_MTD_Price_Sum=Current_MTD_Price_Sum;
if last.year then
Previous_YTD_Price_Sum=Current_YTD_Price_Sum;
run;

[KYW]

Hi ShareMyAnswer. Thanks for your suggestion. Perhaps breaking down the problem into parts will make it easier to find the solution.

Will give it a try.

[Ksharp]

Can you plz post some dummy data into the forum?
It spend me lots of time to input data.


[pre]
data temp;
input year stock $ month price ;
cards;
2004 CVX0504 1 18.2
2004 CVX0504 1 19.61
2004 CVX0504 2 18.38
2004 CVX0504 2 19.58
2004 CVX0504 3 19.61
2004 CVX0504 4 18.38
2004 CVX0504 5 19.58
2004 CVX0504 6 19.58
2004 CVX0504 7 19.58
2004 CVX0504 8 19.58
2004 CVX0504 9 19.58
2004 CVX0504 10 19.58
2004 CVX0504 11 19.58
2004 CVX0504 12 19.58
2005 CVX0504 1 18.55
2005 CVX0504 1 19.67
2005 CVX0504 2 19.37
2005 CVX0504 2 20.21
2005 CVX0504 3 19.61
2005 CVX0504 4 18.38
2005 CVX0504 5 19.58
2005 CVX0504 6 19.58
2005 CVX0504 7 19.58
2005 CVX0504 8 19.58
2005 CVX0504 9 19.58
2005 CVX0504 10 19.58
2005 CVX0504 11 19.58
2005 CVX0504 12 19.58
2006 CVX0604 1 19.04
2006 CVX0604 1 17.5
2006 CVX0604 2 18
2006 CVX0604 2 17.61
2006 CVX0604 3 19.61
2006 CVX0604 4 18.38
2006 CVX0604 5 19.58
2006 CVX0604 6 19.58
2006 CVX0604 7 19.58
2006 CVX0604 8 19.58
2006 CVX0604 9 19.58
2006 CVX0604 10 19.58
2006 CVX0604 11 19.58
2006 CVX0604 12 19.58
2007 CVX0604 1 18.09
2007 CVX0604 1 17.35
2007 CVX0604 2 17.9
2007 CVX0604 2 17.9
2007 CVX0604 3 19.61
2007 CVX0604 4 18.38
2007 CVX0604 5 19.58
2007 CVX0604 6 19.58
2007 CVX0604 7 19.58
2007 CVX0604 8 19.58
2007 CVX0604 9 19.58
2007 CVX0604 10 19.58
2007 CVX0604 11 19.58
2007 CVX0604 12 19.58
;
run;
proc sort data=temp;
by stock year month;
run;
data want;
set temp;
by stock year month ;
if first.month then current_mtd_price_sum=0;
if first.year then current_ytd_price_sum=0;
current_mtd_price_sum+price;
current_ytd_price_sum+price;
if last.month then output;
run;
data want;
set want;
previous_mtd_price_sum=lag12(current_mtd_price_sum);
previous_ytd_price_sum=lag12(current_ytd_price_sum);
run;
[/pre]


Ksharp

[KYW]

Thanks again KSharp and your efforts in creating dummy data. Your guidance is very helpful.
One question I have is that if lag12() is used to get the values 1 year back, what if I need to further split the month into weeks? We know that 1 month has around 4 or 5 weeks. Can lag12( ) still be used in this situation or do we have to another solution as I don't think lag4 will solve the problem.

Thank you.

[Ksharp]

Yes.You are right.For this situation, the format about week such as weekdate. will help you some.Give some dummy data,I will try it.


Ksharp

[KYW]

Okay. Let's say below is the dummy data with the following fields:
Year, Month, Week, Stock, Price.
I will just take 1 stock for easy understanding. We know that 1 year has 52 weeks. So the weeks are counted as a continuation from week 1 until week 52 (1 year) corresponding to the relevant months. So now that instead of just summing up the MTD and YTD prices for the stock, we need to split it to the week.

Eg. Previous_WTD_Price_SUM for Week 18 (Which falls on April) in Year 2005 is equivalent to the Current_WTD_Price_Sum for Week 18 in Year 2004 while maintaining what we have already done in Current and Previous MTD and YTD Price Values.

What makes thing more complicated is that the Year, Month and Week are all characters with a '0' to make it looks double digits.

2004 01 01 A 21
2004 01 02 A 25
2004 01 03 A 22
2004 01 04 A 23
2004 01 05 A 24
2004 02 06 A 27
2004 02 07 A 25
2004 02 08 A 23
2004 02 09 A 23
2004 03 10 A 22
2004 03 11 A 22
2004 03 12 A 22
2004 03 13 A 26
2004 03 14 A 24
2004 04 15 A 23
2004 04 16 A 25
2004 04 17 A 22
2004 04 18 A 25
2004 05 19 A 22
2004 05 20 A 25
2004 05 21 A 21
2004 05 22 A 23
2004 05 23 A 21
2004 06 24 A 21
2004 06 25 A 22
2004 06 26 A 22
2004 06 27 A 23
2004 07 28 A 21
2004 07 29 A 24
2004 07 30 A 26
2004 07 31 A 27
2004 07 32 A 28
2004 08 33 A 27
2004 08 34 A 27
2004 08 35 A 26
2004 08 36 A 23
2004 08 37 A 24
2004 09 38 A 21
2004 09 39 A 25
2004 09 40 A 22
2004 09 41 A 23
2004 10 42 A 22
2004 10 43 A 25
2004 10 44 A 27
2004 10 45 A 24
2004 11 46 A 23
2004 11 47 A 21
2004 11 48 A 22
2004 11 49 A 20
2004 12 50 A 21
2004 12 51 A 25
2004 12 52 A 22
================================
2005 01 01 A 21
2005 01 02 A 25
2005 01 03 A 22
2005 01 04 A 23
2005 01 05 A 24
2005 02 06 A 27
2005 02 07 A 25
2005 02 08 A 23
2005 02 09 A 23
2005 03 10 A 22
2005 03 11 A 22
2005 03 12 A 22
2005 03 13 A 26
2005 03 14 A 24
2005 04 15 A 23
2005 04 16 A 25
2005 04 17 A 22
2005 04 18 A 25
2005 05 19 A 22
2005 05 20 A 25
2005 05 21 A 21
2005 05 22 A 23
2005 05 23 A 21
2005 06 24 A 21
2005 06 25 A 22
2005 06 26 A 22
2005 06 27 A 23
2005 07 28 A 21
2005 07 29 A 24
2005 07 30 A 26
2005 07 31 A 27
2005 07 32 A 28
2005 08 33 A 27
2005 08 34 A 27
2005 08 35 A 26
2005 08 36 A 23
2005 08 37 A 24
2005 09 38 A 21
2005 09 39 A 25
2005 09 40 A 22
2005 09 41 A 23
2005 10 42 A 22
2005 10 43 A 25
2005 10 44 A 27
2005 10 45 A 24
2005 11 46 A 23
2005 11 47 A 21
2005 11 48 A 22
2005 11 49 A 20
2005 12 50 A 21
2005 12 51 A 25
2005 12 52 A 22

[Ksharp]

OK.It looks like merge several tables.
Since you do not post the output You want.So check it to see whether it is what you need.


[pre]
data temp;
input _year $ _month $ _week $ stock $ price ;
cards;
2004 01 01 A 21
2004 01 02 A 25
2004 01 03 A 22
2004 01 04 A 23
2004 01 05 A 24
2004 02 06 A 27
2004 02 07 A 25
2004 02 08 A 23
2004 02 09 A 23
2004 03 10 A 22
2004 03 11 A 22
2004 03 12 A 22
2004 03 13 A 26
2004 03 14 A 24
2004 04 15 A 23
2004 04 16 A 25
2004 04 17 A 22
2004 04 18 A 25
2004 05 19 A 22
2004 05 20 A 25
2004 05 21 A 21
2004 05 22 A 23
2004 05 23 A 21
2004 06 24 A 21
2004 06 25 A 22
2004 06 26 A 22
2004 06 27 A 23
2004 07 28 A 21
2004 07 29 A 24
2004 07 30 A 26
2004 07 31 A 27
2004 07 32 A 28
2004 08 33 A 27
2004 08 34 A 27
2004 08 35 A 26
2004 08 36 A 23
2004 08 37 A 24
2004 09 38 A 21
2004 09 39 A 25
2004 09 40 A 22
2004 09 41 A 23
2004 10 42 A 22
2004 10 43 A 25
2004 10 44 A 27
2004 10 45 A 24
2004 11 46 A 23
2004 11 47 A 21
2004 11 48 A 22
2004 11 49 A 20
2004 12 50 A 21
2004 12 51 A 25
2004 12 52 A 22
2005 01 01 A 21
2005 01 02 A 25
2005 01 03 A 22
2005 01 04 A 23
2005 01 05 A 24
2005 02 06 A 27
2005 02 07 A 25
2005 02 08 A 23
2005 02 09 A 23
2005 03 10 A 22
2005 03 11 A 22
2005 03 12 A 22
2005 03 13 A 26
2005 03 14 A 24
2005 04 15 A 23
2005 04 16 A 25
2005 04 17 A 22
2005 04 18 A 25
2005 05 19 A 22
2005 05 20 A 25
2005 05 21 A 21
2005 05 22 A 23
2005 05 23 A 21
2005 06 24 A 21
2005 06 25 A 22
2005 06 26 A 22
2005 06 27 A 23
2005 07 28 A 21
2005 07 29 A 24
2005 07 30 A 26
2005 07 31 A 27
2005 07 32 A 28
2005 08 33 A 27
2005 08 34 A 27
2005 08 35 A 26
2005 08 36 A 23
2005 08 37 A 24
2005 09 38 A 21
2005 09 39 A 25
2005 09 40 A 22
2005 09 41 A 23
2005 10 42 A 22
2005 10 43 A 25
2005 10 44 A 27
2005 10 45 A 24
2005 11 46 A 23
2005 11 47 A 21
2005 11 48 A 22
2005 11 49 A 20
2005 12 50 A 21
2005 12 51 A 25
2005 12 52 A 22
;
run;
data temp;
set temp;
year=input(_year,best4.);
month=input(_month,best2.);
week=input(_week,best2.);
drop _:;
run;

proc sort data=temp;
by stock year month week;
run;
data result month(drop=price current_ytd_price_sum week) year(drop=price current_mtd_price_sum month week);
set temp;
by stock year month ;
if first.month then current_mtd_price_sum=0;
if first.year then current_ytd_price_sum=0;
current_mtd_price_sum+price;
current_ytd_price_sum+price;
if last.month then output month;
if last.year then output year;
output result;
run;
data week(keep=stock year week previous_wtd_price_sum);
set result(rename=(price=previous_wtd_price_sum));
year=year+1;
run;
data month(rename=(current_mtd_price_sum=previous_mtd_price_sum));
set month;
year=year+1;
run;
data year(rename=(current_ytd_price_sum=previous_ytd_price_sum));
set year;
year=year+1;
run;
data result(where =(price is not missing));
merge result year;
by stock year;
run;
data result(where =(price is not missing));
merge result month;
by stock year month;
run;
data result(where =(price is not missing));
merge result week;
by stock year week;
run;

[/pre]



Ksharp

[KYW]

Hi KSharp. Thank you for your codes. It's been very helpful in solving the problem.

I have tried your codes below and I found that the Previous_YTD_Price_Sum values do not correspond to the Current_YTD_Price_Sum (which should be the correct way) as the Previous_YTD_Price_Sum only showed the total value of 1220 in every cell instead of the gradual addition of values as seen in Current_YTD_Price_Sum.

Thank you once again for your guidance.

KYW

[Ksharp]

Oh.I understand what you need.How about this:
[pre]
data temp;
input _year $ _month $ _week $ stock $ price ;
cards;
2004 01 01 A 21
2004 01 02 A 25
2004 01 03 A 22
2004 01 04 A 23
2004 01 05 A 24
2004 02 06 A 27
2004 02 07 A 25
2004 02 08 A 23
2004 02 09 A 23
2004 03 10 A 22
2004 03 11 A 22
2004 03 12 A 22
2004 03 13 A 26
2004 03 14 A 24
2004 04 15 A 23
2004 04 16 A 25
2004 04 17 A 22
2004 04 18 A 25
2004 05 19 A 22
2004 05 20 A 25
2004 05 21 A 21
2004 05 22 A 23
2004 05 23 A 21
2004 06 24 A 21
2004 06 25 A 22
2004 06 26 A 22
2004 06 27 A 23
2004 07 28 A 21
2004 07 29 A 24
2004 07 30 A 26
2004 07 31 A 27
2004 07 32 A 28
2004 08 33 A 27
2004 08 34 A 27
2004 08 35 A 26
2004 08 36 A 23
2004 08 37 A 24
2004 09 38 A 21
2004 09 39 A 25
2004 09 40 A 22
2004 09 41 A 23
2004 10 42 A 22
2004 10 43 A 25
2004 10 44 A 27
2004 10 45 A 24
2004 11 46 A 23
2004 11 47 A 21
2004 11 48 A 22
2004 11 49 A 20
2004 12 50 A 21
2004 12 51 A 25
2004 12 52 A 22
2005 01 01 A 21
2005 01 02 A 25
2005 01 03 A 22
2005 01 04 A 23
2005 01 05 A 24
2005 02 06 A 27
2005 02 07 A 25
2005 02 08 A 23
2005 02 09 A 23
2005 03 10 A 22
2005 03 11 A 22
2005 03 12 A 22
2005 03 13 A 26
2005 03 14 A 24
2005 04 15 A 23
2005 04 16 A 25
2005 04 17 A 22
2005 04 18 A 25
2005 05 19 A 22
2005 05 20 A 25
2005 05 21 A 21
2005 05 22 A 23
2005 05 23 A 21
2005 06 24 A 21
2005 06 25 A 22
2005 06 26 A 22
2005 06 27 A 23
2005 07 28 A 21
2005 07 29 A 24
2005 07 30 A 26
2005 07 31 A 27
2005 07 32 A 28
2005 08 33 A 27
2005 08 34 A 27
2005 08 35 A 26
2005 08 36 A 23
2005 08 37 A 24
2005 09 38 A 21
2005 09 39 A 25
2005 09 40 A 22
2005 09 41 A 23
2005 10 42 A 22
2005 10 43 A 25
2005 10 44 A 27
2005 10 45 A 24
2005 11 46 A 23
2005 11 47 A 21
2005 11 48 A 22
2005 11 49 A 20
2005 12 50 A 21
2005 12 51 A 25
2005 12 52 A 22
;
run;
data temp;
set temp;
year=input(_year,best4.);
month=input(_month,best2.);
week=input(_week,best2.);
drop _:;
run;

proc sort data=temp;
by stock year month week;
run;
data result month(drop=price current_ytd_price_sum week) year(drop=price current_mtd_price_sum week);
set temp;
by stock year month ;
if first.year then current_ytd_price_sum=0;
if first.month then current_mtd_price_sum=0;
current_ytd_price_sum+price;
current_mtd_price_sum+price;
current_wtd_price_sum=price;

run;
data week(keep=stock year week previous_wtd_price_sum);
set result(rename=(current_wtd_price_sum=previous_wtd_price_sum));
year=year+1;
run;
data month(rename=(current_mtd_price_sum=previous_mtd_price_sum));
set month;
year=year+1;
run;
data year(rename=(current_ytd_price_sum=previous_ytd_price_sum));
set year;
year=year+1;
run;

data result(where =(price is not missing));
merge result year;
by stock year month;
run;
data result(where =(price is not missing));
merge result month;
by stock year month;
run;
data result(where =(price is not missing));
merge result week;
by stock year week;
run;
[/pre]



Ksharp

[KYW]

Hi KSharp. Thank you very much. This is the answer that I've been looking for so long. Yet you did it in a simple way and yet easy to understand.

Really appreciate what you have been doing to help those who dabble in SAS programming, including me.