How to find the first negative value in every specific quarters

[latte747]

I want to find the first negative observation during the 20 quarters following "mark = 1" that has at least 50% of the (mark=1)'s "acc".

In this dataset, datadate is composed of five digits = (year * 10) + quarter. Thus, there are four quarters in each year. For example, Obs. 1 shows the 1st Quarter in Year 1989.

data test1;
set test1;
period + 1;
by gvkey datadate;
if first.gvkey then period = 1;
run;

data test2; 
	set test1;
	if mark = 1 then do;
    first_period = period;
    first_acc = acc;
  	end;

  	else 
	do;
	if -acc > first_acc/2 and period <= first_period + 20 then first_neg_50pct = 1;
  	retain first:;
run;

[ballardw]

Does Name ever change within the values of Gvkey? If so, how does that effect the process? If name has not impact on the process then please say so. Otherwise the problem description could be incomplete.

What if a negative value meets the criteria for previous "mark=1" group? Consider in your example below if the 7th observation were actually the 8th and the value for the reordered data had ACC = 0.1 or such . Then the value meets the criteria for the "mark=1" that started on obs=1: it is within 20 quarters and magnitude is large enough for the previous mark group but not the one with Acc=0.1

[latte747]

Thank you for your interest.

1. Name has no impact, so only gvkey matters.

2. If a negative value meets the previous criteria as well, then I'd like to count it twice. But, since "first_negative_50%" is an indicator variable, it would be simply "1".

[ChrisNZ · Accepted solution]

What happens after observation 20?

Untested as no usable data was supplied, but something like this should work, provided the data is sorted.

Adapt to your needs.

data WANT;
  set HAVE;
  if MARK=1 and ^MARK then do;
    FIRST_PERIOD=PERIOD;
    FIRST_ACC=ACC;
  end;
  else if -ACC > FIRST_ACC/2 and PERIOD <= FIRST_PERIOD+20 then FIRST_NEG_50PCT=1;
  retain FIRST:;
run;

[latte747]

Thank you so much!

But, how can I extract only "the first" negative value?

[ChrisNZ]

> how can I extract only "the first" negative value?

You can set a flag when you found a value. Don't forget to retain that flag.