Hi, I am trying to set a 3-year moving window starting at a given date by the following code, data have;
infile cards dsd dlm=",";
input
ID $
publn_date :YYMMDD10.
earliest_publn_date :YYMMDD10.
;
format
publn_date YYMMDDD10.
earliest_publn_date YYMMDDD10.
;
cards;
1,1993-09-05,1990-09-04 2,1993-09-04,1990-09-04 3,1993-09-03,1990-09-04 4,1992-09-05,1990-09-04 5,1992-09-04,1990-09-04 6,1992-09-03,1990-09-04 7,1990-09-04,1990-09-04 8,1990-09-03,1990-09-04
;;;;
run;
proc sql;
create table want as
select distinct
*
from have
WHERE publn_date <= intnx('year', earliest_publn_date,4) /*a 3-year moving window starting at the earliest publication date */
AND publn_date >= earliest_publn_date
;
quit; it meanings, for each observation, if 1) 'publn_date' >= the 'earliest_publn_date' and 2) 'publn_date' <= 'earliest_publn_date' + 3-year, then it should be remain. By using the earliest_publn_date '1990-09-04' as an example, 1) the earliest 'publn_date' should be as same as earliest_publn_date '1990-09-04', 2) the latest 'publn_date' should be '1993-09-04', it is the 3 years after '1990-09-04', Namely, I expect to get table want 2,1993-09-04,1990-09-04
3,1993-09-03,1990-09-04
4,1992-09-05,1990-09-04
5,1992-09-04,1990-09-04
6,1992-09-03,1990-09-04
7,1990-09-04,1990-09-04 I expect to exclude obs 8 because publn_date 1990-09-03 is earlier than earliest_publn_date 1990-09-04. I expect to exclude obs 1 because publn_date 1993-09-04 is the 3 years after '1990-09-04', and 1993-09-05 is later than it. I failed by using my code, could you please give me some suggestions about this? thanks in advance.
... View more