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 -,
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: