BookmarkSubscribeRSS Feed
Jahanzaib
Quartz | Level 8

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

12 REPLIES 12
Kurt_Bremser
Super User

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.

Jahanzaib
Quartz | Level 8

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. 

Jahanzaib
Quartz | Level 8

oh sorry. its Tdebt

Jahanzaib
Quartz | Level 8
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.  

 

Kurt_Bremser
Super User

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

Jahanzaib
Quartz | Level 8

Here is the log in jpg format.


log.jpg
Kurt_Bremser
Super User

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.

Jahanzaib
Quartz | Level 8
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.
Kurt_Bremser
Super User

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.

mkeintz
PROC Star

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:

  1. 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.
  2. 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;

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

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
  • 12 replies
  • 1341 views
  • 3 likes
  • 3 in conversation