Solved: Re: Accumulating no of - over years

Jahanzaib · Posted 12-08-2016 04:03 AM

I have one variable name std. I have values for number of companies from year 2006-2012.

I want to create a new variable that accumulate value of year (2006, 2007 and 2008) and put that in front of year 2006 for that company. same way for 2007(accumulate 2007+2008+2009) so on (We cannot calculate 2011 and 20112 that I dont need). How can I do that?

data work.new;
infile datalines dsd truncover;
input Global_Company_Key:32. SICC:32. CoName:$29. company:$21. fam:32. Country:$3. year:32. ACCUMULATED_DEPRECIATION:32. BVPS:32. CASH:32. Sales:32. csout:32. cs:32. Bequity:32. Mequity:32. mdr:32. CGS:32. CAT:32. MTB:32. CLT:32. DEPRECIATION:32. DEPRECIATION_AND_DEPLETION:32. DEPRECIATION_DEPLETION_AMORT:32. EBIT:32. EPS:32. EBIT___DEPRECIATION:32. FIXED_ASSETS___COMMON_EQUITY:32. LTD:32. MP:32. MARKET_PRICE_YEAR_END:32. MV:32. ND:32. NT:32. OTHER_PROCEEDS_FROM_SALE_ISSUA:32. PROPERTY__PLANT___EQUIP_GROSS:32. assmat:32. FA:32. Tdebt:32. bdr:32. RD:32. STD:32. TA:32. TOTAL_DEBT:32. TOTAL_DEBT___COMMON_EQUITY:32. TOTAL_DEBT___TOTAL_ASSETS:32. TOTAL_DEBT___TOTAL_CAPITAL_STD:32. EBIT_TA:32. RD_TA:32. FA_TA:32. lnTA:32. EBITDA:32. FFI12:32. avglev:32.;
datalines;
282713,2070,CHINA AGRI INDUS HOLDINGS,CHINA AGRI PRODUCTS,0,HKG,2009,10944,654.164,155701,-12.86,443,29187,289794.652,188.275,0.9997905897,24.83,191185,0.8443250154,692783,5779,5779,19400,-324119,186.29,-304719,,557077,0.425,849.578,156.96,726182,104117,192400,40479,5.357634E-20,29535,898884,0.4831860205,,341807,1860327,898884,310.06,48.32,65.26,-0.174226897,0,0.0158762411,14.436262837,-343519,1,0.1511500044
282713,2070,CHINA AGRI INDUS HOLDINGS,CHINA AGRI PRODUCTS,0,HKG,2010,10264,117.245,81539,-35.52,750,49387,87933.75,318.75,0.9996272853,29.51,134877,0.9482144736,636825,5672,5672,11345,-306356,0,-295011,,689439,0.425,286.493,376.51,761378,129952,98221,37598,7.801026E-20,27334,854893,0.5052911491,,165454,1691882,854893,972.39,50.53,75.64,-0.181074094,0,0.0161559731,14.341352077,-317701,1,0.1511500044
282713,2070,CHINA AGRI INDUS HOLDINGS,CHINA AGRI PRODUCTS,0,HKG,2007,2682,7225.897,260894,33.37,89,91363,643104.833,37.825,0.999954622,52.06,293259,0.6497141584,391331,1334,1334,1334,1642,0,2976,,761887,0.425,9812.961,285.73,572621,45929,599687,644546,1.668188E-20,641864,833515,0.4540250077,,71628,1835835,833515,129.72,45.4,55.18,0.0008944159,0,0.3496305496,14.423009977,308,1,0.1511500044
282713,2070,CHINA AGRI INDUS HOLDINGS,CHINA AGRI PRODUCTS,0,HKG,2008,5418,3380.123,239185,-3.23,116,119443,392094.268,49.3,0.9999106091,18.3,246347,0.7313345399,556873,2554,2554,48921,-843327,0,-794406,,312006,0.425,1350.105,872.63,312276,168050,0,20816,8.821179E-20,15398,551461,0.3779120646,,239455,1459231,551461,140.33,37.79,54.33,-0.577925633,0,0.0105521333,14.193420143,-892248,1,0.1511500044
282713,2070,CHINA AGRI INDUS HOLDINGS,CHINA AGRI PRODUCTS,0,HKG,2006,1240,1131.578,2140,-45.72,42,43276,47526.276,17.85,0.9973413168,66.86,38076,0.3677093043,13626,1061,1061,1061,-8057,0,-6996,,0,0.425,6783.456,224.7,4556,31740,0,3612,4.492581E-17,2372,6696,0.0891172126,,6696,75137,6696,14.05,8.91,9.82,-0.107230792,0,0.0315690006,11.227068393,-9118,1,0.1511500044
250921,3942,DREAM INTERNATIONAL LTD,DREAM INTERNATIONAL,0,HKG,2009,122814,0.695,198270,8.61,668529,52019,464627.655,1096387.56,0.0481710439,72.37,629675,1.7818752645,333268,21767,21767,22861,89111,0,111972,,0,1.64,0.55,66.85,-272434,994052,0,254694,1.825949E-20,131880,55487,0.0686715198,,55487,808006,55487,11.94,6.87,10.44,0.1102850721,0,0.1632166098,13.602324763,66250,1,0.1511500044
250921,3942,DREAM INTERNATIONAL LTD,DREAM INTERNATIONAL,0,HKG,2010,139231,0.958,229985,14.79,672165,52303,643934.07,1102350.6,0.0261108347,68.31,721190,1.4868241815,289661,22838,22838,24024,214986,0.07,239010,,6101,1.64,1.6,367.69,-267015,1544857,0,303069,1.256576E-20,163838,29555,0.0313864962,,23454,941647,29555,4.59,3.14,4.29,0.228308485,0,0.1739908904,13.755385749,190962,1,0.1511500044
250921,3942,DREAM INTERNATIONAL LTD,DREAM INTERNATIONAL,0,HKG,2007,143162,0.646,79316,1.28,668529,52019,431869.734,1096387.56,0.0946813528,81.06,432046,1.9174646706,265293,30105,30105,31221,-12067,0,19154,,780,1.64,0.295,160.45,7442,946328,0,325184,3.18717E-20,182022,114664,0.1583105296,,113884,724298,114664,26.54,15.83,20.3,-0.01666027,0,0.2513081632,13.492958189,-43288,1,0.1511500044
250921,3942,DREAM INTERNATIONAL LTD,DREAM INTERNATIONAL,0,HKG,2008,136624,0.56,82563,-1.68,668529,52019,374376.24,1096387.56,0.117296834,79.18,529352,1.9807644409,350890,30550,30550,31652,-35495,0,-3843,,0,1.64,0.1,197.22,-50632,1048589,0,308557,2.421156E-20,171933,145692,0.1979048375,,145692,736172,145692,38.94,19.79,27.78,-0.048215634,0,0.2335500399,13.509219066,-67147,1,0.1511500044
250921,3942,DREAM INTERNATIONAL LTD,DREAM INTERNATIONAL,0,HKG,2006,137013,0.631,80545,0.27,668529,52019,421841.799,1096387.56,0.1128556826,82.75,452715,1.8885434553,299355,26070,26070,27043,-114890,0,-87847,,8684,1.64,0.24,387.75,56676,1084357,0,336891,2.743477E-20,199878,139474,0.1837217236,,130790,759159,139474,33.08,18.37,23.98,-0.151338521,0,0.2632887182,13.539966521,-141933,1,0.1511500044
;;;;

Ksharp · Posted 12-08-2016 06:29 AM

You want rolling sum of std ? Better post the output too.



data work.new;
infile datalines dsd truncover;
input Global_Company_Key:32. SICC:32. CoName:$29. company:$21. fam:32. Country:$3. year:32. ACCUMULATED_DEPRECIATION:32. BVPS:32. CASH:32. Sales:32. csout:32. cs:32. Bequity:32. Mequity:32. mdr:32. CGS:32. CAT:32. MTB:32. CLT:32. DEPRECIATION:32. DEPRECIATION_AND_DEPLETION:32. DEPRECIATION_DEPLETION_AMORT:32. EBIT:32. EPS:32. EBIT___DEPRECIATION:32. FIXED_ASSETS___COMMON_EQUITY:32. LTD:32. MP:32. MARKET_PRICE_YEAR_END:32. MV:32. ND:32. NT:32. OTHER_PROCEEDS_FROM_SALE_ISSUA:32. PROPERTY__PLANT___EQUIP_GROSS:32. assmat:32. FA:32. Tdebt:32. bdr:32. RD:32. STD:32. TA:32. TOTAL_DEBT:32. TOTAL_DEBT___COMMON_EQUITY:32. TOTAL_DEBT___TOTAL_ASSETS:32. TOTAL_DEBT___TOTAL_CAPITAL_STD:32. EBIT_TA:32. RD_TA:32. FA_TA:32. lnTA:32. EBITDA:32. FFI12:32. avglev:32.;
datalines;
282713,2070,CHINA AGRI INDUS HOLDINGS,CHINA AGRI PRODUCTS,0,HKG,2009,10944,654.164,155701,-12.86,443,29187,289794.652,188.275,0.9997905897,24.83,191185,0.8443250154,692783,5779,5779,19400,-324119,186.29,-304719,,557077,0.425,849.578,156.96,726182,104117,192400,40479,5.357634E-20,29535,898884,0.4831860205,,341807,1860327,898884,310.06,48.32,65.26,-0.174226897,0,0.0158762411,14.436262837,-343519,1,0.1511500044
282713,2070,CHINA AGRI INDUS HOLDINGS,CHINA AGRI PRODUCTS,0,HKG,2010,10264,117.245,81539,-35.52,750,49387,87933.75,318.75,0.9996272853,29.51,134877,0.9482144736,636825,5672,5672,11345,-306356,0,-295011,,689439,0.425,286.493,376.51,761378,129952,98221,37598,7.801026E-20,27334,854893,0.5052911491,,165454,1691882,854893,972.39,50.53,75.64,-0.181074094,0,0.0161559731,14.341352077,-317701,1,0.1511500044
282713,2070,CHINA AGRI INDUS HOLDINGS,CHINA AGRI PRODUCTS,0,HKG,2007,2682,7225.897,260894,33.37,89,91363,643104.833,37.825,0.999954622,52.06,293259,0.6497141584,391331,1334,1334,1334,1642,0,2976,,761887,0.425,9812.961,285.73,572621,45929,599687,644546,1.668188E-20,641864,833515,0.4540250077,,71628,1835835,833515,129.72,45.4,55.18,0.0008944159,0,0.3496305496,14.423009977,308,1,0.1511500044
282713,2070,CHINA AGRI INDUS HOLDINGS,CHINA AGRI PRODUCTS,0,HKG,2008,5418,3380.123,239185,-3.23,116,119443,392094.268,49.3,0.9999106091,18.3,246347,0.7313345399,556873,2554,2554,48921,-843327,0,-794406,,312006,0.425,1350.105,872.63,312276,168050,0,20816,8.821179E-20,15398,551461,0.3779120646,,239455,1459231,551461,140.33,37.79,54.33,-0.577925633,0,0.0105521333,14.193420143,-892248,1,0.1511500044
282713,2070,CHINA AGRI INDUS HOLDINGS,CHINA AGRI PRODUCTS,0,HKG,2006,1240,1131.578,2140,-45.72,42,43276,47526.276,17.85,0.9973413168,66.86,38076,0.3677093043,13626,1061,1061,1061,-8057,0,-6996,,0,0.425,6783.456,224.7,4556,31740,0,3612,4.492581E-17,2372,6696,0.0891172126,,6696,75137,6696,14.05,8.91,9.82,-0.107230792,0,0.0315690006,11.227068393,-9118,1,0.1511500044
250921,3942,DREAM INTERNATIONAL LTD,DREAM INTERNATIONAL,0,HKG,2009,122814,0.695,198270,8.61,668529,52019,464627.655,1096387.56,0.0481710439,72.37,629675,1.7818752645,333268,21767,21767,22861,89111,0,111972,,0,1.64,0.55,66.85,-272434,994052,0,254694,1.825949E-20,131880,55487,0.0686715198,,55487,808006,55487,11.94,6.87,10.44,0.1102850721,0,0.1632166098,13.602324763,66250,1,0.1511500044
250921,3942,DREAM INTERNATIONAL LTD,DREAM INTERNATIONAL,0,HKG,2010,139231,0.958,229985,14.79,672165,52303,643934.07,1102350.6,0.0261108347,68.31,721190,1.4868241815,289661,22838,22838,24024,214986,0.07,239010,,6101,1.64,1.6,367.69,-267015,1544857,0,303069,1.256576E-20,163838,29555,0.0313864962,,23454,941647,29555,4.59,3.14,4.29,0.228308485,0,0.1739908904,13.755385749,190962,1,0.1511500044
250921,3942,DREAM INTERNATIONAL LTD,DREAM INTERNATIONAL,0,HKG,2007,143162,0.646,79316,1.28,668529,52019,431869.734,1096387.56,0.0946813528,81.06,432046,1.9174646706,265293,30105,30105,31221,-12067,0,19154,,780,1.64,0.295,160.45,7442,946328,0,325184,3.18717E-20,182022,114664,0.1583105296,,113884,724298,114664,26.54,15.83,20.3,-0.01666027,0,0.2513081632,13.492958189,-43288,1,0.1511500044
250921,3942,DREAM INTERNATIONAL LTD,DREAM INTERNATIONAL,0,HKG,2008,136624,0.56,82563,-1.68,668529,52019,374376.24,1096387.56,0.117296834,79.18,529352,1.9807644409,350890,30550,30550,31652,-35495,0,-3843,,0,1.64,0.1,197.22,-50632,1048589,0,308557,2.421156E-20,171933,145692,0.1979048375,,145692,736172,145692,38.94,19.79,27.78,-0.048215634,0,0.2335500399,13.509219066,-67147,1,0.1511500044
250921,3942,DREAM INTERNATIONAL LTD,DREAM INTERNATIONAL,0,HKG,2006,137013,0.631,80545,0.27,668529,52019,421841.799,1096387.56,0.1128556826,82.75,452715,1.8885434553,299355,26070,26070,27043,-114890,0,-87847,,8684,1.64,0.24,387.75,56676,1084357,0,336891,2.743477E-20,199878,139474,0.1837217236,,130790,759159,139474,33.08,18.37,23.98,-0.151338521,0,0.2632887182,13.539966521,-141933,1,0.1511500044
;;;;

proc sql;
select *,(select sum(std) from new 
where Global_Company_Key=a.Global_Company_Key
 and year between a.year and a.year+2) as cum
from new as a;
quit;

View solution in original post

Reeza · Posted 12-08-2016 04:46 AM

Please provide a smaller sample dataset in the future, it's just a sample.

if you have SAS ETS PROC EXPAND is the easiest option, otherwise a SQL query is straightforward.

Heres rhe general idea:

Proc SQL;

create table want as

select a.company, a.year, a.value, sum(b.value) as sum3

from have as a

left join have as b

on a.company = b.company

and a.year between b.year and b.year+2

grouo by a.company, a.year, a.value;

quit;

Ksharp · Posted 12-08-2016 06:29 AM

You want rolling sum of std ? Better post the output too.



data work.new;
infile datalines dsd truncover;
input Global_Company_Key:32. SICC:32. CoName:$29. company:$21. fam:32. Country:$3. year:32. ACCUMULATED_DEPRECIATION:32. BVPS:32. CASH:32. Sales:32. csout:32. cs:32. Bequity:32. Mequity:32. mdr:32. CGS:32. CAT:32. MTB:32. CLT:32. DEPRECIATION:32. DEPRECIATION_AND_DEPLETION:32. DEPRECIATION_DEPLETION_AMORT:32. EBIT:32. EPS:32. EBIT___DEPRECIATION:32. FIXED_ASSETS___COMMON_EQUITY:32. LTD:32. MP:32. MARKET_PRICE_YEAR_END:32. MV:32. ND:32. NT:32. OTHER_PROCEEDS_FROM_SALE_ISSUA:32. PROPERTY__PLANT___EQUIP_GROSS:32. assmat:32. FA:32. Tdebt:32. bdr:32. RD:32. STD:32. TA:32. TOTAL_DEBT:32. TOTAL_DEBT___COMMON_EQUITY:32. TOTAL_DEBT___TOTAL_ASSETS:32. TOTAL_DEBT___TOTAL_CAPITAL_STD:32. EBIT_TA:32. RD_TA:32. FA_TA:32. lnTA:32. EBITDA:32. FFI12:32. avglev:32.;
datalines;
282713,2070,CHINA AGRI INDUS HOLDINGS,CHINA AGRI PRODUCTS,0,HKG,2009,10944,654.164,155701,-12.86,443,29187,289794.652,188.275,0.9997905897,24.83,191185,0.8443250154,692783,5779,5779,19400,-324119,186.29,-304719,,557077,0.425,849.578,156.96,726182,104117,192400,40479,5.357634E-20,29535,898884,0.4831860205,,341807,1860327,898884,310.06,48.32,65.26,-0.174226897,0,0.0158762411,14.436262837,-343519,1,0.1511500044
282713,2070,CHINA AGRI INDUS HOLDINGS,CHINA AGRI PRODUCTS,0,HKG,2010,10264,117.245,81539,-35.52,750,49387,87933.75,318.75,0.9996272853,29.51,134877,0.9482144736,636825,5672,5672,11345,-306356,0,-295011,,689439,0.425,286.493,376.51,761378,129952,98221,37598,7.801026E-20,27334,854893,0.5052911491,,165454,1691882,854893,972.39,50.53,75.64,-0.181074094,0,0.0161559731,14.341352077,-317701,1,0.1511500044
282713,2070,CHINA AGRI INDUS HOLDINGS,CHINA AGRI PRODUCTS,0,HKG,2007,2682,7225.897,260894,33.37,89,91363,643104.833,37.825,0.999954622,52.06,293259,0.6497141584,391331,1334,1334,1334,1642,0,2976,,761887,0.425,9812.961,285.73,572621,45929,599687,644546,1.668188E-20,641864,833515,0.4540250077,,71628,1835835,833515,129.72,45.4,55.18,0.0008944159,0,0.3496305496,14.423009977,308,1,0.1511500044
282713,2070,CHINA AGRI INDUS HOLDINGS,CHINA AGRI PRODUCTS,0,HKG,2008,5418,3380.123,239185,-3.23,116,119443,392094.268,49.3,0.9999106091,18.3,246347,0.7313345399,556873,2554,2554,48921,-843327,0,-794406,,312006,0.425,1350.105,872.63,312276,168050,0,20816,8.821179E-20,15398,551461,0.3779120646,,239455,1459231,551461,140.33,37.79,54.33,-0.577925633,0,0.0105521333,14.193420143,-892248,1,0.1511500044
282713,2070,CHINA AGRI INDUS HOLDINGS,CHINA AGRI PRODUCTS,0,HKG,2006,1240,1131.578,2140,-45.72,42,43276,47526.276,17.85,0.9973413168,66.86,38076,0.3677093043,13626,1061,1061,1061,-8057,0,-6996,,0,0.425,6783.456,224.7,4556,31740,0,3612,4.492581E-17,2372,6696,0.0891172126,,6696,75137,6696,14.05,8.91,9.82,-0.107230792,0,0.0315690006,11.227068393,-9118,1,0.1511500044
250921,3942,DREAM INTERNATIONAL LTD,DREAM INTERNATIONAL,0,HKG,2009,122814,0.695,198270,8.61,668529,52019,464627.655,1096387.56,0.0481710439,72.37,629675,1.7818752645,333268,21767,21767,22861,89111,0,111972,,0,1.64,0.55,66.85,-272434,994052,0,254694,1.825949E-20,131880,55487,0.0686715198,,55487,808006,55487,11.94,6.87,10.44,0.1102850721,0,0.1632166098,13.602324763,66250,1,0.1511500044
250921,3942,DREAM INTERNATIONAL LTD,DREAM INTERNATIONAL,0,HKG,2010,139231,0.958,229985,14.79,672165,52303,643934.07,1102350.6,0.0261108347,68.31,721190,1.4868241815,289661,22838,22838,24024,214986,0.07,239010,,6101,1.64,1.6,367.69,-267015,1544857,0,303069,1.256576E-20,163838,29555,0.0313864962,,23454,941647,29555,4.59,3.14,4.29,0.228308485,0,0.1739908904,13.755385749,190962,1,0.1511500044
250921,3942,DREAM INTERNATIONAL LTD,DREAM INTERNATIONAL,0,HKG,2007,143162,0.646,79316,1.28,668529,52019,431869.734,1096387.56,0.0946813528,81.06,432046,1.9174646706,265293,30105,30105,31221,-12067,0,19154,,780,1.64,0.295,160.45,7442,946328,0,325184,3.18717E-20,182022,114664,0.1583105296,,113884,724298,114664,26.54,15.83,20.3,-0.01666027,0,0.2513081632,13.492958189,-43288,1,0.1511500044
250921,3942,DREAM INTERNATIONAL LTD,DREAM INTERNATIONAL,0,HKG,2008,136624,0.56,82563,-1.68,668529,52019,374376.24,1096387.56,0.117296834,79.18,529352,1.9807644409,350890,30550,30550,31652,-35495,0,-3843,,0,1.64,0.1,197.22,-50632,1048589,0,308557,2.421156E-20,171933,145692,0.1979048375,,145692,736172,145692,38.94,19.79,27.78,-0.048215634,0,0.2335500399,13.509219066,-67147,1,0.1511500044
250921,3942,DREAM INTERNATIONAL LTD,DREAM INTERNATIONAL,0,HKG,2006,137013,0.631,80545,0.27,668529,52019,421841.799,1096387.56,0.1128556826,82.75,452715,1.8885434553,299355,26070,26070,27043,-114890,0,-87847,,8684,1.64,0.24,387.75,56676,1084357,0,336891,2.743477E-20,199878,139474,0.1837217236,,130790,759159,139474,33.08,18.37,23.98,-0.151338521,0,0.2632887182,13.539966521,-141933,1,0.1511500044
;;;;

proc sql;
select *,(select sum(std) from new 
where Global_Company_Key=a.Global_Company_Key
 and year between a.year and a.year+2) as cum
from new as a;
quit;

Jahanzaib · Posted 12-09-2016 03:17 AM

@Ksharp output would be like this.

Ksharp · Posted 12-09-2016 03:35 AM

Then did you run my code? Is that what you are looking for ?

Jahanzaib · Posted 12-09-2016 03:50 AM

@Ksharp yes thanks. but this comes in a query results. how can i obtain it in sas table?

Ksharp · Posted 12-09-2016 08:29 AM

Add one more code

proc sql;

create table want as 

select *,(select sum(std) from new 
where Global_Company_Key=a.Global_Company_Key
 and year between a.year and a.year+2) as cum
from new as a;
quit;

SAS Innovate 2025: Save the Date

SAS Training: Just a Click Away