Accumulating no of - over years

[Jahanzaib]

I have one variable name ltdebt1. I have values for number of companies from year 2000-2015.

I want to create a new variable that accumulate value of year 2001 and 2002 and put that in front of year 2000 for that company. same way for 2001(accumulate 2002+2003) so on (We cannot calculate 2014 and 2015 that  I dont need). How can I do that? I am attaching

How can I do that? I am attaching files here both in excel and csv format. 

[Kurt_Bremser]

This is how you should post your example data:

data have;
infile cards dlm=',' dsd;
input company_key data_year data_date:yymmdd8. ltdebt1 company_name:$30. iso_code:$3. sic;
format data_date yymmddn8.;
cards;
16538,2000,20001231,1783,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2001,20011231,2010,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2002,20021231,4281,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2003,20031231,4879,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2004,20041231,4969,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2005,20051231,3563,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2006,20061231,6151,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2007,20071231,3878,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2008,20081231,4255,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2009,20091231,7828,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2010,20101231,8704,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2011,20111231,8562,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2012,20121231,8157,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2013,20131231,10218,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2014,20141231,9781,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2015,20151231,13238,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16590,2000,20001231,3976,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2001,20011231,731,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2002,20021231,3270,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2003,20031231,2436,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2004,20041231,1400,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2005,20051231,376,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2006,20061231,1089,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2007,20071231,2191,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2008,20081231,1687,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2009,20091231,,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2010,20101231,8459,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2011,20111231,617,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2012,20121231,5311,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2013,20131231,500,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2014,20141231,,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2015,20151231,,POWER ASSETS HOLDINGS LTD,HKG,4911
;
run;

Note that I used valid SAS names for the columns.

Edit: Added the DSD option.

[Jahanzaib]

Do I need to write this way for each and every company?

I have updated the data. the year 2000 or 20001231 means same. now i have deleted the second value.

[Kurt_Bremser]

---

@Jahanzaib wrote:

Do I need to write this way for each and every company?

I have updated the data. the year 2000 or 20001231 means same. now i have deleted the second value.

---

No. Just take a significant subset that is sufficient to illustrate your case. For instance, 2 companies are enough to make it clear that the company_key will undergo a group change, and the missing values for the last 2 years also indicate that those will always be missing.

And the format of a data step with cards is a basic courtesy to those that will help you. Instead of having to import the data ourselves, we can just copy the text into SAS and run it.

And never use .xls to post data. Nobody in his/her right mind will open a MS Office file from the web.

[Jahanzaib]

I understand code to some extent but I dont get the idea how these lines could be made?  moreover why use $30 with company_name and $3 with iso_code. 

16538,2000,20001231,1783,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2001,20011231,2010,CATHAY PACIFIC AIRWAYS LTD,HKG,4512

[Kurt_Bremser]

---

@Jahanzaib wrote:

I understand code to some extent but I dont get the idea how these lines could be made?  moreover why use $30 with company_name and $3 with iso_code. 

16538,2000,20001231,1783,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2001,20011231,2010,CATHAY PACIFIC AIRWAYS LTD,HKG,4512

---

I use the formats to override the default length for character variables (8); one to enlarge the size sufficiently, the other to prevent waste of space.

If you find those are too short for other values appearing in your input data, adjust them accordingly.

[Kurt_Bremser · Accepted solution]

Try this:

data have;
infile cards dlm=',' dsd;
input company_key data_year data_date:yymmdd8. ltdebt1 company_name:$30. iso_code:$3. sic;
format data_date yymmddn8.;
cards;
16538,2000,20001231,1783,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2001,20011231,2010,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2002,20021231,4281,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2003,20031231,4879,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2004,20041231,4969,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2005,20051231,3563,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2006,20061231,6151,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2007,20071231,3878,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2008,20081231,4255,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2009,20091231,7828,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2010,20101231,8704,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2011,20111231,8562,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2012,20121231,8157,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2013,20131231,10218,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2014,20141231,9781,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16538,2015,20151231,13238,CATHAY PACIFIC AIRWAYS LTD,HKG,4512
16590,2000,20001231,3976,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2001,20011231,731,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2002,20021231,3270,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2003,20031231,2436,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2004,20041231,1400,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2005,20051231,376,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2006,20061231,1089,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2007,20071231,2191,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2008,20081231,1687,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2009,20091231,,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2010,20101231,8459,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2011,20111231,617,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2012,20121231,5311,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2013,20131231,500,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2014,20141231,,POWER ASSETS HOLDINGS LTD,HKG,4911
16590,2015,20151231,,POWER ASSETS HOLDINGS LTD,HKG,4911
;
run;

data yearsums (keep=company_key data_year ltdebtsum_next_2_years);
set have;
by company_key;
oldltdebt = lag(ltdebt1);
if not first.company_key
then do;
  ltdebtsum_next_2_years = ltdebt1 + oldltdebt;
  data_year = data_year - 2;
  output;
end;
run;

data want (keep=company_key company_name data_year ltdebtsum_next_2_years);
merge
  have (in=a)
  yearsums
;
by company_key data_year;
if a;
run;

proc print noobs;
run;

Result:

                                                   ltdebtsum_
company_    data_                                    next_2_
   key       year           company_name              years

  16538      2000    CATHAY PACIFIC AIRWAYS LTD        6291  
  16538      2001    CATHAY PACIFIC AIRWAYS LTD        9160  
  16538      2002    CATHAY PACIFIC AIRWAYS LTD        9848  
  16538      2003    CATHAY PACIFIC AIRWAYS LTD        8532  
  16538      2004    CATHAY PACIFIC AIRWAYS LTD        9714  
  16538      2005    CATHAY PACIFIC AIRWAYS LTD       10029  
  16538      2006    CATHAY PACIFIC AIRWAYS LTD        8133  
  16538      2007    CATHAY PACIFIC AIRWAYS LTD       12083  
  16538      2008    CATHAY PACIFIC AIRWAYS LTD       16532  
  16538      2009    CATHAY PACIFIC AIRWAYS LTD       17266  
  16538      2010    CATHAY PACIFIC AIRWAYS LTD       16719  
  16538      2011    CATHAY PACIFIC AIRWAYS LTD       18375  
  16538      2012    CATHAY PACIFIC AIRWAYS LTD       19999  
  16538      2013    CATHAY PACIFIC AIRWAYS LTD       23019  
  16538      2014    CATHAY PACIFIC AIRWAYS LTD           .  
  16538      2015    CATHAY PACIFIC AIRWAYS LTD           .  
  16590      2000    POWER ASSETS HOLDINGS LTD         4001  
  16590      2001    POWER ASSETS HOLDINGS LTD         5706  
  16590      2002    POWER ASSETS HOLDINGS LTD         3836  
  16590      2003    POWER ASSETS HOLDINGS LTD         1776  
  16590      2004    POWER ASSETS HOLDINGS LTD         1465  
  16590      2005    POWER ASSETS HOLDINGS LTD         3280  
  16590      2006    POWER ASSETS HOLDINGS LTD         3878  
  16590      2007    POWER ASSETS HOLDINGS LTD            .  
  16590      2008    POWER ASSETS HOLDINGS LTD            .  
  16590      2009    POWER ASSETS HOLDINGS LTD         9076  
  16590      2010    POWER ASSETS HOLDINGS LTD         5928  
  16590      2011    POWER ASSETS HOLDINGS LTD         5811  
  16590      2012    POWER ASSETS HOLDINGS LTD            .  
  16590      2013    POWER ASSETS HOLDINGS LTD            .  
  16590      2014    POWER ASSETS HOLDINGS LTD            .  
  16590      2015    POWER ASSETS HOLDINGS LTD            .