How to code before and after in unbalanced panel data

Reply
Occasional Contributor
Posts: 9

How to code before and after in unbalanced panel data

Dear SAS communities,

I have an unbalanced dataset as follows:

I want to code pre-sale and post-sale for each firm ID before and after the eventdummy is equal to 1. In more details, I want "pre" one, two and three periods before the eventdummy equals 1 and "post" also one, two and three periods after the eventdummy equals 1. Also,if the years are not consecutive, the pre and post should stop and should only reflect exactly one or two or three years before and after the event. Ex: for firm ID 001335, event occurs in year 1989. So, pre-sale one year prior should equal 3.582, which is year 1988, but pre-sale two years prior should not be 3.878 because that is the value in year 1986. which is 3 years prior, not 2 years prior. In this sale, the pre should have 2 values: pre one year and pre three years and no value for pre two years. Another issue is that sometimes the event occurs several years in a row. In that case, "pre" is defined as before the first event and "post" is defined as after the last event. Ex: for firm ID 001338, the event dummy is equal to 1 four years in a row. Therefore, pre-sale one period prior should be 906.2, which is year 1990 and post-sale should be 742, which is year 1995.

Thank you very much for any amount of help.

Best,

Hinh

FirmIDYearSaleEventdummy
0010101999290
001010200037.40
0010102001210
001010200226.40
0010102003121.10
001020198828.0941
001058198731.8791
00116120127320
001164200210801
00120920121004.90
0012451990241.7110
0012451991254.0130
0012451992308.0970
0012451993379.8260
0012451994449.9910
001245199500
00127719891.7660
00127719901.5791
00127719911.8181
00127719921.0241
001300201247360
0013011988301.11
0013011989174.20
00133519863.8780
00133519883.5820
00133519893.1681
00133519904.1930
00133519912.6930
00133519923.5780
00133519935.030
00133519944.6180
00133519953.1730
00133619878.1450
00133619886.5370
00133619898.2460
00133619906.3890
00133619926.8830
00133619935.2920
00133619949.2610
00133619953.9950
00133619964.0220
00133619975.7650
00133619983.7610
00133719912.7170
00133719921.7610
001356201227020
001369198912.1680
00138220123660
00138319860.3360
0013881986504.4810
0013881988687.90
0013881989883.40
0013881990906.20
00138819919391
00138819928891
00138819938571
00138819948311
00138819957420
00138819969140
00138819978550
00138819999910
001388200011780
001388200117170
001388200211291
00138820039101
00138820049451
00138820059980
00138820069870
00138820088910
00138820099881
001388201010730
00138820119810
0013931993124.0620
Super User
Posts: 10,023

Re: How to code before and after in unbalanced panel data

So you have two different criteria for pre-sale and post-sale ?

data have;
input FirmID $     Year     Sale     Eventdummy ;
cards;
001010     1999     29     0
001010     2000     37.4     0
001010     2001     21     0
001010     2002     26.4     0
001010     2003     121.1     0
001020     1988     28.094     1
001058     1987     31.879     1
001161     2012     732     0
001164     2002     1080     1
001209     2012     1004.9     0
001245     1990     241.711     0
001245     1991     254.013     0
001245     1992     308.097     0
001245     1993     379.826     0
001245     1994     449.991     0
001245     1995     0     0
001277     1989     1.766     0
001277     1990     1.579     1
001277     1991     1.818     1
001277     1992     1.024     1
001300     2012     4736     0
001301     1988     301.1     1
001301     1989     174.2     0
001335     1986     3.878     0
001335     1988     3.582     0
001335     1989     3.168     1
001335     1990     4.193     0
001335     1991     2.693     0
001335     1992     3.578     0
001335     1993     5.03     0
001335     1994     4.618     0
001335     1995     3.173     0
001336     1987     8.145     0
001336     1988     6.537     0
001336     1989     8.246     0
001336     1990     6.389     0
001336     1992     6.883     0
001336     1993     5.292     0
001336     1994     9.261     0
001336     1995     3.995     0
001336     1996     4.022     0
001336     1997     5.765     0
001336     1998     3.761     0
001337     1991     2.717     0
001337     1992     1.761     0
001356     2012     2702     0
001369     1989     12.168     0
001382     2012     366     0
001383     1986     0.336     0
001388     1986     504.481     0
001388     1988     687.9     0
001388     1989     883.4     0
001388     1990     906.2     0
001388     1991     939     1
001388     1992     889     1
001388     1993     857     1
001388     1994     831     1
001388     1995     742     0
001388     1996     914     0
001388     1997     855     0
001388     1999     991     0
001388     2000     1178     0
001388     2001     1717     0
001388     2002     1129     1
001388     2003     910     1
001388     2004     945     1
001388     2005     998     0
001388     2006     987     0
001388     2008     891     0
001388     2009     988     1
001388     2010     1073     0
001388     2011     981     0
001393     1993     124.062     0
;
run;
proc sql;
 create table temp as 
  select *,min(Year) as Year_min,max(Year) as Year_max
   from have
    group by FirmID;
quit;
data want;
 if _n_ eq 1 then do;
  if 0 then set temp(rename=(sale=_sale));
  declare hash h(dataset:'temp(rename=(sale=_sale) where=(Eventdummy=0))');
  h.definekey('FirmID','Year');
  h.definedata('_sale');
  h.definedone();
 end;
set temp;
if Eventdummy=1 then do;
 n=0;
 do i=Year-1 to Year_min by -1;
  _sale=.;
  rc=h.find(key:FirmID,key:i);
  n+1;
  if n=1 and _sale ne . then _1_years_prior=_sale;
   else if n=2 and _sale ne . then  _2_years_prior=_sale;
    else if n=3 and _sale ne . then  _3_years_prior=_sale;
     else if n gt 3 then leave;
 end;

 n=0;
 do i=Year+1 to Year_max;
  _sale=.;
  rc=h.find(key:FirmID,key:i);
  if rc eq 0 then n+1;
  if n=1 and _sale ne . then _1_years_post=_sale;
   else if n=2 and _sale ne . then  _2_years_post=_sale;
    else if n=3 and _sale ne . then  _3_years_post=_sale;
     else if n gt 3 then leave;
 end; 
end;
drop _sale i n rc Year_:;
run;


Xia Keshan

Ask a Question
Discussion stats
  • 1 reply
  • 163 views
  • 0 likes
  • 2 in conversation