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

How to find the first negative value in every specific quarters

latte747 — Sun, 18 Apr 2021 06:30:50 GMT

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;

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

ballardw — Wed, 14 Apr 2021 21:30:59 GMT

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

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

ChrisNZ — Wed, 14 Apr 2021 21:36:55 GMT

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;

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

latte747 — Wed, 14 Apr 2021 22:17:11 GMT

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

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

latte747 — Thu, 15 Apr 2021 03:27:46 GMT

Thank you so much!

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

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

ChrisNZ — Thu, 15 Apr 2021 06:02:32 GMT

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