topic Re: Highlighting all one-year prior observations using intnx function in SAS Programming

Highlighting all one-year prior observations using intnx function

KS99 — Wed, 11 May 2022 16:59:06 GMT

Hi, SAS community,

Sorry if I am bothering you with many questions.

I proc sorted the data by CUSIP and ANNDATS. I am trying to mark all the obs. over one-year range (Beforeyear) prior to a designated row (k=1) using intnx. I ran the following code, but SAS accomplish the task for certain k=1, but not for other k=1. Can anyone please check why this happens?

My code is written as follows:

data Brokers3(drop = a); do until (last.cusip);
      set Brokers2; by CUSIP; if k = 1 then a = anndats; end;
   do until (last.cusip); set Brokers2; by CUSIP; 
Beforeyear = (a > anndats > intnx('year', a, -1, 's')); output; end; run;

And what I get, which is problematic, is the following.

ANNDATS	CUSIP	k	Beforeyear
7/8/1998	00036110	0	0
7/9/1998	00036110	0	0
10/1/1998	00036110	0	0
10/8/1998	00036110	0	0
12/17/1998	00036110	0	0
1/7/1999	00036110	0	0
2/12/1999	00036110	0	0
2/12/1999	00036110	0	0
2/16/1999	00036110	0	0
2/18/1999	00036110	0	0
3/16/1999	00036110	0	0
3/17/1999	00036110	0	0
4/19/1999	00036110	0	0
5/6/1999	00036110	0	0
6/11/1999	00036110	0	0
6/25/1999	00036110	0	0
6/28/1999	00036110	0	0
9/1/1999	00036110	1	0
9/20/1999	00036110	0	0
10/14/1999	00036110	0	0
10/7/2010	00036110	0	0
10/7/2010	00036110	0	0
10/18/2010	00036110	0	0
12/16/2010	00036110	0	1
12/16/2010	00036110	0	1
12/17/2010	00036110	0	1
12/17/2010	00036110	0	1
3/16/2011	00036110	0	1
7/8/2011	00036110	0	1
7/8/2011	00036110	0	1
7/15/2011	00036110	0	1
9/12/2011	00036110	0	1
9/21/2011	00036110	0	1
10/6/2011	00036110	0	1
10/7/2011	00036110	0	1
10/21/2011	00036110	0	1
10/25/2011	00036110	0	1
11/7/2011	00036110	1	0
12/8/2011	00036110	0	0

Many thanks in advance!

Sincerely,

KS -,

Re: Highlighting all one-year prior observations using intnx function

mkeintz — Wed, 11 May 2022 17:59:42 GMT

You are using two DO UNTIL (last.cusip) loops. The first loop reads all the observations and set the value of a for each instance of K=1. So the second K=1 record overwrites the value of a established by the previous k=1 record.

I think you will get what you want by changing

do until (last.cusip);

  do until (last.cusip or k=1);

for both loops.

I'd also suggest generating the cutoff date (i.e. a lower_bound on the date filter) when you encounter the k=1 record. And between the two loops, if there was no k=1 record encountered in the upper loop set the lower_bound to dec 31, 9999, so the BEFOREYEAR will always be zero.

Here's some untested code:

data Brokers3(drop = lower_bound);
  do until (last.cusip or k=1);
    set Brokers2;
    by CUSIP;
    if k = 1 then lower_bound=intnx('year',anndats,-1,'sameday');
  end;
  if lower_bound=. then lower_bound='31dec9999'd;

  do until (last.cusip or k=1); 
    set Brokers2; 
    by CUSIP; 
    Beforeyear = (anndats > lower_bound);
    output; 
  end; 
run;

Re: Highlighting all one-year prior observations using intnx function

Reeza — Wed, 11 May 2022 19:26:13 GMT

Your one year prior doesn't seem to be "one calendar year" simply the year before in the data set according to what you've shown. Is that correct?

2010 data is well over one year the k=1 date which is 1999.

Re: Highlighting all one-year prior observations using intnx function

KS99 — Thu, 12 May 2022 03:09:15 GMT

Thank you, mkeintz, for helping me this time plus your many previous helps.
Either code you suggested works perfectly well!

Wish you a good evening,

KS -

Re: Highlighting all one-year prior observations using intnx function

KS99 — Thu, 12 May 2022 03:12:13 GMT

Thank you Reeza for pointing out.
I abbreviated many obs. to present my dataset here.
What I simple mean is to check all previous obs. within 365 days' range.

Wish you a good evening,

KS -,