Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Contributor ak2
Contributor
Posts: 34
Accepted Solution

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

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.


Accepted Solutions
Solution
‎05-09-2018 02:24 AM
Super User
Posts: 2,053

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

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;

View solution in original post


All Replies
Respected Advisor
Posts: 3,268

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

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;
--
Paige Miller
Super User
Posts: 6,933

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

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.

PROC Star
Posts: 629

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

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;
Thanks,
Suryakiran
Super User
Posts: 10,849

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

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;
Solution
‎05-09-2018 02:24 AM
Super User
Posts: 2,053

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

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;
Contributor ak2
Contributor
Posts: 34

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

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.
Super User
Posts: 10,849

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

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;
Contributor ak2
Contributor
Posts: 34

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

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

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 218 views
  • 0 likes
  • 6 in conversation