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 -,
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);
to
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;
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);
to
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;
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.
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 25. 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.