topic Re: STD problem in SAS Procedures

STD problem

Khang — Wed, 30 Nov 2016 07:32:23 GMT

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?

Re: STD problem

Reeza — Wed, 30 Nov 2016 08:52:43 GMT

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

Re: STD problem

RW9 — Wed, 30 Nov 2016 09:19:27 GMT

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-data-AKA-generate/ta-p/258712

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

Re: STD problem

Khang — Thu, 01 Dec 2016 05:08:00 GMT

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

Re: STD problem

Reeza — Thu, 01 Dec 2016 05:14:02 GMT

I'm sorry but your question is unclear.

Re: STD problem

Khang — Thu, 01 Dec 2016 07:35:40 GMT

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.

Re: STD problem

Khang — Thu, 01 Dec 2016 07:29:31 GMT

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

Re: STD problem

Reeza — Thu, 01 Dec 2016 07:26:45 GMT

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

Re: STD problem

RW9 — Thu, 01 Dec 2016 09:35:23 GMT

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;

Re: STD problem

Khang — Thu, 01 Dec 2016 10:45:32 GMT

May be these figures could help you in understanding.

There is column of year in the data set.

Re: STD problem

RW9 — Thu, 01 Dec 2016 10:58:48 GMT

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.