topic Re: Retrieving past 5 years record from a focal date in SAS Programming

Retrieving past 5 years record from a focal date

jimmychoi — Mon, 04 Feb 2019 01:43:25 GMT

Hi,

I have observations with the 'date_created' column having dates as in YYYY-MM-DD format.

I would like to count the number of observations that are created within past 5 years from the focal observation, by adding 'numof5years' column.

my data:

[data_created]

2013-03-01

2014-07-30

2014-09-15

2015-02-22

2018-01-02

2018-03-02

2019-01-26

expected output:

[data_created] [numof5years]

2013-03-01 0

2014-07-30 1

2014-09-15 2

2015-02-22 3

2018-01-02 4

2018-03-02 4

2019-01-26 5

thanks in advance.

Re: Retrieving past 5 years record from a focal date

ChrisNZ — Mon, 04 Feb 2019 02:14:38 GMT

Like this?

data HAVE;
 input DATE yymmdd10.;
 cards;
2013-03-01
2014-07-30
2014-09-15
2015-02-22
2018-01-02
2018-03-02
2019-01-26
run;
proc sql;
  select a.DATE     format=date9.
        ,count(b.DATE)        
  from HAVE a
         left join
       HAVE b
        on 0 < yrdif(b.DATE,a.DATE) <= 5
  group by a.DATE
  order by a.DATE;

DATE
01MAR2013	0
30JUL2014	1
15SEP2014	2
22FEB2015	3
02JAN2018	4
02MAR2018	4
26JAN2019	5

Re: Retrieving past 5 years record from a focal date

jimmychoi — Mon, 04 Feb 2019 02:18:40 GMT

Hi Chris, actually i'm doing this in a DATA procedure.
will there be a comparable function as 'yrdif' of SQL?

Re: Retrieving past 5 years record from a focal date

Reeza — Mon, 04 Feb 2019 02:20:57 GMT

For a within table lookup like this, it's better to stick with SQL, it's much harder to do this type of join in a data step.

Re: Retrieving past 5 years record from a focal date

ChrisNZ — Mon, 04 Feb 2019 02:41:44 GMT

> actually i'm doing this in a DATA procedure. will there be a comparable function as 'yrdif' of SQL?

You can use that function in data step as well. Cartesian products are not easily obtained in data steps though.

The type of join guides the coding choice here.

Re: Retrieving past 5 years record from a focal date

novinosrin — Mon, 04 Feb 2019 03:25:20 GMT

haven't used yrdiff in a while. @ChrisNZ is brilliant as usual

copied(plagiarized from his) for hash demo


data have;
input date :yymmdd10.;
format date yymmdd10.;
cards;
2013-03-01
2014-07-30
2014-09-15
2015-02-22
2018-01-02
2018-03-02
2019-01-26
;

data want ;
 if _n_=1 then do;
 if 0 then set have(rename=(date=_date));
   declare hash H (dataset:'have(rename=(date=_date))',ordered: "A") ;
   h.definekey  ("_date") ;
   h.definedone () ;
   declare hiter hh('h');
   end;
set have;
years=0;
do while(hh.next()=0);
if 	0 < yrdif(_date,DATE,'age') <= 5 then years=sum(years,1);
end;
drop _:;
run;

Re: Retrieving past 5 years record from a focal date

ChrisNZ — Mon, 04 Feb 2019 03:39:09 GMT

@novinosrin

> @ChrisNZ is brilliant as usual

Re: Retrieving past 5 years record from a focal date

s_lassen — Mon, 04 Feb 2019 07:39:42 GMT

You can do it in a datastep like this:

data HAVE;
 input DATE yymmdd10.;
 format date yymmdd10.;
 cards;
2013-03-01
2014-07-30
2014-09-15
2015-02-22
2018-01-02
2018-03-02
2019-01-26
;run;

data want;
  set have;
  retain _P_ 1;
  do _P_=_P_ to _N_-1;
    set have(keep=date rename=(date=old_date)) point=_P_;
    if old_date>=intnx('year',date,-5,'S') then leave;
    end;
  numof5years=_N_-_P_;
  drop old_date;
run;

If you have a BY variable in the real data, just ad a couple of lines:

data want;
  set have;
  by key;
  if first.key then
    _P_=_N_;
  retain _P_ 1;
  do _P_=_P_ to _N_-1;
    set have(keep=date rename=(date=old_date)) point=_P_;
    if old_date>=intnx('year',date,-5,'S') then leave;
    end;
  numof5years=_N_-_P_;
  drop old_date;
run;

Re: Retrieving past 5 years record from a focal date

Ksharp — Mon, 04 Feb 2019 11:12:52 GMT

data HAVE;
 input DATE yymmdd10.;
 format DATE yymmdd10.;
 cards;
2013-03-01
2014-07-30
2014-09-15
2015-02-22
2018-01-02
2018-03-02
2019-01-26
run;
proc sql;
select *,(select count(*) from have 
where  intnx('year',a.date,-5,'s') < date < a.date) as want
 from have as a;
quit;

Re: Retrieving past 5 years record from a focal date

jimmychoi — Mon, 04 Feb 2019 14:37:30 GMT

Hi Nov, brilliant as usual!

i have modified the range,

if 	0 <= yrdif(_date,DATE,'age') < 5

because i wanted to count the dates within the same year.

but by doing this, this makes a tiny problem... that some are counted twice..

when comparing with the hashtable, what if I want to cancel out counts coming from myself?

Re: Retrieving past 5 years record from a focal date

jimmychoi — Mon, 04 Feb 2019 14:42:47 GMT

wow adding a calculated row using sql... thnx a lot K

Re: Retrieving past 5 years record from a focal date

novinosrin — Mon, 04 Feb 2019 18:02:04 GMT

data have;
input date :yymmdd10.;
format date yymmdd10.;
cards;
2013-03-01
2014-07-30
2014-09-15
2015-02-22
2018-01-02
2018-03-02
2019-01-26
;

proc sql;
create table want as
select a.date,sum(0 < yrdif(b.date,a.DATE,'age') <= 5)  as count
from have a, have b
where b.date<=a.date
group by a.date;
quit;