Fluorite | Level 6

## How to find the first negative value in every specific quarters

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;
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;``````

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

## Re: How to find the first negative value in every 20 quarters with at least 50% of the value

What happens after observation 20?

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

``````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;``````

5 REPLIES 5
Super User

## Re: How to find the first negative value in every 20 quarters with at least 50% of the value

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

Fluorite | Level 6

## Re: How to find the first negative value in every 20 quarters with at least 50% of the value

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".

Tourmaline | Level 20

## Re: How to find the first negative value in every 20 quarters with at least 50% of the value

What happens after observation 20?

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

``````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;``````

Fluorite | Level 6

## Re: How to find the first negative value in every 20 quarters with at least 50% of the value

Thank you so much!

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

Tourmaline | Level 20

## Re: How to find the first negative value in every 20 quarters with at least 50% of the value

> 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.

Discussion stats
• 5 replies
• 766 views
• 2 likes
• 3 in conversation