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!
;;;;
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.
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.
I do not see a column bdebt in your example data.
oh sorry. its Tdebt
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.
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.
"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).
Here is the log in jpg format.
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.
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.
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:
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.