topic calculating difference. in SAS Procedures

calculating difference.

Jahanzaib — Thu, 24 Nov 2016 09:56:58 GMT

I want to calculate the difference of values. like in this data set there is variable name tdebt. I have many firms and years from 2001-2015. So i want to calculate (tdebt of 2001-tdebt of 2002) and place this value in a new column(edebt) infront of 2001 and so on. But these values do not mix with different companies.

data work.new;

infile datalines dsd truncover;
input Global_Company_Key:32. SICC:32. CoName:$255. company:$255. fam:32. Country:$255. 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. 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. 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:$255. RD_TA:32. FA_TA:32. lnTA:32. EBITDA:$255.;
datalines;
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2015,26321,1.376,831105,9.49,1499360,2225723,2063119.36,2383982.4,0.2500901691,90.18,2260330,1.0752692705,1315517,795043,1.59,1.77,3755.11,-1067326,1246706,0,245150,218829,795043,0.1865042064,0,0,4262869,795043,38.55,18.65,27.58,#REF!,0,0.0513337379,15.265452966,#REF!
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2009,24128,0.919,633776,-24.97,677460,677460,622585.74,1077161.4,0,99.5,801447,1.4887169365,233660,0,1.59,2.23,704.56,-633776,225376,0,34687,10559,0,0,0,0,930141,0,0,0,0,#REF!,0,0.0113520423,13.743091467,#REF!
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2014,12903,1.365,1092244,33.87,1496060,2219647,2042121.9,2378735.4,0.2496908534,41.86,2203858,1.1054257309,291595,791605,1.59,2.51,1461.98,-900862,108516,0,241399,228496,791605,0.2479268827,0,0,3192897,791605,38.77,24.79,27.87,#REF!,0,0.0715638494,14.976439213,#REF!
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2011,29417,1.062,718208,-13.43,677460,677460,719462.52,1077161.4,0,82.72,936676,1.3434567586,242887,0,1.59,1.27,948.44,-718208,185184,0,39135,9718,0,0,0,0,1041467,0,0,0,0,#REF!,0,0.0093310686,13.856140854,#REF!
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2013,30689,1.04,221743,-10.45,854960,854960,889158.4,1359386.4,0.1830111742,79.17,1293125,1.3283999452,223410,304512,1.59,1.71,1077.16,-872678,193067,200010,40436,9747,304512,0.2126664599,0,0,1431876,304512,34.24,21.27,25.34,#REF!,0,0.0068071537,14.174496031,#REF!
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2010,25363,0.914,619709,-6.24,677460,677460,619198.44,1077161.4,0,80.93,788938,1.4957822192,230889,0,1.59,1.4,1510.74,-619709,211639,0,34786,9423,0,0,0,0,923718,0,0,0,0,#REF!,0,0.0102011653,13.736162109,#REF!
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2000,43076,0.283,6472,-0.58,85759,85759,24269.797,136356.81,0.5658115189,51.59,53802,1.429910069,137413,103983,1.59,1.47,90.05,171221,167561,0,162472,119396,177693,0.6815420256,0,73710,260722,177693,733.33,68.15,90.19,#REF!,0,0.4579437102,12.471209985,#REF!
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2007,19579,1.72,728263,-1.29,681481,681481,1172147.32,1083554.79,0,88.61,979986,0.942421572,317969,0,1.59,3.74,840.61,-728263,358412,248644,27728,8149,0,0,6977,0,1538641,0,0,0,0,#REF!,0.004534521,0.0052962322,14.246410117,#REF!
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2008,22828,1.133,617471,-16.67,677460,677460,767562.18,1077161.4,0,98.48,843015,1.2903341007,186028,0,1.59,1.04,2548.74,-621448,293223,5700,34118,11290,0,0,0,0,1066355,0,0,0,0,#REF!,0,0.0105874685,13.879756849,#REF!
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2005,96607,0.883,150445,-0.22,493981,493981,436185.223,785429.79,0.1251001421,52.86,470654,1.4088078743,334453,4020,1.59,0.68,474.22,-38138,531298,0,169235,72628,112307,0.1314608451,6007,108287,854300,112307,25.76,13.15,17.86,#REF!,0.0070314878,0.0850146319,13.658037699,#REF!
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2012,31481,1.033,310152,-14.23,677460,677460,699816.18,1077161.4,0,88.42,825907,1.3908174802,195867,0,1.59,1.59,860.37,-652829,208387,0,44013,12532,0,0,0,0,965528,0,0,0,0,#REF!,0,0.0129794268,13.780430381,#REF!
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2006,94320,0.955,302383,7.63,591981,591981,565341.855,941249.79,0.0972660664,55.49,670777,1.3667984944,353856,6423,1.59,1.42,335.91,-200967,584832,103961,163178,68858,101416,0.0989583689,7921,94993,1024835,101416,17.94,9.9,13.23,#REF!,0.0077290491,0.0671893524,13.840042182,#REF!
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2002,57750,0.889,114117,11.78,446259,446259,396724.251,709551.81,0.1779067572,47.56,422283,1.4453295889,209645,60264,1.59,0.9,423.94,29061,387377,0,190121,132371,153552,0.2185908724,0,93288,702463,153552,38.69,21.86,26.38,#REF!,0,0.1884383946,13.46234801,#REF!
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2001,48176,0.858,123499,-0.06,446259,446259,382890.222,709551.81,0.1185361225,53.43,246185,1.5606365062,170191,10014,1.59,0.95,126.07,-33166,231425,211840,174189,126013,95418,0.1637621812,0,85404,582662,95418,24.91,16.38,19.4,#REF!,0,0.2162711829,13.275362537,#REF!
200687,7370,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2004,90301,0.972,102506,10.96,493981,493981,480149.532,785429.79,0.2362535744,50.08,505572,1.3218649956,393071,4324,1.59,0.96,523.62,116264,531848,0,173586,83285,242961,0.2561601648,6421,238637,948473,242961,50.62,25.62,30.6,#REF!,0.006769829,0.0878095634,13.762608602,#REF!
202779,4922,BEIJING DEVELOPMENT (HK) LTD,BEIJING DEVELOPMENT,0,HKG,2003,73622,0.936,90281,15.18,493981,493981,462366.216,785429.79,0.1972490849,50.01,445760,1.3822445931,271289,43233,1.59,1.06,401.63,82201,481345,0,164880,91258,192993,0.2283467921,3749,149760,845175,192993,41.76,22.83,26.74,#REF!,0.0044357677,0.1079752714,13.647298985,#REF!
202779,4922,BEIJING ENTERPRISES HOLDINGS,BEIJING ENTERPRISES,0,HKG,2015,19019058,47.213,9785230,34.31,10005799,31274178,472403788.19,501190471.91,0.1160562781,141.82,51791052,1.1528315082,44080330,52077258,50.09,52.38,78088.5,45226171,73652902,0,58134480,39115422,65803172,0.3493559077,84612,13725914,188355687,65803172,251.57,76.26,94.09,#REF!,0.0004492139,0.2076678577,19.053842685,#REF!
202779,4922,BEIJING ENTERPRISES HOLDINGS,BEIJING ENTERPRISES,0,HKG,2009,7536614,28.147,6758884,40.37,5346059,461956,150475522.67,267784095.31,0.0504517267,137.89,18072857,2.7791962072,13136114,9893427,50.09,58.393,35929.21,3732846,25938443,0,26814126,19277512,14227997,0.2157932513,8268,4334570,65933466,14227997,157.38,62.17,73.58,#REF!,0.0001253991,0.292378259,18.004156701,#REF!
202779,4922,BEIJING ENTERPRISES HOLDINGS,BEIJING ENTERPRISES,0,HKG,2014,17460674,46.331,10620831,35.63,9991775,31272626,462928927.53,500488009.75,0.1081723049,135.09,48324082,1.2146885592,46536490,38984960,50.09,66.09,97677.75,43044157,56861737,0,58024199,40563525,60705607,0.3469946157,89390,21720647,174946827,60705607,213.91,75.72,90.52,#REF!,0.0005109553,0.2318620217,18.97999264,#REF!
202779,4922,BEIJING ENTERPRISES HOLDINGS,BEIJING ENTERPRISES,0,HKG,2011,10994668,34.231,7951348,25.12,8046741,804674,275447991.17,403061256.69,0.0670037892,136.05,34890404,2.2575017606,22957443,22171572,50.09,48.72,55389.92,14289927,33126213,3385362,37545128,26550460,28946132,0.2852353304,19729,6774560,101481580,28946132,162.06,61.74,78.25,#REF!,0.0001944097,0.2616283664,18.435387862,#REF!
;;;;

Re: calculating difference.

Kurt_Bremser — Thu, 24 Nov 2016 10:11:43 GMT

Use proper sorting and the lag() function:

proc sort data=new;
by global_company_key descending year;
run;

data want;
set new;
by global_company_key;
edebt = tdebt - lag(tdebt);
if first.global_company_key or lag(year) - year > 1 then edebt = .;
run;

proc sort data=want;
by global_company_key year;
run;

By also comparing the years I have taken care of missing years.

Re: calculating difference.

Jahanzaib — Fri, 25 Nov 2016 06:39:37 GMT

Thanks for your reply. Now i want to compare this value of edebt with that of previously calculated bdebt by creating another column. like if edebt is 5% or more of bedebt then debtissu=1 otherwise 0. here debtissu is another variable/column.

Re: calculating difference.

Kurt_Bremser — Fri, 25 Nov 2016 08:31:49 GMT

I do not see a column bdebt in your example data.

Re: calculating difference.

Jahanzaib — Fri, 25 Nov 2016 08:47:05 GMT

oh sorry. its Tdebt

Re: calculating difference.

Kurt_Bremser — Fri, 25 Nov 2016 09:53:54 GMT

Just add

debtissu = (edebt > (tdebt * .05));

This makes use of the fact that a false condition is represented internally as 0, while true is 1.

Re: calculating difference.

Jahanzaib — Sun, 27 Nov 2016 09:44:56 GMT

proc sort data=new;
by global_company_key descending year;
run;

data want;
set new;
by global_company_key;
edebt = tdebt - lag(tdebt);
if first.global_company_key or lag(year) - year > 1 then edebt = .;
run;

proc sort data=want;
by global_company_key year;
run;

if first.global_company_key or lag(year) - year > 1 then edebt = .;
run;
from the above statement i think there is some problem in this statement because it is giving error.

Moreover why lag(year) - Year > 1 then edebt=.; i think this statement make values in edebt greater then 1 to '.' but this is not required.

I think my purpose is solved without this statement.

now i want to calculate following on the basis of debtissu

country Year

HK 2000 Fam 10

HK 2000 Non-fam 20

HK 2001

actually purpose of this table is to count the number of fam firms and non-fam firms which issue debt in that specific year. in the colums of fam 1 shows that firm is family firm 0 is non-family. while 1 in debtissu coulmn shows that firm issue debt in that particular year.

Re: calculating difference.

mkeintz — Mon, 28 Nov 2016 01:53:52 GMT

You want a lead value for TDEBT. The usual approach is to sort data in descending order, get lags and resort to original order - a big problem with large data sets, and just plain annoying.

SAS has no lead function, but if the data set is in ascending order, you can simulate leads using the FIRSTOBS data set parameter with a self-merge of the data set. This program is a good example of a simple way to get lead values, in a by-group setting:

Notes:

The SET statement keeps only the global_company_key variable, simple so that a by global_company_key can be used to generate the usual first. and last. dummies.
The MERGE statement has FirSTOBS=2 in the second use of the NEW dataset. It keeps only those vars (YEAR and TDEBT) neccessary to calculate EDEBT, but those vars have to be renamed to distinguish them from current values of year and tdebt.

Mark

proc sort data=new;
  by global_company_key year;
run;

data want (drop=next_:);
 set new (keep=global_company_key);
 by global_company_key;
 merge new
       new (firstobs=2 keep=tdebt year 
            rename=(tdebt=next_tdebt year=next_year));
 if next_year=year+1 and not(last.global_company_key) then edebt=next_tdebt-tdebt;
 if edebt^=. then debtissu=(edebt>0.5*tdebt);
run;

Re: calculating difference.

Kurt_Bremser — Mon, 28 Nov 2016 07:25:30 GMT

"It is giving error" is very vague and on a level with "a bicycle was stolen in Beijing". Please be more specific (ie post the log).

The statement is there to prevent calculating a difference if there is a gap in your records (not all years are present).

Re: calculating difference.

Jahanzaib — Mon, 28 Nov 2016 07:41:33 GMT

Here is the log in jpg format.

Re: calculating difference.

Kurt_Bremser — Mon, 28 Nov 2016 07:49:47 GMT

You have an additional run statement that ends the data step before the if statement. Therefore the if appears outside a data step, causing the ERROR.

Re: calculating difference.

Jahanzaib — Mon, 28 Nov 2016 08:06:03 GMT

ok got it.
now i want to calculate following on the basis of debtissu
country Year
HK 2000 Fam 10
HK 2000 Non-fam 20
HK 2001

actually purpose of this table is to count the number of fam firms and non-fam firms which issue debt in that specific year. in the colums of fam 1 shows that firm is family firm 0 is non-family. while 1 in debtissu coulmn shows that firm issue debt in that particular year.

Re: calculating difference.

Kurt_Bremser — Mon, 28 Nov 2016 08:16:19 GMT

You should be able to do that with proc freq and

class country year family_code;

Use a where=dataset option to only use records that you want counted.