creating variable based on some criteria/logic

[Ramin1]

Hi experts, I have the attached data. I want to create another variable based on some criteria. The new variable High will have 1 if id (Co_per_role) score more than 100 percent (>1) in the confidence column/variable and show this same behavior at least twice in the sample period; I will assign 1 to high from the first time such behavior shows up else it will be 0.

For instance;

co_per_rol year confidence High

1234 2000 .87 0

1234 2001 .78 0

1234 2002 .45 0

1235 2000 .5 0

1235 2001 1.45 1

1235 2002 .64 1

1235 2003 1.2 1

1236 2000 .67 0

1236 2001 1.23 0

1236 2002 .54 0

[ChrisNZ]

> show this same behavior at least twice in the sample period

The behaviour only happens once in your example: once per year.

Also please post your data as code so we can easily use it.

[ballardw]

You should define exactly what "sample period" means if it has any bearing on the problem.

I suspect you are looking for something like this:

data want; 
   set sample;
   by co_per_rol;
   retain high;
   if first.co_per_rol then high=0;
   high = max(high, confidence >0);
run;

This assumes the data is actually sorted by co_per_rol and year.

Retain keeps the value of a variable across data step boundaries.

The BY statement creates automatic variables indicating whether the current record is the first or last of a by group that is 1 (true) or 0 (false) and can be used to reset a variable for the first record.

Max function returns the largest value, so if High has been set to 1 it stays1. The comparison Confidence > 0 will return 1 when true or 0 when false.

[Ramin1]

I am sorry, I should describe the problem more clearly. Sample period means the sample year which is 2000 to 2019 in my data. Therefore, if someone (co_per_rol) has confidence more than 1 and shows this behavior at least twice between 2000 and 2019 then I will assign 1 from the first time he shows this behavior. Thanks, I got my answer from FreelanceReinhard.

[ChrisNZ]

Pick @FreelanceReinh 's reply as the answer then please.

[FreelanceReinh · Accepted solution]

Hi @Ramin1,

If your dataset is grouped by CO_PER_ROL and sorted by YEAR within the CO_PER_ROL BY-groups, you can use a double DOW loop:

data want(drop=_:);
do _n=1 by 1 until(last.co_per_rol);
  set sample;
  by co_per_rol notsorted;
  if confidence>1 then do;
    if _y=. then _y=year;
    _c=sum(_c,1);
  end;
end;
do _n=1 to _n;
  set sample;
  High=_c>1 & year>=_y;
  output;
end;
run;