topic Re: calculating lag in SAS Programming

calculating lag

AbuChowdhury — Sat, 25 Oct 2014 06:42:32 GMT

Following is the sample of my dataset. I need to calculate lag of "asset" for each company_id. Then years 2003 and 2013 will have to be dropped. How can I do that?

year company_id asset

2003	AN8068571086	3109000
2004	AN8068571086	2997000
2005	AN8068571086	3496000
2006	AN8068571086	2998873
2007	AN8068571086	3169033
2008	AN8068571086	3692000
2009	AN8068571086	4642000
2010	AN8068571086	5006000
2011	AN8068571086	4837000
2012	AN8068571086	6312000
2013	AN8068571086	8478000
2003	ANN6748L1027	26679
2004	ANN6748L1027	29661
2005	ANN6748L1027	65606
2006	ANN6748L1027	25151
2007	ANN6748L1027	28439
2008	ANN6748L1027	18375
2009	ANN6748L1027	17421
2010	ANN6748L1027	27269
2011	ANN6748L1027	62001
2012	ANN6748L1027	39696
2013	ANN6748L1027	39492
2003	BMG0129K1045	.
2004	BMG0129K1045	0
2005	BMG0129K1045	741997
2006	BMG0129K1045	58002
2007	BMG0129K1045	13546
2008	BMG0129K1045	80947
2009	BMG0129K1045	142666
2010	BMG0129K1045	239957
2011	BMG0129K1045	295522
2012	BMG0129K1045	618217
2013	BMG0129K1045	777386

Re: calculating lag

mohamed_zaki — Sat, 25 Oct 2014 07:13:51 GMT

what do you mean by lag.

Re: calculating lag

AbuChowdhury — Sat, 25 Oct 2014 07:23:50 GMT

Say, these are asset values of the companies at time t. I want to have asset values at t-1. This means, in year 2004, I want the asset value of year 2003.

Re: calculating lag

mohamed_zaki — Sat, 25 Oct 2014 09:09:17 GMT

proc sort data=have;

by company_id year;

run;

data want;

set have;

by company_id;

retain x .;

if first.company_id then x=.;

Else if not last.company_id then output;

x=asset;

run;

Re: calculating lag

KachiM — Sat, 25 Oct 2014 10:53:43 GMT

Assuming that the company_id is unique then sort the data set by it.

proc sort data = have;

by company_id;

run;

There is a SAS LAG() function available. The following code just saves the previous ASSET and writes it on the next observation.

The empty format statement retains the order of the variables.

data want;

format year company_id asset _lag;

retain prev_asset _lag;

drop prev_asset;

do until(last.company_id);

set have;

by company_id;

if first.company_id then do; prev_asset = asset; _lag = .; end;

else prev_asset = asset;

output;

_lag = prev_asset;

end;

run;

Re: calculating lag

data_null__ — Sat, 25 Oct 2014 11:23:38 GMT

I'm not sure what you mean by 2003 and 2013 will have to be dropped. I learnt this from support.sas.com.

data asset;
   infile cards dsd; 
   input year company_id:$12. asset;
   cards; 
2003,AN8068571086,3109000
2004,AN8068571086,2997000
2005,AN8068571086,3496000
2006,AN8068571086,2998873
2007,AN8068571086,3169033
2008,AN8068571086,3692000
2009,AN8068571086,4642000
2010,AN8068571086,5006000
2011,AN8068571086,4837000
2012,AN8068571086,6312000
2013,AN8068571086,8478000
2003,ANN6748L1027,26679
2004,ANN6748L1027,29661
2005,ANN6748L1027,65606
2006,ANN6748L1027,25151
2007,ANN6748L1027,28439
2008,ANN6748L1027,18375
2009,ANN6748L1027,17421
2010,ANN6748L1027,27269
2011,ANN6748L1027,62001
2012,ANN6748L1027,39696
2013,ANN6748L1027,39492
2003,BMG0129K1045,.
2004,BMG0129K1045,0
2005,BMG0129K1045,741997
2006,BMG0129K1045,58002
2007,BMG0129K1045,13546
2008,BMG0129K1045,80947
2009,BMG0129K1045,142666
2010,BMG0129K1045,239957
2011,BMG0129K1045,295522
2012,BMG0129K1045,618217
2013,BMG0129K1045,777386
;;;;
   run; 
data lagasset;
   set asset;
   by company_id;
   array lasset[3];
   lasset[1] = lag1(asset);
   lasset[2] = lag2(asset);
   lasset[3] = lag3(asset);
   if first.company_id then c=0; 
   c+1; 
   do i = c to dim(lasset);
      call missing(lasset);
      end; 
   drop i c;
   run; 
proc print; 
   run; 
      

Re: calculating lag

AbuChowdhury — Sat, 25 Oct 2014 12:04:04 GMT

Thanks. I also need to drop the observations of year 2003 and 2013. So will I just write the two following lines after "drop i c"?

if year = 2003 then delete;

if year = 2013 then delete;

Re: calculating lag

AbuChowdhury — Sat, 25 Oct 2014 12:13:53 GMT

Thanks. Your code works as well. But I also need to drop the observations of year 2003 and 2013.

Re: calculating lag

AbuChowdhury — Sat, 25 Oct 2014 12:14:33 GMT

Thanks. Your code works.

Re: calculating lag

AbuChowdhury — Sat, 25 Oct 2014 21:11:37 GMT

I also need to calculate the change in asset i.e. delta_asset = asset - lasset1.

If I want to perform these operations (i.e. calculating lag then calculating the change) for more than one variable (say, asset is one variable, debt is another variable etc.) then what will be the code?

Re: calculating lag

data_null__ — Sat, 25 Oct 2014 21:35:45 GMT

This "asset - lasset1" is calculated by DIF function similar to lag but difference. I don't know what the rest of your question is saying. Show some code data etc.

Re: calculating lag

AbuChowdhury — Sun, 26 Oct 2014 09:36:46 GMT

Ok, the following is my sample dataset. ca is current asset, cl is current liability and stdebt is short term debt. So I need to calculate the lca1, lca2, lca3 (which we already did before). Now need to calculate the difference between ca and lca1 .... (which is the change in ca between year 2004 and year 2003). In this way, need to same for cl and stdebt as well. In final output, observations for year 2003 and 2013 will have to be dropped.

year companyISIN ca cl stdebt

2003	AN8068571086	10369000	6795000	1411000
2004	AN8068571086	7060000	4701000	716000
2005	AN8068571086	8554000	5515000	796000
2006	AN8068571086	9186000	6455000	1321000
2007	AN8068571086	11055000	7505000	1672000
2008	AN8068571086	12894000	8124000	1597000
2009	AN8068571086	13650000	7259000	1125000
2010	AN8068571086	18098000	10865000	2595000
2011	AN8068571086	20539000	10538000	1377000
2012	AN8068571086	24156000	12368000	2121000
2013	AN8068571086	26225000	13525000	2783000
2003	ANN6748L1027	117555	42087	8852
2004	ANN6748L1027	119830	33449	7468
2005	ANN6748L1027	175355	66052	12915
2006	ANN6748L1027	177348	46268	2586
2007	ANN6748L1027	192332	50215	8252
2008	ANN6748L1027	197199	53846	3759
2009	ANN6748L1027	209047	61461	3868
2010	ANN6748L1027	232885	62394	8450
2011	ANN6748L1027	312343	135418	14527
2012	ANN6748L1027	258057	52148	12
2013	ANN6748L1027	204018	48645	.
2003	BMG0129K1045	.	.	.
2004	BMG0129K1045	7935	5746	0
2005	BMG0129K1045	745097	553813	496757
2006	BMG0129K1045	65682	391042	185702
2007	BMG0129K1045	18503	1534434	945035
2008	BMG0129K1045	84108	799848	136738
2009	BMG0129K1045	145607	868804	170089
2010	BMG0129K1045	241772	1110606	302223
2011	BMG0129K1045	299168	1080563	247228
2012	BMG0129K1045	623842	1079469	281611
2013	BMG0129K1045	832675	602781	365325

Re: calculating lag

data_null__ — Sun, 26 Oct 2014 11:46:58 GMT

Do you need DIF1 only or do you need 3 or more like I showed you for LAG1 LAG2 and LAG3.

How much of the documentation on this subject have you read?

Re: calculating lag

AbuChowdhury — Sun, 26 Oct 2014 12:04:38 GMT

Sometimes I need DIF1 only, and sometimes I need all three. So it's better to do for all three.

I can do the DIF1 for one variable (according to your previous code). But now there are three variables (ca, cl and stdebt).

Re: calculating lag

data_null__ — Sun, 26 Oct 2014 13:05:46 GMT

It gets a bit fiddly with multiple lags and variables. How much of the documentation on this subject have you read?

data asset;
   infile cards expandtabs firstobs=2; 
   input year company_id:$12. ca cl stdebt;
   cards; 
year   companyISIN     ca            cl             stdebt
2003  AN8068571086   10369000 6795000  1411000
2004  AN8068571086   7060000  4701000  716000
2005  AN8068571086   8554000  5515000  796000
2006  AN8068571086   9186000  6455000  1321000
2007  AN8068571086   11055000 7505000  1672000
2008  AN8068571086   12894000 8124000  1597000
2009  AN8068571086   13650000 7259000  1125000
2010  AN8068571086   18098000 10865000 2595000
2011  AN8068571086   20539000 10538000 1377000
2012  AN8068571086   24156000 12368000 2121000
2013  AN8068571086   26225000 13525000 2783000
2003  ANN6748L1027   117555   42087 8852
2004  ANN6748L1027   119830   33449 7468
2005  ANN6748L1027   175355   66052 12915
2006  ANN6748L1027   177348   46268 2586
2007  ANN6748L1027   192332   50215 8252
2008  ANN6748L1027   197199   53846 3759
2009  ANN6748L1027   209047   61461 3868
2010  ANN6748L1027   232885   62394 8450
2011  ANN6748L1027   312343   135418   14527
2012  ANN6748L1027   258057   52148 12
2013  ANN6748L1027   204018   48645 .
2003  BMG0129K1045   .  .  .
2004  BMG0129K1045   7935  5746  0
2005  BMG0129K1045   745097   553813   496757
2006  BMG0129K1045   65682 391042   185702
2007  BMG0129K1045   18503 1534434  945035
2008  BMG0129K1045   84108 799848   136738
2009  BMG0129K1045   145607   868804   170089
2010  BMG0129K1045   241772   1110606  302223
2011  BMG0129K1045   299168   1080563  247228
2012  BMG0129K1045   623842   1079469  281611
2013  BMG0129K1045   832675   602781   365325
;;;;
   run; 
data difasset;
   set asset;
   by company_id;
   array _v  ca   cl   stdebt;
   array dca[3];
   array dcl[3];
   array dstdept[3];
   array _d1 dca1 dcl1 dstdept1;
   array _d2 dca2 dcl2 dstdept2;
   array _d3 dca3 dcl3 dstdept3;
   do i = 1 to dim(_v);
      _d1 = dif1(_v);
      _d2 = dif2(_v);
      _d3 = dif3(_v);
      end; 
   if first.company_id then c=0; 
   c + 1; 
   do i = c to dim(_v);
      call missing(of dca dcl dstdept);
      end; 
   drop i c;
   run; 
proc print; 
   run; 
      

Re: calculating lag

AbuChowdhury — Sun, 26 Oct 2014 13:11:22 GMT

Thanks. I will try the code and let you know. I did not read the documentation at this moment. Will you please send me the link?

Re: calculating lag

art297 — Sun, 26 Oct 2014 13:39:57 GMT

This sure sounds like a FIFO problem. What do you need to do with the lagged data once you get them?

The following keeps the current and up to the last three values for each variable and then calculates the average of the up to 4 values:

data want (drop=counter);

set have;

by companyISIN notsorted;

retain counter .;

array ca_stack {0:3};

array cl_stack {0:3};

array stdebt_stack {0:3};

retain ca_stack cl_stack stdebt_stack;

if first.companyISIN then do;

call missing(of ca_stack{*});

call missing(of cl_stack{*});

call missing(of stdebt_stack{*});

counter=0;

end;

counter+1;

ca_stack{mod(counter,4)} = ca;

cl_stack{mod(counter,4)} = cl;

stdebt_stack{mod(counter,4)} = stdebt;

if year not in (2003,2013) then do;

ca_avg = mean(of ca_stack{*});

cl_avg = mean(of cl_stack{*});

stdebt_avg = mean(of stdebt_stack{*});

output;

end;

run;

Re: calculating lag

data_null__ — Sun, 26 Oct 2014 13:44:07 GMT

I like this version a bit better.

data difasset2;
   set asset;
   by company_id;
   array _v  ca   cl   stdebt;
   array _d[3,3] dca1 dcl1 dstdept1 dca2 dcl2 dstdept2 dca3 dcl3 dstdept3;
   do i = 1 to dim(_v);
      _d[1,i] = dif1(_v);
      _d[2,i] = dif2(_v);
      _d[3,i] = dif3(_v);
      end; 
   if first.company_id then c=0; 
   c + 1; 
   do i = c to dim(_v);
      do j = 1 to dim(_v);
         call missing(of _d[i,j]);
         end; 
      end; 
   drop i c j;
   run;

Re: calculating lag

AbuChowdhury — Wed, 29 Oct 2014 07:14:44 GMT

Hi again, I need to calculate ca at time t+1 as well. So, ca values at t-1, t and t+1.

Re: calculating lag

data_null__ — Wed, 29 Oct 2014 12:05:48 GMT

I should have guessed. :smileyplain: I should have asked before now. Do you have SAS/ETS software? Type in PROC EXPAND; RUN; and see what your messages are. If that works consult the documentation. It does lags leads and a lot more.