Hi,

I have a dataset that includes several observations for one id and i need to create a sequencing variable that increments in value everytime obs variable is >0.1

my data is something like this :


id year gen env cons obs seq
1 2000 .00058276 0 0 .088 0
1 2001 .00058276 0 0 .1054 0
1 2002 .00058276 0 0 .1241 0
1 2003 .00058276 0 0 .0861 0
1 2004 .00058276 0 0 .1175 0
1 2005 .00058276 0 0 .1004 0
1 2006 .00058276 0 0 .122 0
1 2007 .00058276 0 0 .1095 0
1 2008 .00058276 0 0 .1139 0
1 2009 .00058276 0 0 .0971 0
1 2010 .00058276 0 0 .0869 0
1 2011 .00058276 0 0 .0889 0
1 2012 .00058276 0 0 .1068 0
1 2013 .00058276 0 0 .1231 0
1 2014 .00058276 0 0 .1085 0
1 2015 .00058276 0 0 .0869 0
1 2016 .00058276 0 0 .1097 0
1 2017 .00058276 0 0 .1117 0
2 2000 .00069248 0 0 .1185 0
2 2001 .00069248 0 0 .0881 0
2 2002 .00069248 0 0 .1168 0
2 2003 .00069248 0 0 .1179 0
2 2004 .00069248 0 0 .0891 0
2 2005 .00069248 0 0 .1087 0
2 2006 .00069248 0 0 .0976 0
2 2007 .00069248 0 0 .0996 0
2 2008 .00069248 0 0 .094 0


i want my seq(intitial value is 0) to increment by 1 everytime obs is >0.1

but only upto id =1

then seq should reset to 0 and for id=2 the same iteration

i would also like to know how to create a subsequent variable that would identify the largest value of seq for each individual using a flag variable that return 1 if the corresponding seq is the largest for that id and then create another flag variable that identifies id's under risk havig a seq value of greater than 5 for that person


can anyone help me on this

thank you