How to delte individuals in paneldata

[Louise_svk274]

Hey all,

I’m working with panel data for different individuals at different time periods. Below you see a bit of the data I’m working with.  In reality it is much bigger, about 7 million observations.
Each individual appears 12 times in the dataset – which can be seen by looking at the “years” or at “periods”. In “period2” I have defined that when “employment_staus” is equal to U (U=unemployed and E= employed in the given year) then period2 should be 0.
I would like  to do two things, but I can’t find out how to do it:

1. I would like two delete individuals from the data set if they in the first two years are unemployed. (so if this appears I would like to delete all observations for this individual). Furthermore, I would also like to delete individuals from the dataset if they in the last two “years/periods” are unemployed.  I would also like to delete individuals who has been unemployed more then one time in the 12 period. In the dataset.
   So this would mean that in my data set, I would like to delete individual 1 because he gets unemployed in period 2. I would also like to delete individual 3 because he is unemployed in the first period and in the last 3 periods and the
   
   
2. Then after this: I would like to make a “event plot” where the variable “event_plot” is equal to zero when the event happens – which is when they get unemployed - and the years before the individual gets unemployed is minus and the years after is a plus. I have tried to make an example of the result I want, which is marked with green. I have only marked it with green for individual with id 2, because individual 1 and 3 will get delete to do the restrictions in 1)

I have tried to code some different things both creating a dataset where I use by pnr and first.  and last., and tried to do some proc sql but it does not seem to work.

Hope you can help me and please let me know if anything is unclear.
(And sorry for my English if it not correct – but hope you understand anyway 😊)

The data in sas;

data sas_pnr ;

  input id year $employment_status$ periods period2 ;

datalines;

1     1989 'E'  1     1     

1     1990 'U'  2     0     

1     1991 'E'  3     3     

1     1992 'E'  4     4     

1     1993 'E'  5     5     

1     1994 'E'  6     6     

1     1995 'E'  7     7     

1     1996 'E'  8     8     

1     1997 'E'  9     9     

1     1998 'E'  10    10   

1     1999 'E'  11    11   

1     2000 'S'  12    12   

2     1989 'E'  1     1     

2     1990 'E'  2     2     

2     1991 'E'  3     3     

2     1992 'E'  4     4     

2     1993 'U'  5     0     

2     1994 'E'  6     6     

2     1995 'E'  7     7     

2     1996 'E'  8     8     

2     1997 'E'  9     9     

2     1998 'E'  10    10   

2     1999 'E'  11    11    

2     2000 'E'  12    12    

3     1989 'U'  1     0     

3     1990 'E'  2     2     

3     1991 'E'  3     3     

3     1992 'E'  4     4     

3     1993 'E'  5     5     

3     1994 'U'  6     0     

3     1995 'E'  7     7     

3     1996 'E'  8     8     

3     1997 'U'  9     0     

3     1998 'U'  10    0    

3 1999     'U'  11    0    

;

[PeterClemmensen]

Regarding (1). Does this give you what you want?

data sas_pnr ;
input id year $ employment_status $ periods period2 ;
datalines;
1 1989 E 1 1 
1 1990 U 2 0 
1 1991 E 3 3 
1 1992 E 4 4 
1 1993 E 5 5 
1 1994 E 6 6 
1 1995 E 7 7 
1 1996 E 8 8 
1 1997 E 9 9 
1 1998 E 10 10 
1 1999 E 11 11 
1 2000 S 12 12 
2 1989 E 1 1 
2 1990 E 2 2 
2 1991 E 3 3 
2 1992 E 4 4 
2 1993 U 5 0 
2 1994 E 6 6 
2 1995 E 7 7 
2 1996 E 8 8 
2 1997 E 9 9 
2 1998 E 10 10 
2 1999 E 11 11 
2 2000 E 12 12 
3 1989 U 1 0 
3 1990 E 2 2 
3 1991 E 3 3 
3 1992 E 4 4 
3 1993 E 5 5 
3 1994 U 6 0 
3 1995 E 7 7 
3 1996 E 8 8 
3 1997 U 9 0 
3 1998 U 10 0 
3 1999 U 11 0 
;

data want(drop = f c);

   do until (last.id);
      set sas_pnr;
      by id;
      if periods in (1, 2, 11, 12) and employment_status = 'U' then f = 1;
      if employment_status = 'U' then c + 1;
   end;

   do until (last.id);
      set sas_pnr;
      by id;
      if c < 2 & f ne 1 then output;
   end;

   c = .;
run;

[Louise_svk274]

Yes it works very well.
Thank you so much!

[PeterClemmensen]

Ok. Can you describe your desired plot in more detail?

Perhaps provide a sample drawing?

[Louise_svk274]

So I have to write a project where I investigate how getting unemployed in a period affect furture income.
In my original dataset I also have income for each individual at each time.
I therefor want to make an event_study, where I have a treatment group and a control group. The treatment groups are the individuals who get unemployed one year in the period of 12 years and the controle group is the individuals who do not get unemployed in 12 years.  I there for want to make and event_plot where the “year” the individual gets unemployed is equal to zero, and the minus(negative numbers) therefore represent the years before the individual becomes unemployed, and the positive number after unemployment represent the years of working after unemployment.

I have tried to draw a figure that represent what I would like – maybe this show better what I would like (also the result marked with green int the previous picture represent the result I would like to have)