topic Re: For the same id, how to calculate number of months between two dates given a condition in SAS Enterprise Guide

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

ak2 — Tue, 08 May 2018 13:12:29 GMT

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.

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

PaigeMiller — Tue, 08 May 2018 13:29:39 GMT

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;

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

Astounding — Tue, 08 May 2018 13:29:53 GMT

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.

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

SuryaKiran — Tue, 08 May 2018 13:59:43 GMT

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;

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

Ksharp — Tue, 08 May 2018 14:33:40 GMT

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;

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

novinosrin — Tue, 08 May 2018 15:07:31 GMT

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;

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

ak2 — Wed, 09 May 2018 06:28:18 GMT

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.

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

Ksharp — Wed, 09 May 2018 13:03:33 GMT

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;

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

ak2 — Wed, 09 May 2018 13:07:30 GMT

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