topic Re: My intnx function is not working in SAS Programming

My intnx function is not working

KS99 — Wed, 11 May 2022 01:05:26 GMT

Hi, SAS community!

While I was working with intnx function, I simply got entangled in a mire.

What I want to get is the following:

CUSIP	ANNDATS	mark	Oneyear
00036110	6/25/1999		0
00036110	6/28/1999		0
00036110	9/1/1999	1	1
00036110	9/20/1999		1
00036110	10/14/1999		1
00036110	12/17/1999		1
00036110	12/22/1999		1
00036110	12/23/1999		1
00036110	1/13/2000		1
00036110	2/3/2000		1
00036110	7/11/2000		1
00036110	7/11/2000		1
00036110	7/17/2000		1
00036110	7/25/2000		1
00036110	9/1/2000		0
00036110	9/5/2000		0
00040010	3/18/2003		0
00040010	5/27/2003	1	1
00040010	7/15/2003		1
00040010	1/2/2004		1
00040010	1/14/2004		1
00040010	4/13/2004		1
00040010	7/21/2004		0

The data is proc sorted by CUSIP and ANNDATS.

I have occasional mark=1. What I want to accomplish is, starting from mark=1, create Oneyear=1's over the subsequent 1-year period. So, Oneyear has values 1 over 9/1/1999~7/25/2000 for CUSIP 00036110; values 1 over 5/27/2003~4/13/2004 for CUSIP 00040010.

My code, which is also provided by a SAS community fellow, runs as follows:

data Brokers2(drop = a); do until (last.cusip);
      set Brokers1; by CUSIP; if mark = 1 then a = anndats; end;
   do until (last.cusip); set Brokers1; by CUSIP; 
Oneyear = (anndats <= intnx('year', a, 1, 's')); output; end; run;

However, what I get is a mumble-jumble Oneyear variable, like below:

CUSIP	ANNDATS	mark	Oneyear
00036110	6/25/1999		1
00036110	6/28/1999		1
00036110	9/1/1999	1	1
00036110	9/20/1999		1
00036110	10/14/1999		1
00036110	12/17/1999		1
00036110	12/22/1999		1
00036110	12/23/1999		1
00036110	1/13/2000		1
00036110	2/3/2000		1
00036110	7/11/2000		1
00036110	7/11/2000		1
00036110	7/17/2000		1
00036110	7/25/2000		1
00036110	9/1/2000		1
00036110	9/5/2000		1
00040010	3/18/2003		1
00040010	5/27/2003	1	1
00040010	7/15/2003		1
00040010	1/2/2004		1
00040010	1/14/2004		1
00040010	4/13/2004		1
00040010	7/21/2004		0

Any help to fix my code will be greatly appreciated!!

Sincerely,

KS -,

Re: My intnx function is not working

mkeintz — Wed, 11 May 2022 02:09:46 GMT

I can see why you are getting unintended ONEYEAR=1 for dates preceding the DATE for mark=1. That's because in your second DO UNTIL ... group you have

    Oneyear = (anndats <= intnx('year', a, 1, 's'));

while you should have

    Oneyear = (a<=anndats <= intnx('year', a, 1, 's'));

This does not explain why you are getting ONEYEAR=1 for dates after INTNX('year',a,1,'s').

But I would suggest replacing what you've written with far simpler code, which doesn't need the double DO UNTIL structure:

data want (drop=_:);
  set brokers1;
  by cusip;
  retain _cutoff_date ;

  if first.cusip then _cutoff_date=.;
  if mark=1 then _cutoff_date=intnx('year',anndats,1,'sameday');
  oneyear=(anndats<=_cutoff_date);
run;

This doesn't require testing for a lower bound on anndats - i.e. it doesn't need the equivalent of "a<=" in ("a<=anndats<=intnx....") - because it is not re-reading the CUSIP, and therefore the test is not exposed to dates preceding the MARK=1 date.

Re: My intnx function is not working

KS99 — Wed, 11 May 2022 15:21:17 GMT

Thank you, mkeintz, for you help out all the time!
Your code works perfectly 😉

Sincerely,
KS -