BookmarkSubscribeRSS Feed
Khang
Obsidian | Level 7

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?

10 REPLIES 10
Reeza
Super User

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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.

 

Untitled.png

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
Super User

I'm sorry but your question is unclear. 

Khang
Obsidian | Level 7

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
Obsidian | Level 7

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

Reeza
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

May be these figures could help you in understanding.

There is column of year in the data set.

Screenshot 2016-12-01 19.35.15.pngScreenshot 2016-12-01 19.37.23.png

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1680 views
  • 3 likes
  • 3 in conversation