For the same id, how to calculate number of months between two dates given a condition

[ak2]

Hi. Can anyone please help me to figure out how to calculate the number of months between 2 dates for the same id, and given a condition?

For each id I need to find the number of months between date2 where column replay=y and the previous date2 where the column replay=y . I need help to find a way to calculate the column called months, i.e. like this:

date1	curr	id	date2	replay	months
01.Jun.17	0	1	23.Nov.16	n	.
01.Jun.17	0	1	29.Nov.16	y	.
01.Jun.17	1	1	01.Jun.17	y	6
19.Jul.17	0	2	06.Sep.16	y	.
19.Jul.17	0	2	23.Sep.16	y	0
19.Jul.17	0	2	19.Jan.17	n	.
19.Jul.17	1	2	19.Jul.17	y	9
02.Mar.17	0	3	07.Sep.16	y	.
02.Mar.17	0	3	14.Feb.17	n	.
02.Mar.17	0	3	02.Mar.17	n	.
02.Mar.17	0	3	02.Mar.17	n	.
02.Mar.17	1	3	02.Mar.17	y	5

Help is highly appreciated.

[PaigeMiller]

First you need to have date1 and date2 as actual SAS dates, and not character strings such as '23.NOV.16'. I believe that the informat ANYDTDTE will convert these to actual SAS dates, but I haven't tried. So I leave that as a homework assignment for you.

Assuming you now have actual SAS dates

data want;
    set have;
    prevdate2=lag(date2);
    previd=lag(id);
    if replay='y' and id=previd then months=intck('month',prevdate2,date2);
run;

[Astounding]

Begin with a question ... are these true SAS dates, or are they character strings?  If they are character strings, they can be converted easily enough:

sasdate1 = input(date1, date9.);

format sasdate1 yymmdd10.;

Second, the rules about counting months need a little more detail.  For example, does this constitute a month?

January 30 to February 28

Counting days instead of months would be easy, but I'm not sure if that would meet your needs.

[SuryaKiran]

Properly sorting the data and then using FIRST. and LAG() and INTCK() function can solve your problem.

You might get negative values if same month, adjust accordingly to ignore negative values

data have;
infile datalines dlm='09'x dsd truncover;
format date1 date2 date7.;
input date1 :date7. curr  id date2 :date7. replay	:$;
datalines;
01Jun17	0	1	23Nov16	n
01Jun17	0	1	29Nov16	y
01Jun17	1	1	01Jun17	y
19Jul17	0	2	06Sep16	y
19Jul17	0	2	23Sep16	y
19Jul17	0	2	19Jan17	n
19Jul17	1	2	19Jul17	y
02Mar17	0	3	07Sep16	y
02Mar17	0	3	14Feb17	n
02Mar17	0	3	02Mar17	n
02Mar17	0	3	02Mar17	n
02Mar17	1	3	02Mar17	y
;
run;

proc sort data=have;
by id replay date2;
run;
data want;
format lag_date2 date7.;
set have;
by id replay date2;
lag_date2=lag(date2);
if replay='y' and not first.replay then month=intck('month',lag_date2,date2)-1;
run;

[Ksharp]

data have;
infile datalines dlm='09'x dsd truncover;
format date1 date2 date7.;
input date1 :date7. curr  id date2 :date7. replay	:$;
datalines;
01Jun17	0	1	23Nov16	n
01Jun17	0	1	29Nov16	y
01Jun17	1	1	01Jun17	y
19Jul17	0	2	06Sep16	y
19Jul17	0	2	23Sep16	y
19Jul17	0	2	19Jan17	n
19Jul17	1	2	19Jul17	y
02Mar17	0	3	07Sep16	y
02Mar17	0	3	14Feb17	n
02Mar17	0	3	02Mar17	n
02Mar17	0	3	02Mar17	n
02Mar17	1	3	02Mar17	y
;
run;
data want;
if _n_=1 then do;
  if 0 then set have;
  declare hash h(dataset:'have(where=(replay="y"))');
  h.definekey('id','replay','date2');
  h.definedone();
end;
set have;
by id;
retain temp;
if first.id then temp=date2;
if replay='y' then do;
  do i=date2-1 to temp by -1;
     if h.check(key:id,key:replay,key:i)=0 then do;month=intck('month',i,date2,'c');leave; end;
  end;
end;
drop i temp;
format i temp date9.;
run;


proc print noobs;run;

[novinosrin · Accepted solution]

data have;
infile datalines truncover;
format date1 date2 date7.;
input date1 :date7. curr  id date2 :date7. replay	$;
datalines;
01Jun17	0	1	23Nov16	n
01Jun17	0	1	29Nov16	y
01Jun17	1	1	01Jun17	y
19Jul17	0	2	06Sep16	y
19Jul17	0	2	23Sep16	y
19Jul17	0	2	19Jan17	n
19Jul17	1	2	19Jul17	y
02Mar17	0	3	07Sep16	y
02Mar17	0	3	14Feb17	n
02Mar17	0	3	02Mar17	n
02Mar17	0	3	02Mar17	n
02Mar17	1	3	02Mar17	y
;
run;

data want;
call missing(_prev);
do until(last.id);
set have;
by id;
if replay='y' and missing(_prev) then _prev=date2;
else if replay='y' and _prev then do;
months=intck('month',_prev, date2,'c');
_prev=date2;
end;
output;
end;
drop _:;
run;

[ak2]

Thank you all for your quick answeres! Both Ksharp and novinosrin gave the same answers, but unfortunately I don't know hashing, so I could not understand the solution too well.
I'm sorry I forgot to say that the dates in my data set were sas dates, they just turned out like that when I pasted them into my question.

[Ksharp]

Here is a simple code.I wonder why I am unable to reach it yesterday.

data have;
infile datalines truncover;
format date1 date2 date7.;
input date1 :date7. curr  id date2 :date7. replay	$;
datalines;
01Jun17	0	1	23Nov16	n
01Jun17	0	1	29Nov16	y
01Jun17	1	1	01Jun17	y
19Jul17	0	2	06Sep16	y
19Jul17	0	2	23Sep16	y
19Jul17	0	2	19Jan17	n
19Jul17	1	2	19Jul17	y
02Mar17	0	3	07Sep16	y
02Mar17	0	3	14Feb17	n
02Mar17	0	3	02Mar17	n
02Mar17	0	3	02Mar17	n
02Mar17	1	3	02Mar17	y
;
run;
data temp;
 set have(where=(replay='y'));
 prev=lag(date2);
 if id=lag(id) then months=intck('month',prev, date2,'c');
 drop prev;
run;
data want;
 merge have temp;
 by id date2 replay;
run;

[ak2]

It was good that you posted the hash Object sloution as well. It'll be a teaser for Learning. Thanks.