STD problem

[Khang]

hello guys.

I am trying to calculate standard deviation of EBIT/TA over the recent 2 years time. i want to ad this in the same table by creating another column std(EBIT/TA). the data contain years from 2006-2010. Can anyone help me here?

[Reeza]

What have you tried so far? What specifically are you having issues with? 

[RW9]

Hi,

To get an acruate post follow these tips:

- Post test data in the form of a datastep following this post if needed (Excel files will not be downloaded):

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

- Show an example of what the output should look like.

- Post any code you have tried already.

For your question, standard deviation can be derived using proc means - use a where clause to restrict to the last two years (or possibly intck if its an interval), then merge the results back to your original data.

[Khang]

I suppose my requirement is different from already discussed ways of calculating std. I am posting my data according to requirement. for more explanation i am attaching my required table. Here STD(EBIT_TA) is the standard deviation of EBIT_TA over recent two years time.

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

[Reeza]

I'm sorry but your question is unclear. 

[Khang]

Can we do it in a way that. we calculate standard deviation of each company for values EBIT_TA for their available years from 2007-2010. Then put that value of standard deviation of each company in a new column Std(EBIT_TA). This will provide a one value for each company.

[Khang]

@Reeza or @RW9 can you guys please help me?

[Reeza]

Unfortunately not. 

But it's late and no computer to run SAS so can't run your code. 

It may be worth simplifying your problem with just a few lines of data and sample expected output. 

Otherwise someone else should be able to help you. 

http://stackoverflow.com/help/how-to-ask

[RW9]

Sorry, your question is becoming more and more confused.  You already have std in the test data you provided.  There is no date information, so how do you know year?  Post your question in the simplest form.  Create a small piece of test data which illustrates the problem, and what you want out.  We are not familiar in any sense with your data/code/ or processes.

At a guess:

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 means data=new;
  by company;
  var ebit_ta;
  output out=tmp std=std;
run;

proc sql;
  create table WANT as
  select  A.*,
          B.STD as STD2
  from    NEW A
  left join TMP B
  on      A.COMPANY=B.COMPANY;
quit;

[Khang]

May be these figures could help you in understanding.

There is column of year in the data set.

[RW9]

Ah, ok, I missed year in that mass of data.  So what is the standard deviation based on?  Is it every year apart from the first merged onto the first, which is what is seems from those screenshots.  If so:

data temp;
  set have;
  by company year;
  if first.company then delete;
run;

proc means data=temp;
  by company;
  var ...;
...;
run;

Then join temp back onto the data again.  So you drop the first year from the data, do the means, then merge back.