BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
latte747
Fluorite | Level 6

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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;

 

 

View solution in original post

5 REPLIES 5
ballardw
Super User

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
Fluorite | Level 6

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
Tourmaline | Level 20

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
Fluorite | Level 6

Thank you so much!

 

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

ChrisNZ
Tourmaline | Level 20

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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