Help using Base SAS procedures

calculating difference.

Reply
Contributor
Posts: 72

calculating difference.

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

Super User
Posts: 7,863

Re: calculating difference.

Posted in reply to Jahanzaib

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 72

Re: calculating difference.

Posted in reply to KurtBremser

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. 

Super User
Posts: 7,863

Re: calculating difference.

Posted in reply to Jahanzaib

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 72

Re: calculating difference.

Posted in reply to KurtBremser

oh sorry. its Tdebt

Super User
Posts: 7,863

Re: calculating difference.

Posted in reply to Jahanzaib

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 72

Re: calculating difference.

Posted in reply to KurtBremser
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.  

 

Super User
Posts: 7,863

Re: calculating difference.

[ Edited ]
Posted in reply to Jahanzaib

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 72

Re: calculating difference.

Posted in reply to KurtBremser

Here is the log in jpg format.


log.jpg
Super User
Posts: 7,863

Re: calculating difference.

Posted in reply to Jahanzaib

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 72

Re: calculating difference.

Posted in reply to KurtBremser
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.
Super User
Posts: 7,863

Re: calculating difference.

Posted in reply to Jahanzaib

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,022

Re: calculating difference.

Posted in reply to Jahanzaib

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;

 

Ask a Question
Discussion stats
  • 12 replies
  • 340 views
  • 3 likes
  • 3 in conversation