DATA Step, Macro, Functions and more

Panel Data: Identify the first occurrence of an event within a by group

Reply
Contributor
Posts: 20

Panel Data: Identify the first occurrence of an event within a by group

I have a panel data with to identifiers: ID and Time. The dataset contains the following variables: ID, Time and V1. I want to create a fourth variable Flag which will identify the first date where V1 > 0 for each ID. For eg: The third observations identifies the third month as the first occurrence of a positive V1 for ID = 1. Again the seventh observation identifies the second month as the first occurrence of a positive V1 for ID = 2 . Thanks in advance.


ID Time V1 Flag
1 , 1, 0, 0
1 , 2, 0, 0
1 , 3, 7.5, 3
1 , 4, 6, 0
1 , 5, 0, 0
2 , 1, 0, 0
2 , 2, 9, 2
2 , 3, 9, 0
2 , 4, 0, 0
2 , 5, 2.5, 0
Super User
Posts: 17,829

Re: Panel Data: Identify the first occurrence of an event within a by group

Use retain with by groups and a found flag to indicate if the variable has already been set.
[pre]
data want;
set have;
by id;

retain found;

if first.id then found=0;

flag=0;

if v1 ne 0 and found=0 then do;
flag=time;
found=1;
end;

drop found;
run;
[/pre]
Contributor
Posts: 20

Re: Panel Data: Identify the first occurrence of an event within a by group

Thanks a lot Reeza.....This works great!!
Super User
Posts: 9,681

Re: Panel Data: Identify the first occurrence of an event within a by group

Hi.
If your data look like you post (e.g have been sorted).


[pre]

data temp;
infile datalines dlm=' ,';
input ID Time V1;
cards;
1 , 1, 0
1 , 2, 0
1 , 3, 7.5
1 , 4, 6
1 , 5, 0
2 , 1, 0
2 , 2, 9
2 , 3, 9
2 , 4, 0
2 , 5, 2.5
;
run;
data temp(drop=_id);
set temp;
retain _id .;
if id ne _id and v1 gt 0 then do;
_id=id;
flag=time;
end;
else flag=0;
run;
[/pre]






Ksharp

Message was edited by: Ksharp Message was edited by: Ksharp
Contributor
Posts: 20

Re: Panel Data: Identify the first occurrence of an event within a by group

Thanks a lot to Ksharp as well.....This code also does the job....
Thanks Guys!!!
Ask a Question
Discussion stats
  • 4 replies
  • 2291 views
  • 0 likes
  • 3 in conversation