BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ak2
Calcite | Level 5 ak2
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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

8 REPLIES 8
PaigeMiller
Diamond | Level 26

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
Astounding
PROC Star

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
Meteorite | Level 14

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
Ksharp
Super User
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
Tourmaline | Level 20
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
Calcite | Level 5 ak2
Calcite | Level 5
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
Super User

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
Calcite | Level 5 ak2
Calcite | Level 5
It was good that you posted the hash Object sloution as well. It'll be a teaser for Learning. Thanks.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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